Solved Hi, stumped with how to build this query

[james.crust]

Hi, new poster here, apologies if I haven't got the right forum - please let me know if I need to post elsewhere. The last 2 images are Excel drafts to try and show what I am trying to achieve.

I have a screening assessment database, simple enough so far: Clients table (ClientID PK), Dsqiid table with ClientID as FK (Dsqiid is the assessment), Scores table with DsqiidID as PK for 1 to 1 relationship. Each client can have many Dsqiid assessments. Each Dsqiid can have one Scores record.

This is a section of the actual scoring part of the form just for context:

There are 43 answers in the Scores table (S1, S2, S3 etc.) Each question can have one of 4 answers stored, as shown above. I have used an option group for each question to assign a value of 1 to 4 for each question. I figured I can look up the text equivalent for the 4 answers from another table. Here's what it looks like:

Now I am stumped on how to aggregate the data to show me: "Show me the percentage of each answer (1 to 4), for each question, aggregated from the DSQIIDs selected (eventually I will have a date period to select from). I was hoping to get something like this (again I figured I could alias/lookup the actual question in place of S1, S2 etc.):

From this I should be able to column chart to get this. This is what I am looking to achieve:

I have been tinkering with UNION queries and trying different aggregate queries, but I feel my head is just not getting round this, or that I am storing the data incorrectly in the first instance and I am doing something fundamentally wrong.

Any help or advice to point me in the right direction would be appreciated.

Many thanks

James

 

[GPGeorge]

What you show in the first image is often referred to as "spreadsheet style" table, suitable for Excel.

You are right. There is a more appropriate way to store data in a relational database table, suitable for Access.

You need four tables to manage this:

Client table -- Only details about clients
"DsqID" table -- (whatever DsqID stands for) Only the Assessments, e.g. "Confused at Night". Should have an AutoNumber Primary Key
Ranking table --Descriptions of the four levels of ranking, with an AutoNumber Primary Key
ClientDsqID table -- a "junction" table in which you record the Primary Key for each client as a Foreign Key, the Primary Key for each assessment given to that client, again as a Foreign Key, and the Primary Key for the Ranking for that client on that assessment, also as a Foreign Key.

 

[jdraw]

I agree with George and would emphasize that ClientDsqID needs an AssessmentDate if you are( or plan to) monitoring scores over time intervals.

 

[james.crust]

What you show in the first image is often referred to as "spreadsheet style" table, suitable for Excel.

You are right. There is a more appropriate way to store data in a relational database table, suitable for Access.

You need four tables to manage this:

Client table -- Only details about clients
"DsqID" table -- (whatever DsqID stands for) Only the Assessments, e.g. "Confused at Night". Should have an AutoNumber Primary Key
Ranking table --Descriptions of the four levels of ranking, with an AutoNumber Primary Key
ClientDsqID table -- a "junction" table in which you record the Primary Key for each client as a Foreign Key, the Primary Key for each assessment given to that client, again as a Foreign Key, and the Primary Key for the Ranking for that client on that assessment, also as a Foreign Key.

Thank you GPGeorge and jdraw

I went for a walk after posting thinking it over and junction table kept nagging me in my mind.

Sorry, I should have posted a view of the relationships, but taking into account what you two have said, I updated it - would this work?

And would I still be able to use a form like this?:

You're probably not, but just in case you're interested, DSQIID is an acronym for Dementia Screening Questionnaire for Individuals with an Intellectual Disability you can Google the PDF to see the actual assessment (I couldn't post links, guess because I'm new). I work for an NHS learning disability service.

Many thanks for your help,

James

 

[CJ_London]

Since users can only select one option your use of an option group would work ok since only one value is stored

 

[jdraw]

James,
Regarding your

Sorry, I should have posted a view of the relationships, but taking into account what you two have said, I updated it - would this work?

Please see this article on Stump the model. Create some sample data and some sample scenarios and test your model. It is much easier to adjust the "paper model/database structure" during some serious vetting of the model rather than attempting to adjust a physical database.

With your proposed Form, try entering/retrieving? data, see /confirm if all necessary data is available/accessible. Also, try to mockup any reports you need based on your test data. This will help you better understand your data and requirements.

What exactly are ClientID, ClientID2, ClientID3, ClientID4?

 

[Pat Hartman]

The problem is the design of your question table. Each question should be a row, NOT a column. If you normalize that table, your statistics will be a breeze. So, you end up with 43 rows per interview. Each row has 4 columns.
UniqueID (autonumber)
PatientID (FK to patient table)
QuestionID (FK to question table)
Answer

 

[Pat Hartman]

The question table contains the text of the questions so you don't have to hard code them on the form. Makes changing and making additional questionnaires as easy as adding a new set of questions.

 

[james.crust]

James,
Regarding your

Please see this article on... Create some sample data and some sample scenarios and test your model. It is much easier to adjust the "paper model/database structure" during some serious vetting of the model rather than attempting to adjust a physical database.

With your proposed Form, try entering/retrieving? data, see /confirm if all necessary data is available/accessible. Also, try to mockup any reports you need based on your test data. This will help you better understand your data and requirements.

What exactly are ClientID, ClientID2, ClientID3, ClientID4?

Thanks for the link - I like the sound of the stump the model approach. I like the 'what if' thought scenarios. Unfortunately no one in my team would be up for taking that approach; we're just a bunch of clinicians really, no one else has any interest in this type of thing, and I took this on as I couldn't take watching others try and manage the ever less manageable Excel approach.

ClientID is the autonumber PK. ClientID2, 3, 4 are 'backup' indexes that are placeholders at the moment. ClientID2 is for their electronic patient record number, ClientID3 for social services' electronic record number, ClientID4 for NHS number. I had to transfer data from the Excel sheet to Access manually, and the idea of spending even more time looking up each client's NHS number on our patient record system at the start of this wasn't appealing. The Excel sheet, of course, had no unique identifier - they were relying on no one ever having the same name and date of birth. I want to eventually force NHS number to avoid duplicates but I have code that alerts for same name & DOB and forces another unique ID if it really is a different person, and a query to list any duplicate names just for checking.

(I had to take the link out of your reply as it wouldn't let me post this reply!)

 

[james.crust]

The problem is the design of your question table. Each question should be a row, NOT a column. If you normalize that table, your statistics will be a breeze. So, you end up with 43 rows per interview. Each row has 4 columns.
UniqueID (autonumber)
PatientID (FK to patient table)
QuestionID (FK to question table)
Answer

Thank you, that was definitely the case. I have a junction table now with a join to QuestionID in the question table. Each row/record now stores the DsqiidID, QuestionID and a ScoreID. Much simpler!

 

[james.crust]

Just wanted to say thanks for everyone's help. I think I am pretty much there with it now. My main mistake was defining the relationship one to one, rather than many to many (each Dsqiid can have many questions and scores, each question/score can belong to many Dsqiids). This required the junction table others suggested above.

There were still a couple of niggles. I ended up using a continuous form for the scoring so I could enter a score into the junction table for each question and I wanted the form to display all questions at the start of scoring, so I populated the junction table with DsqiidID and QuestionID when the user clicks on the relevant section to start the scoring. This had the benefit of just needing 2 controls (QuestionID, ScoreID) on the form. I needed to change the relationship to remove the ScoreID as no score had been entered yet:

Questions and responses are now stored in rows, rather than columns as before, as Pat mentioned:

Which means I can get to my original goal!

Thanks again all.

 

[Pat Hartman]

Glad you got it working. EVERYONE who doesn't understand how relational databases work, makes their questionnaires flat with a question per column and therefore has to hard code the forms and struggle with graphs and calculating statistics. And also, way too many people who know better too.

You grasped the concept immediately and ran with it. You have a future in software development if you get tired of your day job