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.

Problems:
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.

Query:
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
HAVING (((qry_MoCalLocations.Date)=#2/10/2012#))
ORDER BY tbl_RentalLocations.RentalLocation;

Thank you!

nikkinal,

Greetings and Welcome to UtterAccess.

See if the attachment in this previous post is what you're looking for - Calendar with text


AvgJoe

Thank you for your reply. I posted my response in the other area accidentally. This does not really solve my problem, but thank you for the file.

My subform always shows every building. Next to each building name it will have the word "Available" if it is not rented for that day, or it will have [Event] if it is rented for that day.

Your query should look something like this

That's the way to go for calculating intervals.
So probably you don't need all those nested queries... don't know really because honestly I didn't read them carefully
Hope that helps.

Thank you. Not sure how this will solve the problem. The list of locations is in one table. The list of events is in another table. I need to show the list of locations and whether or not they are rented for a particular day.