Getting counts and percentages of specific values, number of records per client, etc.

[Kalani]

Hello, my first post to this forum. I am relatively new to Access, despite having taught digital technology for a few years and it being something we used in one of those years.

In the picture above (hopefully!) you can see the structure of the tables I have set up, probably quite simple.

I work as a school counsellor, and since I have some knowledge of databases, I thought this would be a better way to store data than our typical Excel spreadsheet I've seen used at other schools. One thing that system couldn't do was identify unique clients versus a lot of sessions (several of which might have been the same client).

My problem is that I'm not familiar enough with querying to know how to pull out the data or reports I want, even though I know all the data is there. For example, it would be nice to:

• Get all sessions broken down by year level of the students, in terms of numbers and percentages, and total
• Get same broken down by ethnicity
• Identify those clients who come frequently (more than 6 times, say)
• Get break downs of the data in terms of the reason they're coming to counselling
• Get the above over various periods of time, Term 1, Term 2, and in future, 2022 vs 2021

I know this is a broad question, but I was hoping someone could give me some examples of how to do those, and then I should be away and able to develop other queries based on those.

Thank you!!!

[June7]

Probably should explore report design using SORTING & GROUPING features and aggregate functions (SUM, COUNT, AVG) calcs for the 'breakdown' outputs.

The frequency criteria might be the most difficult.

SELECT Students.*, CntSess FROM Students INNER JOIN (SELECT Count(*) AS CntSess, [Student Number] FROM Sessions GROUP BY [Student Number] HAVING Count(*) >=6) AS Q ON Student.ID=Q.[Student Number];

I don't use multi-value fields. But if you really want to use, get a thorough understanding of how they work. https://support.microsoft.com/en-us/...3-b6624e1e323a

Advise not to use space nor punctuation/special characters in naming convention. Nor reserved words (such as Date) as names.

[davegri]

Here's a workup that you might like to get you started. The main form is the student table, with the subform holding the junction table. The subform Topic dropdown includes not in list code to add new topics.
Included a couple of starter queries that could be expanded/used for report recordsources.

Kalani-davegri-v01.zip

[Kalani]

Thank you both for those replies. I will see if I can implement either of those options, though it seems I have a lot of learning to do, and maybe I haven't set things up very well for extracting the kind of data I'm after....

[Kalani]

@davegri, thanks again for including the files, very helpful.

I've started digging into this today and making a few tweaks, and then have started manually reentering data into the new DB structure. The biggest change I made was adding a tblReferralSources, very similar to the tblTopics and how it relates to the Topic_FK in tblSessions. So ReferralSource in tblSessions is now ReferralSource_FK.

So, all well and good so far, it was progressing well enough. But in doing data entry I wanted to reorganise the fields to match the sequence of data I was entering from, changing frmStudents so the fields are ordered FirstName, LastName, YearLevel, Gender, and Ethnicity. Then after shifting these around I found the tab key followed their original sequence, so it would tab through the fields at the top, then skip over Ethnicity and jump into the subform. I had a look at the property sheet on the form and found under other properties "Tab Index". I think all I changed was to reorder these...the numbering was quite strange initially, seemingly beyond the tab issue I was noticing.

The problem I've run into is now when I switch back to the form view, the subform data has disappeared! Before it loads I get the message "Microsoft Access cannot find the toolbar '7'." Which is pretty cryptic.


Any idea what I've done and how I can get my subform data showing up in the form again?

[Kalani]

Ah I seem to have got it back. Problem was, in the subform, which I could only open in design view (took a while to discover that!), there was a toolbar field that had a random '7' in it. Once removed and saved, and data is now back showing in the subform in the main form. Sigh. Ugh, that was painful. Access seems to specialise in this kind of thing!

[davegri]

Ah I seem to have got it back. Problem was, in the subform, which I could only open in design view (took a while to discover that!), there was a toolbar field that had a random '7' in it. Once removed and saved, and data is now back showing in the subform in the main form. Sigh. Ugh, that was painful. Access seems to specialise in this kind of thing!

Sounds like good progress. All programming, not just Access, is demanding, requiring strict syntactical precision. And it's pretty good at telling you that you have a problem.

[Kalani]

I actually worked as a software engineer for a couple years. Access makes programming seem simple in comparison!