Greetings,
I have a form based on my main table "Referrals" with a sub-form based on my 2nd table "Activity". This sub-form collects entries on various dates from various staff and includes 10 combo boxes where staff can choose any referrals they made to the client. What is entered may be varied choices or multiple of the same. For example:
Combo 1: Social Service
Combo 2: Social Service
Combo 3: Emergency Food
Combo 4: Clothing
Etc....
I know very little of anything Advanced in Access and pretty much learn by trial and error.

What I'm needing to do is to somehow tally the referrals made for a client over several dates. So, at any time I can run a report and it will count each time "Social Service" was entered for that client and then give me a total to report.

Any help would be appreciated! Thank you

What does your table structure look like? Why do you have multiple combo boxes to select the activities? Each time an activity is tied to a client, it should be a record in a related table.

As far as table structure, I have a main table, "Referrals" listing my clients and another table "Activity" that records multiple entries related to each client. I have combo boxes because I didn't know how else to do it - and I'm definitely open to suggestions! As explained above, I want the staff to be able to record the referrals made to the client throughout our involvement with them. Thus, on one day I might make 3 social service referrals and 1 referral for food. The next day I might make another social service referral and 1 referral for clothing. At the end of our involvement with this client, I want a report to tally all this and report that we made a total of 4 social service referrals, 1 referral for food and 1 referral for clothing.
Thank you for assisting.

Since a client can have many activities associated with them and those same activities can apply to many clients you have a many-to-many relationship which you cannot handle effectively with just two tables; you need three

tblClients
-pkClientID primary key, autonumber
-txtClientName

tblActivities (a record for each possible activity)
-pkActivityID primary key, autonumber
-txtActivity

tblClientActivities
-pkClientActID primary key, autonumber
-fkClientID foreign key to tblClients
-fkActivityID foreign key to tblActivities
-dateAssigned

With this structure, tallying the results is done with a simple totals query grouped by client and activity.

Thank you - I will try this!

Thank you for this help - I have created the table structure as you suggested, but now I'm unsure of exactly how to build my query. Is the query now just based off of the tblClientActivities? If I use that and Group By, then it doesn't return any results. I'm sure this is simple, but I'm just not advanced enough to figure out how to produce the desired results of a tally for each referral/activity. I appreciate your continued help!

To start off, yes you would use the tblClientActivities to generate your count, but you might have to nest the queries you create within other queries to get a nice looking result. Let's concentrate on getting the totals first.

To get the count of each activity for each client, the totals query would look like the following in SQL text. Note: it doesn't matter which field you actually conduct the count on.

SELECT tblClientActivities.fkClientID, tblClientActivities.fkActivityID, Count(tblClientActivities.pkClientActID) AS CountOfpkClientActID
FROM tblClientActivities
GROUP BY tblClientActivities.fkClientID, tblClientActivities.fkActivityID;

The above query (which I will name: qryGetCounts) will give you the count. Additionally, it will give you the ClientID and ActivityID values which aren't very useful to your users, so you need to bring in the actual client and activity names. A query like the following will do that for you:

SELECT tblClients.txtClientName, tblActivities.txtActivityName, qryGetCounts.CountOfpkClientActID
FROM tblClients INNER JOIN (tblActivities INNER JOIN qryGetCounts ON tblActivities.pkActivityID = qryGetCounts.fkActivityID) ON tblClients.pkClientID = qryGetCounts.fkClientID;


I have attached a sample database so that you can see what I have done.

I have created the queries as you explained (and thank you for the visual!), but I must have something wrong in my relationships. I actually have 4 tables. One lists my clients and a bunch of info about them. One shows their progress where notes can be recorded, producing many entries per client. On this Progress table exists my combo boxes of which I created 10 - because I didn't know any better way of collecting this info. Those combo boxes pull from the table Referred, which lists all the possible options. I tried to set everything up as you explained, but I've done something incorrectly since I don't get results from the queries. I've attached a print-screen of my relationships so you can get the idea of what I have.

Thank you again both for your assistance and your patience! :)

First, the progress table is not normalized as evidenced by the repeating fields, Referred to 1, Referred to 2, etc. The table ClientReferred is designed to handle the multiple referrals of each client, so the progress table is not needed. To better help you, I need to understand what you are doing (i.e. what process you are trying to model), in detail. From your previous posts, it sounds like you have clients and those clients are in need of various services and you refer those clients to an appropriate person or agency for help. It sounds like it may be beneficial to capture the client's needs and then the associated referrals for each of those needs. Once you refer your client, what happens next? What do you want to track? How do you know when something is complete?

Am I on the right track?

