By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Unique ID system -- multiple data sources

P: n/a
I was given a database that has unique IDs for each record such as:

AL00001
AL00002
AL00003
etc

The trouble is, I'm going to need to import data from another source that
will also have IDs such as this and more than likely there are going to be
duplicates. So if I import data that already has an AL00003, how can I get
access to automatically read the table and figure out the latest ID and then
bump it up or update from AL00003 to AL00004?

In this same field the IDs can be anything like AL00001, or CA00001 so I
also want to look at those first two characters and then figure out the max
num thats in the field.

Make sense? I'd like to setup an import query, then run this unique ID/dup
check/update function, then append those temp records into the main table.
Thank you in advance!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Jason wrote:
I was given a database that has unique IDs for each record such as:

AL00001
AL00002
AL00003
etc

The trouble is, I'm going to need to import data from another source that
will also have IDs such as this and more than likely there are going to be
duplicates. So if I import data that already has an AL00003, how can I get
access to automatically read the table and figure out the latest ID and then
bump it up or update from AL00003 to AL00004?

In this same field the IDs can be anything like AL00001, or CA00001 so I
also want to look at those first two characters and then figure out the max
num thats in the field.

Make sense? I'd like to setup an import query, then run this unique ID/dup
check/update function, then append those temp records into the main table.
Thank you in advance!

OK. Create a query to import the new records into a temp table. I'll
call it QImport.

I'll call the temp table Table1. The other table is called Production

Next, create a query (maybe paramter) that has two columns;
AlphaCode : Left(ID,2)
NumericCode : Mid(ID,3)
and save it to whatever, I'll call it Query1

Now create some code...air code follows
Private Sub ImpRecs
Dim rst As DAO.Recordset
Dim rstProd As DAO.Recordset
Dim rstMax As DAO.Recordset
Dim strSQL As STring

'import the new records into Table1
Docmd.openquery "Qimport"

Set rst = Currentdb.openrecordset("Table1",dbopensnapshot)
Set rstProd = Currentdb.openrecordset("Production",dbopendynaset )

If not rst.BOF Then
rst.MoveFirst
'loop thru all new records
DO while not rst.EOF
'see if rec exists with same id
rstProd.FindFirst "ID = " & rst!ID
If rst!NoMatch then
'rec not found. call insert routine
InsertIt rstProd, rst, rst!ID
Else
'determine max number in Production
strSQL = "Select Max(NumericCode) As "MaxNum From Query1 Where
AlphaCode = '" & Left(rst!ID,2) & "'"

'now get the max num
set rstMax = Currentdb.openrecordset(strSQL,dbopensnapshot)
'you'll need to format MaxNum with leading
'zeros.
Insert rstProd, rst, Left(rst!ID,2) & _
rstMax!MaxNum + 1
rstMax.close
set rstMax = Nothing

rst.MoveNext
Loop
endif
rst.close
rstprod.close
set rst = Nothing
set rstprod = nothing
msgbox "Done with update"
end sub

Private Sub InsertIt(rstProd, rst, NewID)
'now add via AddNew or a SQL Insert. Use the NewID for the ID
'in all cases since the NewID was determined in the calling code
'....AddRec code here
ENd Sub

Nov 12 '05 #2

P: n/a
wow, I'll give that a shot.... thank you so much!!
"Salad" <oi*@vinegar.com> wrote in message
news:gh******************@newsread2.news.pas.earth link.net...

OK. Create a query to import the new records into a temp table. I'll
call it QImport.

I'll call the temp table Table1. The other table is called Production

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.