Duplicate flagging variation 1

[LtCmdrData]

How can i ask this with seming a little dense, i am however bashing my head against the desk in frustration. i have a table of data containing multiple unique customer id numbers and multiple non unique business codes, i also have a URN applied to each record in the database. What i need to do is flag where there is a duplication of the business codes and apply the customer id into a new field. that was the simple partwhat i need to do as well is where there is a duplicate apply only the customer id from the lowest number URN. Effectivley end up with something like this.

URN CUST_ID BUS_ID DUPE_CUST_ID
1 a 1 a
2 b 2 b
3 c 3 c
4 d 3 c
5 e 4 e
6 f 4 e
7 g 4 e
8 h 1 a
9 i 5 i

Any help at all would be appreciated.

 

[dhookom]

If I understand correctly, try something like:

SELECT tblNoNameGiven.*, (SELECT Min(CUST_ID) FROM tblNoNameGiven n WHERE n.BUS_ID = tblNoNameGiven.BUS_ID) As DUPE_CUST_ID
FROM tblNoNameGiven;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[LtCmdrData]

still rather confused what i am lookin at code wise, we had a clean out in the office and migrated from act to access and one guess who wasn't told, the only one who uses the stuff. CUST_ID is not an ordered sequence although unique they do contain unordered alpha prefixes that was the reason for wanting to run against the URN as that is a fair indication of age of data and i want to flag with the oldest CUST_ID the field DUPE_CUST_ID has been generated by me but is still as yet an empty colomn. Have i explained myself any better or am i just talking myself in circles.

 

[dhookom]

I missed that in your original request. Try

SELECT tblNoNameGiven.*, (SELECT TOP 1 CUST_ID FROM tblNoNameGiven n WHERE n.BUS_ID = tblNoNameGiven.BUS_ID ORDER BY URN) As DUPE_CUST_ID
FROM tblNoNameGiven;

Duane MS Access MVP
Now help me support United Cerebral Palsy