Create Subtotal in Access 2010 Query

[alcharbonneau]

Good Evening

I would like to create a subtotal in an Access 2010 query. As you will note from the attached query, record number 267488 and 267535 have entered leveral lines of values. I would like to create a subtotal column, I suppose, that subtotals the values for each record number.

Thank you for taking the time to read and/or respond to this request.



Al

[June7]

This is what reports are for. Take advantage of Grouping & Sorting with aggregate calcs in footers.

[alcharbonneau]

Thank you for responding.

Can you recommend a good newbie reference on reporting writing in Access 2010?

Thanks again.

Al

[June7]

Basic Access functionality. Start with Access Help for guidelines on report designer. Search the Help for:

reports

report design

create a simple report

create a grouped or summary report

[alcharbonneau]

I'll give it a try.

Thanks

[qa12dx]

make a new query, dont choose any tables, click on SQL (top left), paste the below code;

SELECT Hosp_2011_NMRC.RPT_REC_NUM, Sum(Hosp_2011_NMRC.ITM_VAL_NUM) AS SumOfITM_VAL_NUM
FROM Hosp_2011_NMRC
GROUP BY Hosp_2011_NMRC.RPT_REC_NUM;

run query.

that's what u wanted?

[alcharbonneau]

Bingo. Thank you for your very helpful suggestion.

Al

[June7]

An aggregate query will give you summary data but if you want to still view the detail records that contribute to the sum, use a report.

[alcharbonneau]

Yes, thank you.

Al