473,898 Members | 2,838 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Unique ID system -- multiple data sources

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
2 1986
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.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

Nov 12 '05 #2
wow, I'll give that a shot.... thank you so much!!
"Salad" <oi*@vinegar.co m> wrote in message
news:gh******** **********@news read2.news.pas. earthlink.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2403
by: Tony Clarke | last post by:
Hi, I'm trying to write a system thats used for about 50 clients that uses html forms and php to log details. The problem is that when a client loads the form page it's given a value which is the last record in a table +1 (i.e. so its the next record). The problem with that is that a client could sit on that page for 20 mins (or whatever length of time) and a different client could use that record number and there what be an error...
2
2817
by: news | last post by:
We're being asked to get Quickbooks Enterprise edition for our business. We use a completely hand-made online store (PHP and mySQL) and used to simply create CSV sheets that were imported as invoices into regular Quickbooks. But I guess that's neither 100% accurate and may not even be doable in newer Quickbooks. We want to be able to integrate Quickbooks into the system without replacing our system. Has anyone done this?
1
1875
by: Dinesh | last post by:
Hi, my existing web application(vb,asp) takes data from diff sources and populates a sql server which is in turn used as the data source for the application. this population of data happens by a nightly process(once in a day) . now the requirement is that we need the real data all of the times . now if i use merging of Data Sets into single
0
1525
by: Shailesh Ratadia | last post by:
The answer to this will largely depend on your source(s). Are these all databases? One approach would be to build a webservice layer which has specific methods to get data from each source and then you can simply make webserive calls from your application. Not sure if this helps but Good luck :-) -Shailesh Ratadia
3
1605
by: Bob | last post by:
Why, in the process of creating a unique index, does SQL Server allow me to select the "Ignore duplicate keys" option? Wouldn't I just create a non-unique index if I wanted to ignore duplicate keys? I came across this fact while preparing for the SQL Server design exam.
4
15659
by: Louis Frolio | last post by:
Greetings All, I have read many upon many articles here regarding GUID data types and uniqueness. There have been many opinions regarding the effectiveness of GUID's and when they should/should not be used. However, every article strongly implies, if it does not state it outright, that GUID's are always unique. My question is this, what happens if you have a database that uses GUID's and the NIC is changed out on the box? From what I...
9
2441
by: Rolf Kemper | last post by:
Dear Experts, I got stuck with the following problem and need your help. What I wnat to do is to get a set of distinct nodes. Before the distinct I have selected the multiple occourences already sucsessfully. However , the rest does not work as expected. Hope someone can help on that. Rolf
3
3864
by: Steve | last post by:
I have a people table of about 25 fields. The table is initially created each year from 5 different sources. The records from each source are appended to the people table. Any person may be in one or multiple sources. For those in multiple sources, the data for a person may not be complete in any source. For example, a person is in three sources; source#2 has the SSN and source#3 has the membership#. For those people who are in multiple...
3
358
by: Jason | last post by:
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
11
7817
by: garyhoran | last post by:
Hi Guys, I have a collection that contains various attributes (stuff like strings, DateTime and Timespan) . I would like to access the collection in various orders at different points in the application ie sometimes I want to cycle through the values in the collection in DateTime order while at other times in TimeSpan order. Ideally I would like multiple keys - such as Timespan within DateTime order but maybe that is asking too much.
0
9841
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10484
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8036
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5882
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6077
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4706
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3306
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.