Grouping items together to maintain one total on a query

[tmegale]

Grouping items together to maintain one total on a REPORT

I am trying revise a report on a group certain records to give me the total qty in each record grouped together. See below, these are items listed on my report from a query.

Item# Qty HCPCS Total Price
5b999a 2 A4725 $2.00
5b999b 2 A4725 $6.00
5b999c 3 A4798 $3.00
5b999e 4 A4798 $4.00

In the footer of the report. I would like to see a count of all HCPCS by group..i.e.

HCPCS Total Qty Total Price
A4725 4 $8.00
A4798 7 $7.00

Any help would be appreciated!

 

[sfreeman@co.mer]

Should be easy. Just use a 'totals' query,select your groupby fields, and set the totals field to sum your QTY field.

Something like:

SELECT
tblHCPCS.HCPCS
, Sum(tblHCPCS.Qty)

AS
SumOfQty

FROM
tblHCPCS

GROUP BY
tblHCPCS.HCPCS;

HTH

 

[tmegale]

Hi Sam

OK, will try to figure this out. However, all of my information is in One Query, should i just make another Calculated Field? And use the formula you gave? I just looked under queries, I saw an Append and Delete Query, but not a Totals Query.

Thanks for your help! You are right, asking the "right" question makes all the difference. I hope I am asking the right one.

Thanks,
Tiffani

 

[sfreeman@co.mer]

Easy mistake, you select the Totals query via the toolbar button that looks like a greek symbol for 'E'. Just hold your mouse pointer over the symbol and it should read 'Totals'.

That will add a Total criteria to you query designer, and you select the fields that you wish to group by. Here it looks like you are grouping by the HCPCS field.

Yes, add a calculated field called SumOfQty in my example. Use that field as the data source in your form to display the totals.

Good luck.

 

[tmegale]

Thanks

Sam, I am revising the question. See below

I am trying revise a report on a group certain records to give me the total qty in each record grouped together. See below, these are items listed on my report from a query.

Item# Qty HCPCS Total Price
5b999a 2 A4725 $2.00
5b999b 2 A4725 $6.00
5b999c 3 A4798 $3.00
5b999e 4 A4798 $4.00

In the footer of the report. I would like to see a count of all HCPCS by group..i.e.

HCPCS Total Qty Total Price
A4725 4 $8.00
A4798 7 $7.00

Any help would be appreciated!