By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

SQL Query

P: 16
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblBooking.Booking_Date, tblBooking.Booking_Type, tblBooking_Time_Slot.Booking_Time_Slot, tblEmployee.Employee_Name
  2. 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;
  3.  
  4.  
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
Jun 5 '08 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Tell us what you've tried and we can help you find a solution I'm sure.
Jun 5 '08 #2

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Tabanca ID], [Household ID], [Women ID], Min(YearOfBirth) AS MinOfYearOfBirth, Max(YearOfBirth) AS MaxOfYearOfBirth
  2. FROM Children
  3. GROUP BY [Tabanca ID], [Household ID], [Women ID];
  4.  
If you save your query as qryReserve then you can create another query based on it, as follows.
Expand|Select|Wrap|Line Numbers
  1. select booking_date,Booking_Type,Employee_Name, Min(Booking_time_slot),Max(Booking_time_slot) from qryReserve GROUP BY booking_date,Booking_Type,Employee_Name;
  2.  
That should give you what you want
Jun 5 '08 #3

P: 16
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Tabanca ID], [Household ID], [Women ID], Min(YearOfBirth) AS MinOfYearOfBirth, Max(YearOfBirth) AS MaxOfYearOfBirth
  2. FROM Children
  3. GROUP BY [Tabanca ID], [Household ID], [Women ID];
  4.  
If you save your query as qryReserve then you can create another query based on it, as follows.
Expand|Select|Wrap|Line Numbers
  1. select booking_date,Booking_Type,Employee_Name, Min(Booking_time_slot),Max(Booking_time_slot) from qryReserve GROUP BY booking_date,Booking_Type,Employee_Name;
  2.  
That should give you what you want
Jun 6 '08 #4

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

Thanks
Jun 6 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jun 6 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.