Group By on Report (1 Viewer)

[ggovensky]

Hil, I have a report that runs from a query here is the Codeing.

SELECT [Employee Monitoring].EmployeeID, [Employee Monitoring].Program, [Employee Monitoring].[Client Number], [Employee Monitoring].Date, (([eval1])+nz([eval2])+nz([eval3])+nz([eval4])+nz([eval5])+nz([eval6])+nz([eval7])+nz([eval8])+nz([eval9])+nz([eval10])+nz([eval11])+nz([eval12])+nz([eval13])+nz([eval14])+nz([eval15])+nz([eval16])+nz([eval17])+nz([eval18])+nz([eval19])+nz([eval20]))/[Employee Monitoring]![Items Evaluated] AS Total
FROM [Employee Monitoring]
WHERE ((([Employee Monitoring].Date)>[Start Date (mm/dd/yyyy)] And ([Employee Monitoring].Date)<[End Date (mm/dd/yyyy)])) OR ((([Employee Monitoring].Program)=[Enter Program Number]) AND (([Employee Monitoring].[Client Number])=[Enter Client Number]));

I am trying to make "Program" and "Client Number" grouped by. I do not want dupiciates showing up and I used the Hide Duplicates set to "YES" but that did not work the way I expected it to show.
For instance I have two "Clients Numbers" 1000 and 6000 about 15 total each and "Program" 1094 for both. I only want to show the rows that have 1000 and 1094 and 6000 and 1094 ONLY. Or if they have a "TOTAL"

Hope someone can help me. Thanks alot.

 

[Rob.Mills]

Give this a shot:

SELECT [Employee Monitoring].EmployeeID, [Employee Monitoring].Program, [Employee Monitoring].[Client Number], [Employee Monitoring].Date, (([eval1])+nz([eval2])+nz([eval3])+nz([eval4])+nz([eval5])+nz([eval6])+nz([eval7])+nz([eval8])+nz([e
val9])+nz([eval10])+nz([eval11])+nz([eval12])+nz([
eval13])+nz([eval14])+nz([eval15])+nz([eval16])+nz([eval17])+nz([eval18])+nz([eval19])+nz([eval20]))/[Employee Monitoring]![Items Evaluated] AS Total
FROM [Employee Monitoring]
WHERE ((([Employee Monitoring].Program)=1094) AND (([Employee Monitoring].[Client Number])=1000 Or ([Employee Monitoring].[Client Number])=6000) AND (([Employee Monitoring].Date)>[Start Date (mm/dd/yyyy)] And ([Employee Monitoring].Date)<[End Date (mm/dd/yyyy)]));

 

[ggovensky]

Humm, dosent seem to work right. Says Expression is typeed incorrectly, or it is too complex to be evaluted. For example, a numeric expression may contain too many complicated elements. I will play around with the codeing though see if I can get something to work. Thanks for you help though.

 

[ggovensky]

Also looking at the codeing I see

WHERE ((([Employee Monitoring].Program)=1094) AND (([Employee Monitoring].[Client Number])=1000 Or ([Employee Monitoring].[Client Number])=6000) AND (([Employee Monitoring].Date)>[Start Date (mm/dd/yyyy)] And ([Employee Monitoring].Date)<[End Date (mm/dd/yyyy)]));

There will be other Programs and Client Numbers 1094 and 1000 and 6000 is just examples. Ill break it down more.

3 headings on the report.
Program, Client, and Average (total)

Here is what it looks like

Program Client Average
1094 1000
1094 1000 5.4
1094 1000 5.0
1094 6000 1.2
Average 11.6

What I need it to look like is
Program Client Agerage
1094 1000 10.4
1094 6000 1.2
Average 11.6

If anyone can help me Id be very greatfull.

 

[Rob.Mills]

You'll probably have to create another level query. Base another query on the query that you've already setup. Then sum the Total column.