grouping ...

[jer]

columnA ColumnB ColumnC columnD
12345 23 78956 10.00
12345 23 89153 15.00
56780 23 15687 20.00
56780 23 15890 20.00
12345 23 78853 30.00
12345 23 87568 30.00

I am importing a table from an external source structured as above
and if I sum columnD based on grouping on columnA I would get total
for
12345 = 85.00 4 detail records
56780 = 40.00 2 detail records

what I want to get however is
12345 = 25.00 2 detail records
56780 = 40.00 2 detail records
12345 = 60.00 2 detail records

Apart from grouping is there any other query I can build that
would give me the expected result

 

[PieterLinden via AccessMonster.com]

columnA ColumnB ColumnC columnD
12345 23 78956 10.00
12345 23 89153 15.00
56780 23 15687 20.00
56780 23 15890 20.00
12345 23 78853 30.00
12345 23 87568 30.00

I am importing a table from an external source structured as above
and if I sum columnD based on grouping on columnA I would get total
for
12345 = 85.00 4 detail records
56780 = 40.00 2 detail records

what I want to get however is
12345 = 25.00 2 detail records
56780 = 40.00 2 detail records
12345 = 60.00 2 detail records

Apart from grouping is there any other query I can build that
would give me the expected result

I am not clear on how the grouping is supposed to work. What's the logic
behind it? Do you want just the first two records per ColumnA?

 

[John W. Vinson]

columnA ColumnB ColumnC columnD
12345 23 78956 10.00
12345 23 89153 15.00
56780 23 15687 20.00
56780 23 15890 20.00
12345 23 78853 30.00
12345 23 87568 30.00

I am importing a table from an external source structured as above
and if I sum columnD based on grouping on columnA I would get total
for
12345 = 85.00 4 detail records
56780 = 40.00 2 detail records

what I want to get however is
12345 = 25.00 2 detail records
56780 = 40.00 2 detail records
12345 = 60.00 2 detail records

Apart from grouping is there any other query I can build that
would give me the expected result

I don't understand your question. Tables have no order; the only things you
can use for grouping are values actually stored in the records in the table.
Are you assuming that the first two 12345 rows are somehow "grouped" already?
Because they aren't!

More info please.

 

[Marshall Barton]

columnA ColumnB ColumnC columnD
12345 23 78956 10.00
12345 23 89153 15.00
56780 23 15687 20.00
56780 23 15890 20.00
12345 23 78853 30.00
12345 23 87568 30.00

I am importing a table from an external source structured as above
and if I sum columnD based on grouping on columnA I would get total
for
12345 = 85.00 4 detail records
56780 = 40.00 2 detail records

what I want to get however is
12345 = 25.00 2 detail records
56780 = 40.00 2 detail records
12345 = 60.00 2 detail records

Apart from grouping is there any other query I can build that
would give me the expected result

Not unless there are other fields that can be used to
separate the 12345 records into two groups.

It is important for you to understand that the records in a
table are NOT ordered in any way that a human can make sense
out of the way they appear. The ONLY way to sort records is
by using a query with an Order By clause.

Based on that, there is no guarantee that the records you
posted above will appear in that order. Even if they appear
that way today, you can not rely on them appearing the same
way in the future.

 

[jer]

thank you all - I understand,