Totals in Group Footer

[dchase]

I am new to Access reports and am trying to show totals for one field based on results from another field. My reports consists of these fields:
Name
Job Title
Gender
Ethnicity
Hours Billed

Grouped by Client

I'm trying to total the hours billed based on gender and the hours billed based on ethnicity.

Any help you can provide is much appreciated.

 

[dhookom]

Do you want to display both of these results in a single report? They each require their own query
SELECT Gender, Sum([Hours Billed]) as TotHrs
FROM tblA
GROUP BY Gender;

SELECT Ethnicity, Sum([Hours Billed]) as TotHrs
FROM tblA
GROUP BY Ethnicity;

Each of these are the record source for a report. If you need them on one report, create a new report and add these to reports as subreports.


Duane
MS Access MVP

 

[ChrisCalvert]

If your records are not grouped by ethnicity or gender, I am guessing this is in a separate section at the end or begining. If that's the case you could use DSum.

DSum(expr, domain, criteria)

Ex.
DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'&quot

Just put a textbox on the report and put this as the source.
However, the Domain functions (DSum, DCount, etc.) tend to be slow.

-Chris

 

[dhookom]

Using DSum() might create a maintenance nightmare. If a new ethnicity is added or changed then the report will have to be modified. A much more flexible and efficient solution is to use one or more subreport since they are self maintaining and run faster.

Duane
MS Access MVP

 

[dchase]

Thanks for your suggestions. I'll try them and let you know how it works.