Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Set up table joins to append unique records

Posted on 2012-08-19
Medium Priority
Last Modified: 2012-08-25
How to set up table joins to append unique records

in reference to:

We want to append data from remote databases to a central office db.
I want to combine a text string with the auto number so that the default auto numbers from different data sources  would not result in a duplicate.  

The text string would have hundreds of duplicates.  
I use a default value for the text string ( [Location] )for each remote database location.  
The auto number would have 1 dup for each table created remotely.  
We want to combine unique records.  
When appending to the main database, in order to prevent duplicates the append query table join would be set up...
That is the point where I thought that I would require a seperate unique ID field
It looks like I just need help setting up the joins for the append query?
Question by:AMixMaster
  • 4
  • 3
LVL 52

Accepted Solution

Gustav Brock earned 1500 total points
ID: 38309949
Why not use a GUID as the autonumber key?
Just adjust the table design to use GUID for the Id and you are set.

You could still add a field to hold the source location Id for informational purpose.


Author Comment

ID: 38310043
I was hoping to keep it simple by using the unique field as a noticeable visual reference when viewing the table.  I think that combining the values in two existing fields with recognizable values  will work well for this purpose as there are only four or five records per day created.  

I tried converting autonumber to replication id but it created a duplicate number when compared to other databases, (so this is just a long autonumber)...
- auto number 1 became:
and 2 became:
in separate databases.

I might be ok with using a GUID. I haven't figured out how to set this up.

I was hoping to create an automated and reliable query that would add only the new records.
We do this every day when downloading new bank transcations to Quicken or Money
LVL 52

Expert Comment

by:Gustav Brock
ID: 38310830
Your observation regarding existing Ids is correct. They are numbered {00000001-0000-0000-0000-000000000000} and forward, perhaps to be able to identify these.

However, the GUID for new records will be quite different - and unique.

If you only have a handful of records each day you may be able to write these to a text file or a small temp mdb.
That's what I did for a project years ago. The records in question were written to a table in a separate mdb with some sensitive data as encrypted strings. The file was small so I didn't even bother zipping it, just attached it to an e-mail and sent away. At the receiving end the table was opened and appended while decrypting the encrypted fields.
It worked with zero issues for years. It was in JET 2 format so no GUID. I used a compound key made from a fixed LocationId and a normal autonumber Id. Today I would for sure have used a GUID.

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 38321489
Access Field Size Property list states that “Replication ID is a 16 byte Globally unique identifier (GUID)".
It seems that it is not Globally Unique when converted from Auto Number because the same value could exist in another table that was also converted.  
I created new tables using RID/GIUD field size and it there were no duplicates (as expected) in two separate tables.
I conclude that if I paste the existing records into a new table with a fresh GIUD as the primary field the records would remain absolutely unique, indefinitely, as new records are added each day, correct?
LVL 52

Expert Comment

by:Gustav Brock
ID: 38321671
Yes, that would be a method. Can't see why it sholdn't work.


Author Closing Comment

ID: 38332850
The key difference between Auto Number with the Long Integer Field size and Auto Number Replication ID is that you can append Replication ID Values from one table to another.  
This solves the problem bcause the GUID identifies the same record in two seperate databases!
This is the why that was not explained by the expert.
The how is absolutly correct.
LVL 52

Expert Comment

by:Gustav Brock
ID: 38332887
You are welcome!


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question