A matter of junction table and its functions

[The Dude]

Hello everyone,

I have a junction table that unites events and invitees. Plus other fields
like invited, present.. bla bla...

Now to save space I could only add the invitees that have been invited, but
the folks want the list of all invitees, and see who has been invited and who
hasn't.

My choices (that I see so far):
1- Copy the whole list of invitees and see who is Yes/No with a query
2- Add only the invitees, but then I need to cross query the non invited -
hence I don't have the opportunity to check a box if I want to add another
guy (as query don't allow checkboxes as a search result with nz or iif right?)

Number 1 is tricky for me as I heard that append queries are not the
quickest AND I can't manage to launch the query from a button with a WHERE...

Number 2 is tricky because I can't have a descent way to handle non invited
and easely add them to the table if needed...

So, please, HELP ME....
Thanks

 

[Jason Lepack]

Assuming your table structure is as such:

Persons:
identifier for person

Events:
identified for Event

Invites:
Person
Event
Attending (yes/no)

If you want to see a list of people who have been invited and their
status you only need to collect data from the invites table, linked to
Persons and Events for specific info.

If you want to see all the people who have not been invited to an
event, you use a left join from Persons to Event and select only the
records where Event.Person is null.

If you need more info, don't hesitate to post back. If you need an
example email me your email address and I will respond as I have time.

Cheers,
Jason Lepack

 

[The Dude]

Thanks a lot for your time Jason,

I took some time to try your suggestions, but I failed and it took me a
while to figure out why. My brain was so messed up yesterday...

So the problem is that there are several events, so if I do a left join then
high chances that all the contacts will be listed in one event, hence will
not appear in the non invited query.
My only chance: select a query with the invited ones (query 1), and then
create another query (query 2) with a left join from contacts to query 1.

As a result, and thanks Jason , I get both queries with my list of invited
and non invited... BUT, how can I automate these queries if I want to select
an event from a listbox? (ie if I do a [WHERE (query1).event = listbox] ,
how can query 2 be launched in regards of the modifications in Query 1?)

if you understand that it's a great personal achievement for you... if you
can solve that problem then it will be a great help for me!

Thanks

 

[Jason Lepack]

I have an example at home that I will send to you tomorrow. Send me
an email at the account in my profile and I'll email it to you then.

Cheers,
Jason Lepack

Thanks a lot for your time Jason,

I took some time to try your suggestions, but I failed and it took me a
while to figure out why. My brain was so messed up yesterday...

So the problem is that there are several events, so if I do a left join then
high chances that all the contacts will be listed in one event, hence will
not appear in the non invited query.
My only chance: select a query with the invited ones (query 1), and then
create another query (query 2) with a left join from contacts to query 1.

As a result, and thanks Jason , I get both queries with my list of invited
and non invited... BUT, how can I automate these queries if I want to select
an event from a listbox? (ie if I do a [WHERE (query1).event = listbox] ,
how can query 2 be launched in regards of the modifications in Query 1?)

if you understand that it's a great personal achievement for you... if you
can solve that problem then it will be a great help for me!

Thanks

Assuming your table structure is as such:

Persons:
identifier for person

Events:
identified for Event

Invites:
Person
Event
Attending (yes/no)

If you want to see a list of people who have been invited and their
status you only need to collect data from the invites table, linked to
Persons and Events for specific info.

If you want to see all the people who have not been invited to an
event, you use a left join from Persons to Event and select only the
records where Event.Person is null.

If you need more info, don't hesitate to post back. If you need an
example email me your email address and I will respond as I have time.

Cheers,
Jason Lepack