Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate flagging variation 1

Status
Not open for further replies.

LtCmdrData

Technical User
Jan 4, 2008
11
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.
 
If I understand correctly, try something like:
Code:
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
 
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.
 
I missed that in your original request. Try
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top