many-many query

samonwalkabout

Registered User.
Local time
Today, 11:35
Joined
Mar 14, 2003
Messages
185
I Have 3 tables

tblEvents
-eventID
-eventName
-EventDate

tblTrim
-TrimID
-Name

Tblschedule
-ScheduleID
-EventID
-TrimID

Tblschedule is basically a many-to-many link table showing which names are going to which events. I want to create a qry that Looks at the date of events and tells me if any names are booked on more than one event in a day.

I cant think how to get it to do this to the entire record set at once, just on name at a time or one event at a time. What i need is too see any conflicts for all.

Im guessing is a few querys that build on each other

Any Ideas

Can post sample DB if needed

Thanks
 
If I read you correctly (not saying I am) this should give you any name/eventdate combinations that occur more than once:

select name, eventdate
from tblevents T1
inner join tblschedule T2 on T2.eventid = T1.eventid
inner join tbltrim T3 on T3.trimid = T2.trimid
group by name, eventdate
having count(*) > 1
 
Thanks that seems to work, i hadnt thought of just looking at more than one Trim showing up in a given day. I am just using >1 though as the (*) gives me a type mismatch error?

But what if an event runs for a number of days. i am recording startdate/end date and also duration in days in my events table.

Any ideas
 
Might have jumped the gun there a bit, on closer inspection it doesnt seem to work, i think its the (*) bit.. what does this mean? Are you talking SQL or in the qry builder window?

i have got a load of these kinda qry to do, if i could get one working to look at it would be sweet :)

Have Posted Sample if you wanna take a look.

Thanks very much
 

Attachments

SELECT [TblEvents].[EStartDate], [TblEvents].[EventNameTxt], [TblTrimNumbers].[ColNameTxt]
FROM TblTrimNumbers INNER JOIN (TblEvents INNER JOIN tblSchedule ON [TblEvents].[TrainingEventID]=[tblSchedule].[EventID]) ON [TblTrimNumbers].[TrimID]=[tblSchedule].[TrimID]
GROUP BY [TblEvents].[EStartDate], [TblEvents].[EventNameTxt], [TblTrimNumbers].[ColNameTxt]
HAVING Count(*)>1;

Having Count(*)>1 tells Access to return only those groups that have more than one records.

In your sample data, each group of "EventID plus TrimID" in table tblSchedule is unique, so none of the groups has more than one records. Hence the query would not return anything.
 
Last edited:
On re-reading your post, it seems to me what you need is to group by only [TblEvents].[EStartDate] and [TblTrimNumbers].[ColNameTxt] in your query, not three fields:
I want to create a qry that Looks at the date of events and tells me if any names are booked on more than one event in a day.
You can just remove the [TblEvents].[EventNameTxt] from your original query and change the Having Clause to HAVING Count(*)>1:-

SELECT [TblEvents].[EStartDate], [TblTrimNumbers].[ColNameTxt], Count(*) AS NumberOfEvents
FROM TblTrimNumbers INNER JOIN (TblEvents INNER JOIN tblSchedule ON [TblEvents].[TrainingEventID]=[tblSchedule].[EventID]) ON [TblTrimNumbers].[TrimID]=[tblSchedule].[TrimID]
GROUP BY [TblEvents].[EStartDate], [TblTrimNumbers].[ColNameTxt]
HAVING Count(*)>1;

This query is basically the same as FoFa's query. (Note: I have added a NumberOfEvents field in the query.)


I have imported your tables into a new Access 2000 database. You can run the query "Query group by 2 fields" to see if the (*) still gives you a type mismatch error. On my system the query returns:-

EStartDate ColNameTxt NumberOfEvents
9/23/2003 Department Request 2
9/23/2003 Group Request 2
9/23/2003 Sam Black 2
 

Attachments

Last edited:
Cool! So the group by 2 qry shows how many events some one is booked on if its more than 1 on the same start date.

Thats great!

I might even be able to incoperate this into the booking from so you can add someone who already has 1 event on that day.

What if im dealing with an event that runs more than one day? Can i have a between Startdate and End date function, or is it better to use number of days(which is also recorded) + startdate to give me a range?
 
What if im dealing with an event that runs more than one day? Can i have a between Startdate and End date function
Yes, you can.

To search for a range of dates, you can put the search start date and end date in a record in a search table. Then put this table with the other three tables in a new query to return the required records using Between ... And ... Then group and count the returned records in another query.


For testing purpose, I have changed the EEndDate of "arm wrestle training" to 9/24/2003 in the attached database. (Note: My system uses the US m/d/yyyy format.)

And I have put 9/23/2003 as both the search start date and end date in the SearchRange table. When you run the query "Search Dates Query A", you will see those records where 9/23/2003 falls between the EStartDate and the EEndDate. These records will be grouped and counted when you run "Search Dates Query B".


You can change the search end date to 9/24/2003 in the SearchRange table to search for 9/23/2003 to 9/24/2003 and run Query B. Now only those records whose event dates encompass 9/23/2003 to 9/24/2003 will be returned, grouped and counted.

If what you have in mind is only either the search start date or the search end date is between the event dates, you can change AND to OR in the Where Clause in Query A:-
WHERE [SearchRange].[SearchStart] Between [TblEvents].[EStartDate] And [TblEvents].[EEndDate] OR [SearchRange].[SearchEnd] Between [TblEvents].[EStartDate] And [TblEvents].[EEndDate];
 

Attachments

Thanks very much for you help EMP, its an interesting solution, i am however having some trouble seeing how i can intergrate it into a form that would show any overlap between people on courses. Would the user have to enter a search range for show any overlapping course bookings? With you other method for startdate only i have a list box displaying the overlaps and a 2nd list box dispaly details of the overlap i.e the 2 event details so the user can just remove a name from one. Do you use any querys similar to this and if so have you devised any simple method. I also thought about trapping the overlap so that the system would not let you add people to overlapping events but cant even imagine where to start!
 
Boy am I having a tough time with this one. I can get the DB to search different start search and end search date based on what the start and end dates of my events are, but the query doesn't always show the overlaps at the correct time. and nothing shows as double booked until later when different dates are being searched.

Is there any way I can get the DB to look at a date range from start of year to end and show any overlaps?

Or am I better to write a bit of code that just checks for any overlaps in the first place and wont allow you to add data?

If anyone can post a sample Code or point me to one that would be great!

Any help would be really good, I have got everything else under control but this one aspect of the DB function is melting my brains

:confused: :confused:
 

Users who are viewing this thread

Back
Top Bottom