How to solve this problem?

[Tsung90]

Hi, I would like to create a query which produces a report at the end. In the report I would like to know 'how far in advance are members booking the badminton court' and 'how far in advance are non-members booking the badminton court'

In my tblBadmintonCourtBooking:
Column 1: Booking_ID
Column 2: Member_ID
Column 3: Full_Name
Column 4: Date_Of_Booking_Made
Column 5: Time_Of_Booking_Made
Column 6: Date_Booked_For
Column 7: Time_Booked_For

Column 1 consists of data for members and non-members, same for column 3, 4, 5, 6 and 7. Column 2 consists of data for only members.

Thanks.

 

[plog]

This SQL will give you the Average amount of days a court was booked prior to the match:

SELECT IIf(IsNull([Member_ID]),"Non-") & "Member" AS MemberType, Avg(DateDiff("d",[Date_Of_Booking_Made],[Date_Booked_For])) AS AvgLeadTime
FROM tblBadmintonCourtBooking
GROUP BY IIf(IsNull([Member_ID]),"Non-") & "Member";

If it doesn't give you what you want, provide specific data from the table and the data the query should return based on that sample data.

 

[Tsung90]

Where would you place the sample data?

 

[Tsung90]

Can you have a maximum and a minimum rather than an average?

 

[plog]

Sure:

SELECT IIf(IsNull([Member_ID]),"Non-") & "Member" AS MemberType, Min(DateDiff("d",[Date_Of_Booking_Made],[Date_Booked_For])) AS MinimumLeadTime, Max(DateDiff("d",[Date_Of_Booking_Made],[Date_Booked_For])) AS MaximumLeadTime
FROM tblBadmintonCourtBooking
GROUP BY IIf(IsNull([Member_ID]),"Non-") & "Member";

 

[Tsung90]

How would you include the average SQL statement with the min and max SQL statement? I want the whole query to produce the min, max and the average and represented in a report.

Thanks.

 

[plog]

Here's the part where I teach you to fish. Post the first SQL statement into an Access query, then look at it in design view. Then post the second SQL statement into an Access query and look at it in design view. Copy over the entire Average field from the first to the second query and you will have it all in the second query.

 

[Tsung90]

Thank you so much for the help, really appreciated.