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?
Helen FeddemaConnect With a Mentor Commented:
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:  
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 " 

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.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
zoom2000Connect With a Mentor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.