Query/subquery a junction table

[AccessAddict]

Hi

I have four tables related - tblRoomNo (1-M) to tblRoomTrack and tblAllDates (1-M) to tblRoomTrack (junction table)giving a M-M relationship between tblRoomNo and tblAllDates, and tblRoomType (1-M) to tblRoomNo. tblRoomTrack has two fields (Date and RoomNo), both set as PK's and keeps track of which room number is booked on any given date. When a user selects a room type and a date on a data entry form, I need to construct a query(ies) that will check for the combination of room numbers related to the room type selected and on the selected date, and if that combination already exists in tblRoomTrack then doesn't show the room number as available in another query that displays a list of available rooms. Sheesh, it sounds as complicated trying to explain it as it does trying to design it!!
I've run out of aspirin, got through a box of pencils and am now the proud owner of a mountain of scrap paper so am hoping someone here can help me!!

Thanks for your time

Alan

 

[psemianonymous]

The best way to go about this is to use several queries that build upon each other. As an example, for your database, make an "All Available Rooms Past And Present And Future" query, then build another query that is "All Available Today" (which of course draws from the first query as its data source).


Also I recommend a shorter naming convention than the two examples I used.

 

[AccessAddict]

Hi
Thanks for your reply. I've given up on the aspirins - it's 24 hours later and still getting nowhere am thinking of valium or prozac!!

I think I'm missing something really stupid here. tbl1 and tbl2 are related as 1-M to tbl3, meaning tbl3 is the junction table of a M-M relationship. If I create a simple select query using tbl1 and tbl2, the query returns all records from tbl1 and tbl2 as you would expect. However, if I add tbl3 (the junction table) to the query, and not even add any of the fields from tbl3 to the query, the resulting recordset is only the records of tbl3, when what I need is the records from tbl1 and tbl2 NOT including any related records from tbl3. Tis driving me crazy!! I just know it's going to be something stupid that I can't for the life of me see.

Thanks to anyone who can save me from being addicted to tranquilisers!!

Cheers

Alan

 

[psemianonymous]

Look up OUTER JOINs. Look at the help files. This is what you're after.


If you need examples (they always help), maybe use the "Find Unmatched" query wizard and see what it generates.


You're going to have to wrap your mind around this. There is no workaround.