Help creating a participant list
Windows 7 64bit 
Access 2010 64bit
First of all, I'm brand new at this and I've never posted in forums for help, so forgive me if I'm not using good forum etiquette. Also, programming lingo can easily go right over my head, so even if a solution is easy, it may still sound like Greek to me. Nevertheless, I'm writing a query in MS Access and I would love some help figuring this out:
I'm trying to come up with a list that gets the names of each participant who is attending a particular workshop and whether or not they also purchased handbook # 1 or handbook # 2. If they are getting a handbook, it would say so as a line item on their invoice. Both the handbooks and the workshop are listed as a product and linked to a participant. So, I want to write a query that essentially says, for each invoice that contains prod_id = 10 (or whatever workshop is set up as a parameter on the form), get the participant name. If, on that same invoice, the particpant also purchased a handbook (either #1 (prod_id=14) or #2 (prod_id=16)) list the quantity. Otherwise, if their name shows up as having purchased a workshop, but not any handbooks, list 0 for both.
What I have so far is a list that shows which participants purchased prod. 14 or 16 and on what invoice.
Participant_id Prod_id QTY Invoice_id 40 14 1 20 43 14 1 22 41 14 1 20 41 16 4 20 45 16 1 26 40 16 1 20 41 14 1 25
What I want it to show is the two handbooks having quantities displayed in two columns where invoice_id and participant_id are the same, like this:
Participant_id P_hdbk Z_hdbk Invoice_id 40 1 1 20 43 1 0 22 41 1 4 20 45 0 1 26 41 1 0 25
And when I get the Workshop Participant List:
Participant_id Invoice_id Description 42 21 La Crosse Workshop, June 27, 2013 43 22 La Crosse Workshop, June 27, 2013 40 20 La Crosse Workshop, June 27, 2013 41 20 La Crosse Workshop, June 27, 2013 40 24 La Crosse Workshop, June 27, 2013 44 25 La Crosse Workshop, June 27, 2013 46 24 La Crosse Workshop, June 27, 2013
I want the final Workshop Participant List to show the participants who are not getting either handbook, so it would look like this:
Participant_id P_hdbk Z_hdbk Invoice_id 40 1 1 20 43 1 0 22 41 1 4 20 41 1 0 25 42 0 0 21 46 0 0 24
One last problem - it's most likely that a person will be purchasing a workshop and handbooks together on the same invoice, but it's possible that they will call back and order handbooks after they've signed up for the workshop. This means that the query would not show the the extra quantity ordered, unless the new invoice included an additional purchase for the same workshop. I will use an invoice memo to deal with odd cases (explanation of payment status, extra handbooks ordered, etc.) and export the participant list to excel for modification before it is used at the workshop itself. I know this is probably a poor solution, but I haven't been able to see any other way around it.
Any help, insight or suggestions would be greatly appreciated!
Reply With Quote
