Hello. The database I'm working on keeps track of building rentals. People rent the buildings for specific events. When you click on a date in the calendar form, a subform comes up that shows you a list of all the buildings and whether they are available or rented. If they are rented, it shows the name of the event. One event may be held in multiple buildings on the same day. The query I have designed only works if there is exactly one event that day and it is held in one building only. Otherwise we have a bunch of duplicates. I think I have to approach a different way, but I am stuck! Thank you for any suggestions.
Here are the details:
The query pulls from tbl_RentalLocations in order to show all locations, then from qry_MoCalLocations which lists the events, dates and buildings.
The field I created is [Available] and it puts "Available" in the field if the event location does not match that particular location, or it puts the name of the Event [Event] if the location of the event matches the location.
If you have multiple events on that day, you will end up with multiple records for locations - for example, Building #1 will show "Available" for one record and "Dog Show" for another record.
If you have an event that is held in multiple locations, you will also have duplicates. I want to tell it somehow that if there are multiple records for a building and one of them is not "Available", put [Event]. And if they are all "Available", put "Available"
Also, if there are no events that day, nothing is going to come up in this query and I don't know how to handle that unless I put in a false event that spans all the dates of the calendar. I can do that, but then I have to make sure it doesn't show up on all the other reports, so I want to make sure there isn't a better way.
SELECT tbl_RentalLocations.RentalLocation, IIf([tbl_RentalLocations].[RentalLocation]=[qry_MoCalLocations].[Location],[qry_
MoCalLocations].[Event],"Available") AS Available, qry_MoCalLocations.AGREENO, qry_MoCalLocations.Date, qry_MoCalLocations.Event
FROM tbl_RentalLocations, qry_MoCalLocations
ORDER BY tbl_RentalLocations.RentalLocation;