Matching duplicates within one table

[Scottyp]

Hi - hoping for some help nutting out the query I would require to do the
following - Thought it would be easiest to just demonstrate what I'm trying
to achieve.

Here is how the data looks currently in the table:

Company | Phone | Confirmed Phone
CompanyA 5551111 5552222
CompanyB 5552222
CompanyC 5557777 5558888
CompanyD 5556666 5551111
CompanyE 5554444
CompanyF 5555555 5557777
CompanyG 5558888

To explain, the first phone number field is a correct match against the
company name, whereas the 'Confirmed Phone' contains matched numbers, but
they're in mixed up order. I need to 're-sort' the third column so that all
matching confirmed numbers 'line-up' with the appropriate company.
ie. It should look like this when done:

Company | Phone | Confirmed Phone
CompanyA 5551111 5551111
CompanyB 5552222 5552222
CompanyC 5557777 5557777
CompanyD 5556666
CompanyE 5554444
CompanyF 5555555
CompnayG 5558888 5558888

Hope that makes sense - any help would be greatly appreciated. I haven't
played with this stuff for quite some time.

Cheers!
Scott

 

[Scottyp]

Hi bhicks11,
As mentioned in the subject of my post, the fields which contain duplicates
are within the one table. So there is no relationship other than they match
(But are not in corresponding listed order).

 

[Scottyp]

Yep - Your right. What I'm going to have to do (I think) is write some code
to run through, number by number looking for a match, and then output the
results to a new, 'sorted' table.

Sound reasonable?

And this is why I am confused. You cannot sort fields in the same table to
show up in different records in a single query.

Hi bhicks11,
As mentioned in the subject of my post, the fields which contain duplicates
are within the one table. So there is no relationship other than they match
(But are not in corresponding listed order).

Scotty,

[quoted text clipped - 38 lines]

Cheers!
Scott

 

[Scottyp]

There ya go - I told you it's been a while since I did this stuff. A bit of a
case of 'can't see the forest for the trees'. The three query solution you've
described below is exactly what I needed, and is so pathetically obvious that
I am feeling quite embarassed.

Thanks ver much bhicks11!

I don't fknow Scotty - can't see why you have it set up like this. You could
create two queries: one with just the complaint number, the other with the
phone number and company name. Then create a third query with the first two
queries as the source and relate the complaint number and phone number. This
should result in the final product you are looking for. Maybe I'm missing
something but I don't see what you're doing.

Bonnie
http://www.dataplus-svc.com

Yep - Your right. What I'm going to have to do (I think) is write some code
to run through, number by number looking for a match, and then output the
results to a new, 'sorted' table.

Sound reasonable?

And this is why I am confused. You cannot sort fields in the same table to
show up in different records in a single query.

[quoted text clipped - 9 lines]

Cheers!
Scott