Calculating partial data in a report

[Jsha]

Okay, I have been serching the forums for the past 2 days trying to find something similar but have not found it. I have a query that prompts the user for a project number and a date and pulls all of the employees working on that project and how many hours they worked that particular day.

So the problem is this, I need to have three calculations for their hours. One in the top of the Report that will capture only certain employees total time with a specific category code and another right under it capturing the other employee's total time. Then in the body of the report I am itemizing each employees hours.

Here is the SQL from the query:

SELECT ADS.ADS_DATE, [ADS Detail].ADSDETAIL_REGHOURS, [ADS Detail].ADSDETAIL_OTHOURS, Employee.EMP_NUMBER, Employee.EMP_CATEGORY, [ADS Detail].PHASE_ID, Employee.EMP_FNAME, Employee.EMP_LNAME, Employee.EMP_TITLE, [Project Information].PROJECT_DESCR, [Project Information].PROJECT_NUMBER, [Project Information].PROJECT_CHARGECODE, [Project Information].PROJECT_SURVEYNUM, [Project Information].PROJECT_CONTRACTNUM, [EMP_LNAME] & ", " & [EMP_FNAME] AS FullName
FROM Employee INNER JOIN ([Project Information] INNER JOIN (ADS INNER JOIN [ADS Detail] ON ADS.ADS_ID = [ADS Detail].ADS_ID) ON [Project Information].PROJECT_ID = [ADS Detail].PROJECT_ID) ON Employee.EMP_ID = ADS.EMP_ID
WHERE (((ADS.ADS_DATE)=[WHAT DATE?]) AND (([ADS Detail].PHASE_ID)<>1) AND (([Project Information].PROJECT_NUMBER)=[WHAT PROJECT?]));

So I need to take the categories and add the total time from everyone in that category.

Thank you in advance for you help.

 

[hneal98]

If you run the query outside the report, are you getting the correct data?

 

[Jsha]

Yes, I am getting the correct data. I thought about making seperate queries 1 for each category, but the problem i ran into was duplication of information in the detail section of my report.

 

[sxschech]

Not sure if this is what you need...
I created a report that has totals and information in the report header using unbound text boxes and is populated via code through Dlookup and Dsum. Alternatively, data could be obtained with an SQL statement as well. Remember to replace the code with your textbox, field and query names.

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
     'Use DSum to calculate totals  

    'Example without a conditional "WHERE CLAUSE"   
    Me!txtYr1Total = DSum("YR01", "qryRates")
    Me!txtYr2Total = DSum("YR02", "qryRates")

'Next statement is example with a conditional "WHERE CLAUSE"
    Me!txtYr1Total = DSum("YR01", "qryRates", "Condition = '" & Me.txtCondition & "'")
    
End Sub

 

[hneal98]

You also might consider subreports. One for each of the totals you need.

 

[dhookom]

Try not use extra queries, domain aggregate functions, or code. Assuming you have [Gender] field in your employee table and you want to sum the regular hours worked by females:
[blue] =Sum( Abs([Gender]="F") * [ADSDETAIL_REGHOURS] )[/blue]
Now, say you want to sum the Regular Hours worked by females where the PROJECT_CHARGECODE = 3:
[blue] =Sum( Abs([Gender]="F" AND [PROJECT_CHARGECODE] = 3) * [ADSDETAIL_REGHOURS] )[/blue]

If your "domain" includes more than just your report's record source, then you may need to use a domain function.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]