If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A matter of junction table and its functions
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 |
#2
|
|||
|
|||
A matter of junction table and its functions
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 On Jan 17, 9:51 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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 |
#3
|
|||
|
|||
A matter of junction table and its functions
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" wrote: 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 On Jan 17, 9:51 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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 |
#4
|
|||
|
|||
A matter of junction table and its functions
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 On Jan 18, 5:47 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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" wrote: 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 On Jan 17, 9:51 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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 |
#5
|
|||
|
|||
A matter of junction table and its functions
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 On Jan 18, 5:47 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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" wrote: 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 On Jan 17, 9:51 am, The Dude f_com2°AT°yahoo°DOT°fr wrote: 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 |
Thread Tools | |
Display Modes | |
|
|