Aggregate query

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

In performing a sum query, where 3 of 4 fields are "group"
by, and the 4th is "count" I get a counted list of
distinct items, each of which is the same in the
three "group by" fields. What I want is a list of the
text identifiers, as a new, calculated field, that satisfy
each of the counted groups. This is a part list, and many
part definitions (which require the three fields) are
duplicates. I want to list the "Reference ID's" that are
counted in each duplicated part.
 
There's just not enough info here to give a solid answer, maybe post the
structure(s) of the table(s) involved, and maybe your current SQL.

What are the text identifiers?
 
This is what I call a parts list query. The table queried
would look like:

Ref.Des. Part No. Value
-------- -------- -----
R1 ABC 50
R2 ABD 100
R3 ABC 200
R4 ABC 50

Note that there is a duplicate combination: ABC 50.

The SUM query I am after would group the Part No. and
Value fields, and aggregate all the Ref. Des. entries that
are found in a unique group. Thus the output would be:

Ref.Des. Part No. Value
-------- -------- -----
R1, R4 ABC 50
R2 ABD 100
R3 ABC 200

If, in the SUM query, I select:

Ref.Des. Part No. Value
-------- -------- -----

COUNT GROUP BY GROUP BY

Then I get the output:

Count Ref.Des. Part No. Value
-------------- -------- -----
2 ABC 50
1 ABD 100
1 ABC 200

I need to know how go get from this to the list of Ref.
Des. entries, in addition to or instead of the COUNT.

Larry
 
Back
Top