Summing all records but displaying only a few

[Gina]

Hi! I'm developing a material requirement database that
accounts for receipts, allocations, and uses of sheet
material so that we always have a current inventory and
I've run into a problem I can't figure out.

I want a report (I think) based on this query:
SELECT tblMatl.MaterialDesc, tblMatl.MatlType,
tblMatl.MatlLocation, tblMatl.MatlNotes,
tblMatlActivity.Activity, tblMatlActivity.Qty,
tblMatlActivity.ActivityDate, IIf([Activity]="R",[Qty],
([Qty]*-1)) AS Effect
FROM tblMatl INNER JOIN tblMatlActivity ON tblMatl.MatlID
= tblMatlActivity.fkMatlID
WHERE (((tblMatl.MatlType)="Crop"));

As this database grows, I'll want to archive certain
records so they don't show up on the report (creating a
very long report), BUT I do want the quantities from those
archived records accounted for in my totals on the report.
I guess what I'm trying to ask is there a way to display
only certain records, but have all the records summed in
my totals?

 

[Mike Painter]

Hi! I'm developing a material requirement database that
accounts for receipts, allocations, and uses of sheet
material so that we always have a current inventory and
I've run into a problem I can't figure out.

I want a report (I think) based on this query:
SELECT tblMatl.MaterialDesc, tblMatl.MatlType,
tblMatl.MatlLocation, tblMatl.MatlNotes,
tblMatlActivity.Activity, tblMatlActivity.Qty,
tblMatlActivity.ActivityDate, IIf([Activity]="R",[Qty],
([Qty]*-1)) AS Effect
FROM tblMatl INNER JOIN tblMatlActivity ON tblMatl.MatlID
= tblMatlActivity.fkMatlID
WHERE (((tblMatl.MatlType)="Crop"));

As this database grows, I'll want to archive certain
records so they don't show up on the report (creating a
very long report), BUT I do want the quantities from those
archived records accounted for in my totals on the report.
I guess what I'm trying to ask is there a way to display
only certain records, but have all the records summed in
my totals?

You could do a report selecting for only the records you want to see and
include the grand totals as sums using dSum in an unbound field.
I suspect you'll end up doing a little more, probably showing the report
totals as well and explaining or showing the difference somewhere.