Show Only Available Options in a Combo Box

[Ramtrap]

Hi all,
I have an issue with a query not coming up how I want it to. I have a conference scheduling db whereby I have the tables, as below:


Note that I must have all the tables as some conferences may have only the one day, but some may have 3 or 4.
What I need to find is the remaining Conference Days available for a person to be related to, after accounting for the ones he/she is already related to.

For example: I have person (Alan) going to Conference XYZ. It is a 3 day conference, and Alan is going only for Day 1 right now.
Here's what I know:


My query for ALL the days available for Alan would look like this (note that the criteria is coming from another form with pre-populated fields):


My query for the days my person is definitely going for that specific conference would look like this:


What I can't, for the life of me, is figure out how to get the delta. I'm assuming I need to create a query from a query (i.e. start a new query using the first query as my framework and subtract the days I know my person is already going). Is that kind of the right track?

[ranman256]

Q1, shows the persons and the days they attend conferences, qsPersonAttend
Q2, shows the conferences and all the days, qsConfDays
Q3, add in the 2 queries: qsPersonAttend and qsConfDays
join the 2 on [ConfDate]
make it an OUTER JOIN. Dbl-click the join line, set it to: ALL records in qsConfDays, some in qsPersonAttend
bring down the dates from both
qsPersonAttend.name, qsPersonAttend.ConfDate, qsConfDays.ConfDate

if you run you will see NULLs in the qsPersonAttend.ConfDate (the missing dates)
To see only the missing days, put IS NULL under qsPersonAttend.ConfDate.

[Ramtrap]

Q1, shows the persons and the days they attend conferences, qsPersonAttend
Q2, shows the conferences and all the days, qsConfDays
Q3, add in the 2 queries: qsPersonAttend and qsConfDays
join the 2 on [ConfDate]
make it an OUTER JOIN. Dbl-click the join line, set it to: ALL records in qsConfDays, some in qsPersonAttend
bring down the dates from both
qsPersonAttend.name, qsPersonAttend.ConfDate, qsConfDays.ConfDate

if you run you will see NULLs in the qsPersonAttend.ConfDate (the missing dates)
To see only the missing days, put IS NULL under qsPersonAttend.ConfDate.

Thank you for responding! I'm trying to do what you advised, but I think I'm getting crossed up on the last part.

Here is Q3, resulting from Q1 and Q2, as per above. You'll note that my Criteria statement is way off. Leaving it with a blanket IsNull statement generates an error, as expected, and I'm not entirely sure what the argument should be. (Note I am just using sample data and putting in ConferenceID = 1 and PersonID = 2. In my db, that person is going to days 1 and 3 of the conference, so I should be seeing day 2, but the whole thing (as run as above) yields NULL results for all fields (again because of my bad criteria)).



Thanks, again!

[ranman256]

no, just (2 words):
IS NULL


oh, and add qsConfDays.CONFNAME to the query.

[Ramtrap]

no, just (2 words):
IS NULL


oh, and add qsConfDays.CONFNAME to the query.

I got this to work, mostly. My misunderstanding for the Is Null vs IsNull, sorry!

What it's doing now, however, is showing me every day that a person is not going to a conference, i.e. I have to re-state the Conference ID to re-tell the query that I want ONLY days this person is not attending THIS conference--not EVERY conference.

As Is:


Restating ConferenceID within Query:


Is there a way to retain the ConferenceID field? (I changed the ConferenceDate to ConferenceID in my example, but the query search yield the same results, either way)

[aytee111]

In your first post you had a criteria of ConferenceID. In the query qsConfDays you can add that criteria and it will carry thru to this query.

[Ramtrap]

In your first post you had a criteria of ConferenceID. In the query qsConfDays you can add that criteria and it will carry thru to this query.

That was exactly it, thank you!