Results 1 to 3 of 3
  1. #1
    jholc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    2

    Help creating a participant list

    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!

  2. #2
    jholc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    2
    Solved. I guess taking the time to write out my problem was as much help as I needed.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Welcome to the site! Sometimes all it takes is writing out the problem to make you rethink it. Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating dynamic drop down list
    By SltPhx in forum Access
    Replies: 3
    Last Post: 07-29-2013, 10:17 AM
  2. Creating a temp mailing list
    By bbrazeau in forum Programming
    Replies: 2
    Last Post: 05-06-2013, 12:23 PM
  3. Creating a rotating Job list
    By brharrii in forum Queries
    Replies: 4
    Last Post: 05-01-2013, 04:07 PM
  4. Help creating a crosstab query from a list
    By bloocie in forum Queries
    Replies: 3
    Last Post: 04-23-2012, 11:58 AM
  5. Creating a load list for shipping
    By Reaper in forum Access
    Replies: 9
    Last Post: 11-13-2011, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums