keri wrote:
Quote:
Hi,
>
I have recently being doing a lot of work in Excel so apologies if I
refer to things in Excel terms. I need to create a report and cannot
seem to get the data from my tables / queries in the right place to
get the report I need.
>
I have table / field structure as below
TblAccounts
Fields in TblAccounts - AccountID & AccountName
>
TblAssessments
Fields in TblAssessments - AccountID & AssessID
>
TblAssessmentValues
Fields in TblAssessmentValues - AssessID & ValueID & Value
>
Each account may have 0 - 50 assessments, and each assessment will
have 10 values.
>
I need to run a report that shows all assessments between certain
dates. (That's easy). However I then want to know how many of the
assessments were completed correctly eg. ones that do not have any
null values in the Value fields of the TblAssessmentValues.
>
My report could look like this;
AccountID AssessID CountofValuesCompleted
>
12345 1 9
12356 2 10
12345 3 4
12567 4 10
>
etc etc. From the report above I would know 2 of the assessments had
been completed correctly as two of them show 10 in the
CountOfValuesCompleted.
>
I have no idea where to start although I have been playing with this
all day!
>
Thanks in advance.
>
If I understand you correctly, I would create a query on
TblAssessmentValues.
Open the query builder and add TblAssessmentValues. Then drag down
AssessID 2 times. Then the Values field. Now enter in the column
containg the Values Field
ValuesCount : IIF(Not IsNull([Values],1,0)
From the menu select View/Totals. This will make this a totals query.
Under the first AssessID make it GroupBy. Under the second, select
Count. Under the ValuesCount column make it a Sum.
You now will have a total count of AssessIDs. You will have the count
of those completed. You also can subtract the count from completed to
get a not-completed count also.
Now you can create another query for the report with the tables
TblAccounts and TblAssessments. Add the TotalsQuery also. You can set
an Inner or Left join link on TotalsQuery. Dbl-Click on the
relationship line between TblAssessments and TotalsQuery and then select
all records must match or select All TblAssessments and those that match
in TotalsQuery. Filter as desired.
