Count groups rather than detailed records

  • Thread starter Thread starter tsison7
  • Start date Start date
T

tsison7

I generated a report that shows PartNo, Date of Last Shipment, Qty of Last
Shipment. Because there may be more than one shipment of each PartNo, I
grouped the PartNo and summed the Qty so my output looks like these:

Part Date Qty
A 1/1/07 10
B 2/2/07 20

If there were actually 2 shipments of A on 1/1/07 of 5 each, there would be
3 detailed records of the output. How can I get a count of just the unique
PartNo...in this case 2?

Thanks,
 
You may be able to solve the problem merely by changine the query into a
Totals query. Depress the Total button on the toolbar in query design. Group
by Part and Date, and sum Qty. The query now returns only one record for
each combination of Part + Date, and so the count in the report is correct.

Or perhaps you just want a count of parts in the report? If so:
1. In the Sorting And Grouping box (View menu), choose Part, and in the
lower pane of the dialog, choose Yes for Group Header. This adds a Part
Group Header section to the report.

2. Add a text box to this this group header, add a text box with these
properties:
Control Source =1
Running Sum Over All
Name txtPartCountRS
Format General Number

3. In the Report Footer section, add a text box with Control Source of:
=[txtPartCountRS]

Set the Visible property of the Part Group Header section to No if you don't
want to see it.
 
Thanks Allen, both suggestions will work.
--
TIA


Allen Browne said:
You may be able to solve the problem merely by changine the query into a
Totals query. Depress the Total button on the toolbar in query design. Group
by Part and Date, and sum Qty. The query now returns only one record for
each combination of Part + Date, and so the count in the report is correct.

Or perhaps you just want a count of parts in the report? If so:
1. In the Sorting And Grouping box (View menu), choose Part, and in the
lower pane of the dialog, choose Yes for Group Header. This adds a Part
Group Header section to the report.

2. Add a text box to this this group header, add a text box with these
properties:
Control Source =1
Running Sum Over All
Name txtPartCountRS
Format General Number

3. In the Report Footer section, add a text box with Control Source of:
=[txtPartCountRS]

Set the Visible property of the Part Group Header section to No if you don't
want to see it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tsison7 said:
I generated a report that shows PartNo, Date of Last Shipment, Qty of Last
Shipment. Because there may be more than one shipment of each PartNo, I
grouped the PartNo and summed the Qty so my output looks like these:

Part Date Qty
A 1/1/07 10
B 2/2/07 20

If there were actually 2 shipments of A on 1/1/07 of 5 each, there would
be
3 detailed records of the output. How can I get a count of just the
unique
PartNo...in this case 2?

Thanks,
Click to expand...
Click to expand...
 
Back
Top