Qyerying to get data for report

[keri]

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

TblAssessmentVa lues
Fields in TblAssessmentVa lues - 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 TblAssessmentVa lues.

My report could look like this;
AccountID AssessID CountofValuesCo mpleted

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
CountOfValuesCo mpleted.

I have no idea where to start although I have been playing with this
all day!

Thanks in advance.

[salad]

keri wrote:

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

TblAssessmentVa lues
Fields in TblAssessmentVa lues - 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 TblAssessmentVa lues.

My report could look like this;
AccountID AssessID CountofValuesCo mpleted

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
CountOfValuesCo mpleted.

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
TblAssessmentVa lues.

Open the query builder and add TblAssessmentVa lues. 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.

[Larry Linson]

You imply from "completed correctly eg. ones that do not have any
null values in the Value fields of the TblAssessmentVa lues" that you create
records for a given AssessmentID prior to having a Value for it? And, if I
understand correctly, for your data to have meaning, AssessmentID would have
to be unique across all Accounts. Are both these the actual case?

Please clarify, and perhaps someone can offer a useful suggestion. Chances
are, you will use a Totals Query in your solution, but the details of the
solution will differ, depending on the details of your data / database.

Larry Linson
Microsoft Access MVP

"keri" <ke********@hot mail.comwrote in message
news:11******** **************@ r34g2000hsd.goo glegroups.com.. .

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

TblAssessmentVa lues
Fields in TblAssessmentVa lues - 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 TblAssessmentVa lues.

My report could look like this;
AccountID AssessID CountofValuesCo mpleted

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
CountOfValuesCo mpleted.

I have no idea where to start although I have been playing with this
all day!

Thanks in advance.

[keri]

To clarify;
There are actually 13 possible assessment values. To be classed as
complete at least of 10 of these must not be null.

I'm not sure I understand your question about AssessmentID, however
each AccountID may have 0 - infinty assessments, and yes each
AssessmentID would be unique regardless of which account it was linked
to.

I hope this helps you help me!