totals of first entry

[John Cunradi]

I have a table that contains an item ID and quantity fields.

There may be multiple instances of an Item ID but the quantity will always be the same.

I would like to generate a total for the quantity without including any duplicates.

This will be entered in the expression builder of a report. Doing it without the duplicates is the tricky part for me.

Thanks in advance.

[Stan Mathews]

RE: totals of first entry

"without including any duplicates"

Any duplicates of what? Are you saying you want a grand total of the quantity field, totaling only one instance of each Item ID?

You could summarize the table (group rows by Item ID) to a temp table and specify "first" for the operation on the quantity field. Then report on this table.

You could put a group on your report and place the fields in the group header, specify first Item ID and first quantity, then dow away with the detail section.

[John Cunradi]

RE: totals of first entry

Let me give an example:

Id Qty
A 5
B 6
C 8
A 5

Grand total would be 19, the second instance of "A" would be ignored in the sum.

I suspect the the "First()" command will work somewhere in this but I'm not sure.

[Bill Warner]

RE: totals of first entry

I'd suggest creating an index that only allows unique records, and make the sort order ID+STR(QTY). This way, duplicates will not show.

If you want the total on the form, you could use DBSUM and specify the index you created. For a total in a report, run it based on that index.

[John Cunradi]

RE: totals of first entry

I got it by doing a summarize with a sum quantity and all other fields used the "first" option and then basing reports on that.

Thanks for the help.