Running advanced query

[MadSlayer]

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

 

[Tranman]

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

 

[MadSlayer]

eh, well, i dont quite think i work with dates the way you think i do, i just entered them in a table, probably a n00b mistake, but it a way to get weeknr. any help on making good dates, so i can make this work is welcome

 

[Tranman]

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)

 

[MadSlayer]

cool, do you have msn or something, or you just want me to put it on here?

 

[danvlas]

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



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Tranman]

Hi again Mad,
We don't get to use MSN here, and anyway, others benefit from the things that get posted here.

Daniel-nice code.

Tranman

 

[MadSlayer]

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