Group By Query - find which rows are being grouped

[Guest]

Hello. I need a little help. Is there a way to find out which records were
grouped in a group by query, and maybe flag that record with a specific tag

for example in this scenario:

ID Amount Name
1 123 First
2 123 First
3 123 F-irst
4 345 Second
5 345 Second
6 345 Secon-d
.......
.......



If I put this table in a group by query and Sum the amounts, I take the
LastOfName and LastOfID then I get something like this:


ID Amount Name
3 369 F-irst
6 1035 Secon-d

I would like to be able to know which records were grouped maybe "mark" them
for the record? Something like:

ID Amount Name Flag
1 123 First G1
2 123 First G1
3 123 F-irst G1
4 345 Second G2
5 345 Second G2
6 345 Secon-d G2

Where G1 is Group 1, and G2 is group 2. I would then know based on G1 which
records "can" be grouped

I am not sure if this makes sense, but I need to know what records can be
grouped for a specific table, and just mark them somehow. In the end I need
to show those records in a report. For each set of groups, there will be a
matching record already grouped (this is output by a system) and they will be
compared to make sure they match.

So when the report shows let's say G1 like this:

ID Amount Name Flag
1 123 First G1
2 123 First G1
3 123 F-irst G1

a user will already have a record that looks like this:

ID Amount Name
123AC 369 F-ir-st

They will then look at similiarities based on partial Name, Amount and
partial ID to decide wheather the Amount is the same and the record is the
same
So they'll say, oh OK, my ID of 123AC contains 1, 2 and 3, already showing
above, the amount also matches, and the name is not exaclty the same, but
similiar, so they can be looked at being the same. This is the business rule.
I just have to provide the flagging

 

[Guest]

You can not do it the way you expressed it as it will not group on two
different spellings - First & F-irst OR Second & Secon-d

 

[Guest]

My mistake. Name and amount are always the same. It's the IDs that are
different.

 

[Guest]

Use a totals query to group the information. Make it an append query.
You can append the data into a temporary table, but which will have an
autonumber field in addition to the other fields you have data. That
autonumber field will get the values 1, 2, 3, ... as records are appended to
it, even if you do it in just one batch.

Then using the temp table joined to your data table update a group field
from the autonumber field of the temp table.