Access: Need Query to Count Data from List Box

I'm creating an Access app for a doctor's office, and am stumped on how to structure my query...

It starts with a form for new patients -- enter the patient's name, etc., -- and the form has a list box to choose how the patient was referred. Must be able to check off multiple items.

The only other requirement is a chart that shows how many times each referral source was checked.

So here's what I need to figure out...

1. When I add the list box to the form, how do I relate the list box data to the patient ID?

2. How do I form a query that will show A) a list of all of the referral sources, and B) a count of how many times each one was selected?

In case it helps, here are my table and field names:

Foreign Key Field: FK_REFERRALS

Fields: ID, RNAME

Thanks -- offering 500 points since there are two parts to my question.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This database model is many to many relationship
Many referrals can be added for one patient
and many patients can be added  to one  referral
So the design must be changed
You need to add another table

Lets Say:
Fields: PatID Foreign Key for ID in Patients Table
           , RefID Foreign Key for ID in Referrals Table

You have a listbox  on your form with Multi Select  Option
For Each Item Selected in the Listbox , You will append a record in the Table PatRef

The Query or Chart then will be very easy
you will just Say " Select Count(RefID), R.ID, R,Source   From PatRef AS PR , Referrals AS R Where R.ID= PR.RefID Group By R.ID, R.Source " 

Helen FeddemaCommented:
Definitely a many-to-many relationship.  You may find my sample database useful.  Here is a link for downloading it:
And here is a screen shot of one of its forms:  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcantera26Author Commented:
Thank you - this definitely has me on the right track.

I'm having a hard time properly appending a record to the new many-to-many table.

When I add the Listbox to the form, it only provides the option of selecting one value at a time (rather than check boxes). I switched the field to Allow Multiple Values so I can check as many as I want, but now all of the choices selected appear together in a single field in the new m2m (PatRef) table.

Is this the correct way to do it, or should each item selected in the Listbox result in its own new record -- as you indicated in your response?

Why this matters - when a user selects "Other," I need to accommodate a "details" field for that entry. I left this out of my original question for the sake of brevity.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mcantera26Author Commented:
I studied Helen's very polished solution - thanks for providing the file!

I set up the form using a sub-form, but I really need the Listbox to be made up of checkboxes -- the users need to see all of the options as if they were looking at a paper form.

Is there any way to append a record to the m2m table either:

- without using a sub-form, or
- by using a Listbox (with checkboxes) within the sub-form

You can create a listbox , lets say ListBox1

Adjust its properties as follows:
MultiSelect : Extended (for Multiple Selections)

In the ListBox1_AfterUpdate

You  can post the records selected in the m2m table
Search for the record , it's not already on the m2m table post it

Dim SELNO  AS  Variant, rsM2M as recordset
set rsm2m = currentdb.openrecordset("Select *  from m2m")
For Each SELNO In ListBox1.ItemsSelected

' Search for the record  
if  m2m.nomatch then
      '  add the  record


mcantera26Author Commented:
Zoom - I like your approach, but I'm having trouble following some of it.

I set the Listbox property for Multiselect - Extended

After that, does everything else you said go in the AfterUpdate property?

Is the rest of what you typed code that I should type into Code Builder -- beginning with Dim SELNO and ending with Next SELNO?

If so, I think I follow the logic of most of it...

- But what is 'Dim'?
- Is SELNO an Access term or a variable name you chose?
- And should there be quotes before Search and Add, or did you abbreviate something?

Thank you very much - I really appreciate the help
I understand that you want to post the selected items in the list box to the table (you called it m2m for example),
If this is correct so all you need is when the user selects an item,
when the user clicks on a referral or multiple referrals in the list box ,  you will serach for each one of them in the table,
 if it doesn't exist you will post it.
I think the template I typed is enough

- Regarding SelNo , it's just a variable
- I declare it with the Dim Statement ,
you will need this only if  you put (Option Explicit) at the beginning of the module
This prevents you from using any variable without declaring it first
- I didn't type everything , I just gave you guide lines for you to follow ,

I just gave you template , you type the code for search and code for adding the record into the table
I assumed that  it is easy for you , and straightforward :)
If you still need any help ? Just Call :)

mcantera26Author Commented:
Thanks Zoom and Helen - just about have this app all wrapped up. Tomorrow will award points and post my own findings/summary for future EE users.
mcantera26Author Commented:
Thanks to both - very happy with the results. Combined Helen's many-to-many dropdown forms with Zoom's query and logic. Only thing I can't figure out is enabling the many-to-many form entry via a checklist, but will continue to study responses and see if I can make it work.
Helen FeddemaCommented:
Here is some code I use to add a single record to a linking table -- you can combine it with zoom's ItemsSelected code to check and add each selected item from a listbox.
'Link this patient to selected site
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tlnkSitesPatients", dbOpenDynaset)
   strSearch = "[SiteID] = " & lngSiteID & _
      " And [PatientID] = " & lngPatientID
   rst.FindFirst strSearch
   If rst.NoMatch = True Then
      'Record not found; add new record
      rst![SiteID] = lngSiteID
      rst![PatientID] = lngPatientID
   ElseIf rst.NoMatch = False Then
      'Record found; don't add new record
   End If

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.