How do I count a one-to-many relationship in a report? 1

[MrKABC]

Hi all!

I am trying to generate a report that counts the number of questions answered by applicants. I am using two tables (more, but they aren't relevant to this question):

Table A: (main table)
[ul]
[li]review number (primary key)[/li]
[li]Date of review[/li]
[li]applicant info[/li]
[li]etc, etc[/li]
[/ul]

Table B: (questions table)
[ul]
[li]review number (this is the link to table A, and is the MANY part of the relationship)[/li]
[li]Question 1[/li]
[li]Question 2, 3, etc (you get the idea)[/li]
[/ul]

What I *want* to do is generate a report that says:
BETWEEN (date of review X) and (date of review Y)
Question 1 (x number of responses)
Question 2 (x number of responses)

etc.

What it is actually doing:
Question 1 (x number of responses on date X)
Question 1 (x number of responses on date Y)
Question 1 (x number of responses on date Z)
Question 2 (same, etc)

It's driving me nuts. I am thinking that the "select" query behind the report is to blame here, but I don't know if I need some other type of query? Will that help?

I am hoping that it's something minor that I have overlooked. I appreciate any and all suggestions/resources!

 

[dhookom]

You could try to first normalize your table structure with a union query:

SELECT [Review Number], 1 as Question, [Question 1] as Answer
FROM TableB
UNION ALL
SELECT [Review Number], 2, [Question 2]
FROM TableB
UNION ALL
SELECT [Review Number], 3, [Question 3]
FROM TableB
UNION ALL
SELECT [Review Number], #, [you get the idea]
FROM TableB;

You can then summarize your data with totals queries.


Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Thank you Duane, for your response! I tried the Union query, I think I definitely did something wrong. Or rather, it did what I told it to do, but not what I want!

I think it's my fault for trying to make the problem TOO clear... I distorted what I was asking. Let me try again:

MY CODE:

SELECT  ErrorList.ReviewDate, ErrorList.BranchName, ErrorList.EmployeeID, ErrorList.Item, Count(ErrorList.Item) AS CountOfItem, ErrorList.CategoryCode, ErrorList.SelectorCode, ErrorList.Description
FROM ErrorList
GROUP BY ErrorList.ReviewDate, ErrorList.BranchName, ErrorList.EmployeeID, ErrorList.Item, ErrorList.CategoryCode, ErrorList.SelectorCode, ErrorList.Description
ORDER BY ErrorList.Item;

THE CAST OF CHARACTERS:
"Item" is the unique identifier for each error a person can make. There are about 30 different errors (error 1, 2, 3, etc) The Category/Selector/Description is just the plain English translation of the "Item" (error).

I have a VBA report form that generates a DOCMD statement to print a report between a "From" and "To" date, further selectable by BranchName or EmployeeID.

What I am after is to have this query generate the report within the constraints of "From" and "To" dates, and either a BranchName (really a numeric value) or EmployeeID.

The report does what I said above: It generates unique count for EACH "Item" on EACH "ReviewDate". That's the hangup; I would like EACH "Item" total count ONLY (within the date/branch/employee parameter).

Too much to ask? I just *know* that the GROUP BY... is messing me up but I am not sure how to remedy this. Help would be appreciated!

 

[SkipVought]

So remove date/branch/employee form your select and group by.

 

[MrKABC]

Thank you Skip for your input!

Unfortunately, if I remove the date/branch/employee from the select the VBA form will fail...

 

[MrKABC]

Gahhh... no edit feature... Oops...

I should add that if I remove date/branch/employee I also will have no way of distinguishing when the employee/branch errors were made.

 

[dhookom]

You can include all of the fields in a base query that is filtered by the values from the controls on your form. Then feed the base query into a totals query for your report's record source.

Another alternative is to use code to change the SQL of your query to supply a where clause for filtering and only the appropriate fields for the grouping and counting.

Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Hi Duane:

Just now getting back to the database.

I tried creating a base query/totals query but I keep getting missing field errors. Wow, this sure is frustrating.

The From/To date and Employee Name filters are located on a form. The user enters the dates, then optionally selects an employee name from a combo box (leaving it blank selects all).

The code executed:

'******This executes the report(s)!
    
    DoCmd.OpenReport stDocName, ReportDest, , strWhichList & ".[ReviewDate] BETWEEN" & "#" & ddFromDate & "#" & "AND" & "#" & ddToDate & "#" & strBranch & strEmployee

The report relies upon the "ReviewDate" and (optionally) the Branch and/or Employee ID.

Running the report fails because the underlying report query is a SELECT query that selects EACH item on EACH review date. What I want is a count of all unique values between a specified date range.

Am I asking too much out of Access? It's frustrating to ALMOST know. (tearing hair out).

 

[dhookom]

We don't know the SQL view of any of your queries. When you need something complex, consider building a SQL statement based on user input and assign it to the SQL property of the query that serves as your report's record source. You won't need to use any WHERE CONDITION in your DoCmd.OpenReport method.

Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Hi Duane!

Sorry for beating a dead horse. I am close, just having issues with two (what seem to be) mutually exclusive concepts. Here is the SQL statement that ALMOST works:

SELECT AuditErrorList.ReviewDate, MainAuditList.BranchName, MainAuditList.EmployeeID, AuditErrorList.Item, [b][COLOR=#A40000]Count(AuditErrorList.Item)[/color][/b] AS CountOfItem, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode, AuditErrorList.Description
FROM MainAuditList RIGHT JOIN AuditErrorList ON MainAuditList.AuditID = AuditErrorList.AuditID
GROUP BY [b][COLOR=#A40000]AuditErrorList.ReviewDate[/color][/b], MainAuditList.BranchName, MainAuditList.EmployeeID, AuditErrorList.Item, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode, AuditErrorList.Description
HAVING (((AuditErrorList.Item)<=117 And (AuditErrorList.Item)<129))
ORDER BY MainAuditList.EmployeeID, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode;

Everything works how I want it to, except (bolded in the code above):
[ul]
[li]Count(AuditErrorList.Item): This counts the total number of errors.[/li]
[li]AuditErrorList.ReviewDate: Each review may occur on a different date. There is a one (review) to many (error) relationship. [/li]
[/ul]
I run a report that takes all the reviews between two given dates, and I'd like to count the number of errors. What is killing me is that the GROUP BY insists on counting each error for each day, giving me a count of the error on the first, and the third, etc. I don't want that, I want a count of each error within the date range, as in "Error #1 occurred 20 times from October 1, 2014 to October 31, 2014. If I manually reset all of the dates to a single date, the above SQL will do exactly what I want, but that isn't an option when the month/year changes.

Is this even possible? How do I get some sort of date range rather than a GROUP BY? (BTW, the other GROUP BY parameters are perfect the way they are, just not the dates)

 

[dhookom]

Can you create a "starter" query

SELECT AuditErrorList.Item, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode, AuditErrorList.Description
FROM AuditErrorList
WHERE ReviewDate Between Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtToDate

Then replace AuditErrorList with the name of your query.

If you can't figure this out, it would really help to understand your table relationships.

Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Duane, this is EXACTLY what the doctor ordered!

You were right, the key to getting the summary information is the "query of a query". I didn't know how to do this before and I am still not sure I understand the theory behind it, but I will definitely take the win and move forward.

A star for you and my complete gratitude!

Final base query code (that worked):

SELECT AuditErrorList.Item, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode, AuditErrorList.Description, AuditErrorList.AuditID
FROM AuditErrorList
WHERE (((AuditErrorList.ReviewDate) Between Forms![MainForm].[NavigationSubform].[Form]![txtFromDate] And [Forms]![MainForm].[NavigationSubform].[Form]![txtToDate]))
ORDER BY AuditErrorList.Item;