Solved

Microsoft Access 2013 Report Grouping levels need to count unique records

Posted on 2014-11-14
7
262 Views
Last Modified: 2014-11-18
I have a report almost complete. I track request from various sources. The database tracks the time spent on each request. dbreqid is the key field and the request has multiple entries for documenting time. When I do a group by requester and count the number of request it counts for each of my time records. One request will have multiple time records. How do I count just the distinct records in the dbreqid field. I have group level for the source but it counts each individual time record not each dbreqid. I tried to group on dbreqid but that doesn't work either. Thanks for the help.
0
Comment
Question by:marlind605
  • 3
  • 3
7 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40442753
You'll have to use a subquery, something like this:

Select
    T.Source,
    T.Requester,
    T.TotalTime,
    Count(*) As Requests
From
    (Select Source, Requester, Sum(TimeSpent) As TotalTime From tblTable Group By Source, Requester) As T
Group By
    T.Source,
    T.Requester,
    T.TotalTime

This should give you the clean figures.
In your report, however, you would do the grouping and counting, Thus it should use the subquery above as source.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40442759
You may need to break the report into a main report with a subreport.  That will allow you to count records separately for each recordset.
0
 

Author Comment

by:marlind605
ID: 40443585
I am looking at the two answers. Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:marlind605
ID: 40448803
Still not getting this. Tried subreports and different queries. Any other help?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449329
That's hard without knowing what you did try.

/gustav
0
 

Author Closing Comment

by:marlind605
ID: 40449899
What I ended up doing was creating two make table queries one to show each request by organization and the other created from the first result to show count by organization and request and then used the added the query to the one I was using for the report. It is not the way I wanted to go but seems to run quick and is accurate. There may be another solution but I couldn't figure it out. Thanks for all the replies.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449938
OK, thanks for the feedback.

/gustav
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now