You are correct in your line of thinking - we are basically case managing people. I will see a client, document what needs there are and what I do in the Progress Notes (currently a memo field in the Progress table). As part of my Progress Notes I also wish to track the sort of referrals we are making. So, at the end of everything, I want to be able to print out my notes (a narrative of involvement) as well as (for those not wanting to read the detail of the narrative) have a concise tally and sort of referrals given to that client (over the length of involvement we gave this client 3 housing referrals, 2 food referrals, and 1 clothing referral).

The narrative will describe what happens from the referrals given and provide a detailed description of our involvement; any follow-up is included in this narrative. If we need to switch case workers mid-stream we can hit the ground running since they will be able to read all the back-story. When a case is complete, I am able to mark it as closed (located on my Client table). (My other issue is figuring out how to assign a case worker at log-on having it then assign any referrals/progress notes under that particular worker and then sort my reports by case workers - but that is a completely different question altogther.)

In all simplicity, I am an ameteur of Access trying to create a complex database for our team. I am very appreciative of the help I have received from you and this site! Let me know if you need further detail.

OK, I have a better understanding of what you are doing, but I will make an assumption that a client can have many cases.

tblClients
-pkClientID primary key, autonumber
-txtFName
-txtLName

tblCaseWorkers
-pkCaseWorkerID primary key, autonumber
-txtWorkerFName
-txtWorkerLName

tblClientCases
-pkClientCaseID primary key, autonumber
-fkClientID
-fkCaseWorkerID foreign key to tblCaseWorkers
-dteStarted
-dteCompleted

For each case, you said that you identify needs. This warrants a one (case) to many (needs) relationship

tblCaseNeeds
-pkCaseNeedID primary key, autonumber
-fkClientCaseID foreign key to tblClientCases
-txtNeed

You also said that for each need, you record various progress items (one (need) to many (progress items)

tblNeedProgress
-pkNeedProgID primary key, autonumber
-fkCaseNeedID foreign key to tblCaseNeeds
-dteProgress date for the progress item was recorded
-txtProgress


For each progress item, you may make one or more referrals (correct me if I am wrong in this assumption)

tblProgReferrals
-pkProgRefID primary key, autonumber
-fkReferralID foreign key to tblReferral

tblReferral
-pkReferralID primary key, autonumber
-txtReferral

What exactly are referrals? Are you referring the client to an agency? It also sounds like you have different types of referrals (clothing, housing, food). Can 1 agency handle many types of referrals? We might have to tweak the last two tables based on your response to these questions.

Thank you for continuing to assist me, jzwp22. Sorry I disappeared; my father passed away and I had to leave abruptly. As for your assumptions, they all look correct. In regards to referrals, we do refer to various agencies and each of those agencies can fulfill different needs.

Sorry to hear about your father's passing; its always difficult when we lose someone close to us. My thoughts and prayers are with you and your family.


I'm thinking that since you would probably have overlap in the services the various agencies (to which you refer your clients) can perform, you need to set up a junction table

tblAgencies
-pkAgencyID
-txtAgencyName

tblServices
-pkServiceID primary key, autonumber
-txtServiceName

tblAgencyServices
-pkAgencyServID primary key, autonumber
-fkAgencyID foreign key to tblAgencies
-fkServiceID foreign key to tblServices

Then when you make a referral related to a particular progress item

tblProgReferrals
-pkProgRefID primary key, autonumber
-fkAgencyServID foreign key to tblAgencyServices

You will not need the following table that I had in my earlier structure:

tblReferral
-pkReferralID primary key, autonumber
-txtReferral

Thank you for both your kind thoughts and your continued assistance. There is a lot here; I will create the tables as outlined and let you know how it goes.

You're welcome; good luck on your project.

Thank you again for all your assistance. I believe I am much closer to my solution, but still finding areas that confuse me.

I've attached how my table relationships look. I'm not real sure which relationships should be "show only those equal" or which should include multliples one way or the other, so this might be creating problems.

I like and understand the table structure, but I can't get my subform to access all the Activity records related to the main form. For example: I have client "A" who has 3 Activity entries. When I open my subform to enter another activity, I want to enter a new activity and also be able to scroll through past entered activities. When I try this, I can enter a new activity, but cannot see any of those previously entered.

I've put in the property default value for ClientID: =[Forms]![Outreach Referral Form]![ClientID].

This with "Outreach Referral Form" being the name of my main form. The ClientID automatically fills in correctly from the main form, but that is all. It does not pull any pre-existing info. Any thoughts?

Thank you!:)

Most, if not all, of your relationships should be as follows: "Only include rows where the joined fields from both tables are equal"

Since you are joining the activites to the referrals in the table ActivityReferrals, you should join the clientActivities to ActivityReferrals (not the Activity table)

ClientActivities
-ClientActivityID primary key, autonumber
-ClientID foreign key to Client table
-ActivityReferralID foreign key to ActivityReferrals table
-CaseMgrID foreign key to CaseMgr table