I got a db containing days and room numbers, i need the query to tell me what room was used yesterday and is empty today, anyone got any ideas???? i no that i can use is null for the empty slots, but i need to only select the rooms that were full yesterday
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Running advanced query
- Thread starter MadSlayer
- Start date
Mad,
You're on the right track--First make a query that selects all of the Room Numbers where the date is null. Save that query, then make a second query that selects the room number where the date = DateAdd("d",-1,Date()). Join the two queries on room number, and you should have what you want.
Tranman
You're on the right track--First make a query that selects all of the Room Numbers where the date is null. Save that query, then make a second query that selects the room number where the date = DateAdd("d",-1,Date()). Join the two queries on room number, and you should have what you want.
Tranman
- Thread starter
- #3
No sweat Mad,
I was a noob myself about a zillion years ago, and lots of people were patient and helped me. I consider my contributions to this forum payback for that.
So, why don't we start at the start. Describe your database and what tables it contains and data types for the fields in question. Just open the table(s) in design view, and write down the names of the pertinent fields, and their data types. Maybe a little background about how the data gets in, what it's used for; etc.
Not to worry, I'll stick with you until you have a solution.
Tranman (Paul)
I was a noob myself about a zillion years ago, and lots of people were patient and helped me. I consider my contributions to this forum payback for that.
So, why don't we start at the start. Describe your database and what tables it contains and data types for the fields in question. Just open the table(s) in design view, and write down the names of the pertinent fields, and their data types. Maybe a little background about how the data gets in, what it's used for; etc.
Not to worry, I'll stick with you until you have a solution.
Tranman (Paul)
- Thread starter
- #5
I don't quite understand your setup, but this query may do what you need:
Select RoomNo From YourTable Where RoomDate Is Null And RoomNo In (Select RoomNo From YourTable As A Where A.RoomDate = Date()-1);
HTH
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
Select RoomNo From YourTable Where RoomDate Is Null And RoomNo In (Select RoomNo From YourTable As A Where A.RoomDate = Date()-1);
HTH
Daniel Vlas
Systems Consultant
- Thread starter
- #8
thats true, i may have found the solution, i was getting so sick of this all, that i started over, i made yes no items to indicate if a room is filled on a specific day, no i can use true and false in my query, probably a n00b solution, but it seems to work
- Status
Similar threads
- Question