SQL Query

[Wildster]

Hi,

I've generated a query using the query builder in Access and it has generater the following: -

SELECT tblBooking.Booking_Date, tblBooking.Booking_Type, tblBooking_Time_Slot.Booking_Time_Slot, tblEmployee.Employee_Name
FROM tblEmployee INNER JOIN (tblBooking INNER JOIN (tblBooking_Time_Slot INNER JOIN tblBooking_Slot ON tblBooking_Time_Slot.Booking_Time_Slot_ID = tblBooking_Slot.Booking_Time_Slot_ID) ON tblBooking.Booking_ID = tblBooking_Slot.Booking_ID) ON tblEmployee.Employee_ID = tblBooking.Employee_ID;

Basically all it does it return the booking date, booking type, employee name and booking time. The thing is that each record has more that one booking time, therefore the query will show duplicated rows of the same record bar the booking time, which is different in each instant.

What I'm trying to do is return only one line for each record and show the MAX and MIN booking time for each particular Booking_ID

i.e.
15/10/2007 Reservation 10:00 (MIN time) 13:00 (Max time) Mark Smith

rather than at present

i.e.
15/10/2007 Reservation 10:00 Mark Smith
15/10/2007 Reservation 11:00 Mark Smith
15/10/2007 Reservation 12:00 Mark Smith

I realise that I prpbably need to use a GROUP BY statement and a MIN & MAX to return the correct times, but I'm having great difficulty in doing so.

Any help would be very much appreciated.

Thanks

[NeoPa]

Tell us what you've tried and we can help you find a solution I'm sure.

[Lysander]

This is a simple query I just knocked up that looks at a child table. For each women in each house in each village it returns the min year of birth and max year of birth
[code=sql]
SELECT [Tabanca ID], [Household ID], [Women ID], Min(YearOfBirth ) AS MinOfYearOfBirt h, Max(YearOfBirth ) AS MaxOfYearOfBirt h
FROM Children
GROUP BY [Tabanca ID], [Household ID], [Women ID];
[/code]

If you save your query as qryReserve then you can create another query based on it, as follows.
[code=sql]
select booking_date,Bo oking_Type,Empl oyee_Name, Min(Booking_tim e_slot),Max(Boo king_time_slot) from qryReserve GROUP BY booking_date,Bo oking_Type,Empl oyee_Name;
[/code]

That should give you what you want

[Wildster]

Didn't know you could do a query on a query, Genious!!

Thanks very much for you help

This is a simple query I just knocked up that looks at a child table. For each women in each house in each village it returns the min year of birth and max year of birth
[code=sql]
SELECT [Tabanca ID], [Household ID], [Women ID], Min(YearOfBirth ) AS MinOfYearOfBirt h, Max(YearOfBirth ) AS MaxOfYearOfBirt h
FROM Children
GROUP BY [Tabanca ID], [Household ID], [Women ID];
[/code]

If you save your query as qryReserve then you can create another query based on it, as follows.
[code=sql]
select booking_date,Bo oking_Type,Empl oyee_Name, Min(Booking_tim e_slot),Max(Boo king_time_slot) from qryReserve GROUP BY booking_date,Bo oking_Type,Empl oyee_Name;
[/code]

That should give you what you want

[Wildster]

Is there a way that the two queries can be brought together as one?

Thanks

[NeoPa]

Check out Subqueries in SQL.

Next time I suggest you follow the advice to show what you've done already. Anyone can copy and paste a provided solution, but you'll be back with the same question, at the same level, unless you start to do something for yourself.

Trust me. It's much easier to learn if you try things out first. It gives comprehension context to any solution provided.