Unique ID system -- multiple data sources

[Jason]

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!

[Salad]

Re: Unique ID system -- multiple data sources

Jason wrote:
[color=blue]
> 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!
>
>[/color]
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.openr ecordset("Table 1",dbopensnapsh ot)
Set rstProd = Currentdb.openr ecordset("Produ ction",dbopendy naset)

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.FindFir st "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.openr ecordset(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(rstPro d, 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

[Jason]

Re: Unique ID system -- multiple data sources

wow, I'll give that a shot.... thank you so much!!


"Salad" <oil@vinegar.co m> wrote in message
news:ghegc.1483 9$k05.1963@news read2.news.pas. earthlink.net.. .[color=blue]
>
> 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
>[/color]

[Jason]

Re: Unique ID system -- multiple data sources

wow, I'll give that a shot.... thank you so much!!


"Salad" <oil@vinegar.co m> wrote in message
news:ghegc.1483 9$k05.1963@news read2.news.pas. earthlink.net.. .[color=blue]
>
> 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
>[/color]