Results 1 to 5 of 5

Thread: Distinct-ify

  1. #1
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98

    Unanswered: Distinct-ify

    Hi,

    I have a query that selects:
    - subject name (DomeinID)
    - booking date (Datum_nu)
    - end date (Afgemeld_datum)

    and the last field does a DateDiff between the booking date and end date. What I want to do and what I just can't figure out is how I can make it so that only the domain name and the month are selected with the average DateDiff field.

    Please let me know if this is possible.

    THankyou,
    Charlotte

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by charlottevk
    Hi,
    What I want to do and what I just can't figure out is how I can make it so that only the domain name and the month are selected with the average DateDiff field.
    Charlotte
    If I understand your requirements correctly, the following should help:

    SELECT Domains.DomeinID, Month([Datum_nu]) AS MonthNum, Avg(DateDiff("d",[Datum_nu],[Afgemeld_datum])) AS Days
    FROM Domains
    GROUP BY Month([Datum_nu]), Domains.DomeinID;

    I've assumed your table is called DOMAINS.

    HTH
    Chris

  3. #3
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    It works like a charm. Thankyou so much.
    Can you tell me if there is a way I can change the month numbers to names (like January 2004)?

    charlotte

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    How about this

    Format(DateSerial(Year([Datum_nu]),Month([Datum_nu]),1),"mmmm yyyy")

    Dave
    Last edited by DavidCoutts; 08-24-04 at 12:43.

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by charlottevk
    Can you tell me if there is a way I can change the month numbers to names (like January 2004)?
    Try:
    SELECT Domains.DomeinID, Format([Datum_nu],"mmmm yyyy") AS MonthNum, Avg(DateDiff("d",[Datum_nu],[Afgemeld_datum])) AS Days
    FROM Domains
    GROUP BY Domains.DomeinID, Format([Datum_nu],"mmmm yyyy");

    Look up Format and DateDiff in Access help to see how the functions work. They are quite powerful e.g. you can use DateDiff to count the number of week days.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •