471,049 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

DCount groups in a report

2
Hiya,

Fairly new to Access and was wondering what the best way to perform Dcounts on groups in an Access report.

For example, I have 10 employees and the different instances of jobs they can perform are either Clean, Shampoo or Vacuum. I have a query called Details that shows a history of all the jobs they have performed. On my report I want a total for each employee of how many of each job they have performed.

This is what I know of the Dcount function but the result I get (obviously) is the TOTAL number of the jobs at the end of each group.

These are the text boxes I place in the employee footer:
=Dcount("[ServiceID]","Details","[Service ID] = "Clean")
=Dcount("[ServiceID]","Details","[Service ID] = "Shampoo")
=Dcount("[ServiceID]","Details","[Service ID] = "Vacuum")

Can anyone please help me with the syntax to Dcount the values for each individual employee and not the entire total? I know that the reason the total value is being returned is because I am Dcount'ing the entire Details query. How am i able to Dcount each instance of the employee?

Thanks in advance
Nov 17 '06 #1
2 7653
NeoPa
32,337 Expert Mod 16PB
To be most helpful we need the fieldnames used in the report as well as the SQL of the record source.
I suspect there's a better, more natural, way, but until more info is available...
Try :
Expand|Select|Wrap|Line Numbers
  1. =Dcount("[ServiceID]","Details","(([EmpName]='" & [txtEmpName] & "') AND ([ServiceID]='Clean'))")
or something similar that matches the field names you have.
Nov 17 '06 #2
Wingz
2
To be most helpful we need the fieldnames used in the report as well as the SQL of the record source.
I suspect there's a better, more natural, way, but until more info is available...
Try :
Expand|Select|Wrap|Line Numbers
  1. =Dcount("[ServiceID]","Details","(([EmpName]='" & [txtEmpName] & "') AND ([ServiceID]='Clean'))")
or something similar that matches the field names you have.

That did the trick!! Thanks Neo!
Nov 17 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Simon Matthews | last post: by
1 post views Thread by Megan | last post: by
7 posts views Thread by kaosyeti | last post: by
5 posts views Thread by den4673 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.