INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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)
  • review number (primary key)
  • Date of review
  • applicant info
  • etc, etc
Table B: (questions table)
  • review number (this is the link to table A, and is the MANY part of the relationship)
  • Question 1
  • Question 2, 3, etc (you get the idea)
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?

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

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

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

CODE --> SQL

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

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

(OP)
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:

CODE --> SQL

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! :) :)

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

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

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

(OP)
Thank you Skip for your input!

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

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

(OP)
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.

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

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

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

(OP)
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:

CODE -->

'******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).

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

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

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

(OP)
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:

CODE --> ACCESS

SELECT AuditErrorList.ReviewDate, MainAuditList.BranchName, MainAuditList.EmployeeID, AuditErrorList.Item, Count(AuditErrorList.Item) AS CountOfItem, AuditErrorList.CategoryCode, AuditErrorList.SelectorCode, AuditErrorList.Description
FROM MainAuditList RIGHT JOIN AuditErrorList ON MainAuditList.AuditID = AuditErrorList.AuditID
GROUP BY AuditErrorList.ReviewDate, 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):
  • Count(AuditErrorList.Item): This counts the total number of errors.
  • AuditErrorList.ReviewDate: Each review may occur on a different date. There is a one (review) to many (error) relationship.
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)

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

Can you create a "starter" query

CODE --> SQL

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

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

(OP)
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):

CODE --> SQL

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; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close