Query to group duplicates

[Christian]

Hi NG,

I'm trying to make sense of some data I have imported to Access from various
sources.

I have one table with two fields; A and B.

Field A contain duplicate values

Field B contain duplicate values

So one A links to several Bs.

And one B might link to several As (this is not always the case).

So the table contains is a many-to-many relation.

I would like to calculate a new Group - a "Field C" - in a query that would
group A and B records.

Example of desired query result:

----------------------------------------------------

A; B; C

----------------------------------------------------

1; 5; X; first group

1; 6; X; because A is 1 (duplicate)

2; 5; X; because B is 5 (duplicate)

2; 7; X; becase A is 2 and above row has B=5 and A=2.

3; 8; Y; new group as A=3 is not among previous A values and neither is 8
among B values

3; 9; Y; because A=3 is part of group

----------------------------------------------------

I'm not sure if this is easy or impossible. I have been trying to wrap my
head around this problem for a few hours now and I'm stuck.

Any ideas on how to attack this problem is warmly welcome

- Chr

 

[kingston via AccessMonster.com]

I don't see how this can be done without some sort of recursion so I think
you'll have to write a procedure of some sort or run a bunch of queries
manually.

Create a temporary table of keys: Value (1,2,3,etc.) & Field (A or B).
Seed the table with an A value (e.g. 1, A).
Create an append query that adds all B values corresponding to all A values
in the temporary table.
Create an append query that adds all A values corresponding to all B values
in the temporary table.
Run the queries until no more records are added to the temporary table
(because of uniqueness).
Every record in the original table that corresponds to a record in the
temporary table is in the same group.
Run again for a different seed value (that hasn't been assigned a group).

 

[Christian]

Thanks kingston,
Seems to be a tricky one then.
Looping through a ranges and inserting the results to a temporary table is a
bit over my current access skills.
But I'll give it a go, unless someone out there has a better solution

I have 6951 records
Out of those
- 815 unique groups (field A)
- 6033 unique parts (field B)
- Out of the 6033 parts 722 belong to more than one group
Hopefully it will not take too long to loop through the data and insert the
new ad-hoc groups to the table.
- Chr