UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Primary Keys, Access 2016    
 
   
sub3g
post Jul 19 2019, 06:01 PM
Post#1



Posts: 2
Joined: 19-July 19



Hi, I've recently inherited a few databases. I'm presently having issues linking two of these databases due to primary keys. One databases is front end and other is entirely backend with no front end access. The issue comes with having two unique primary key values (i.e., two values in the same column for one tbl) need to match one unique primary key in a different tbl. Is this possible without removing a primary key for one of these tables?

The different unique primary keys came about due a location staying the same but had a name overtime. I don't want to change the primary key numbers to match because the time series of the dataset is important. Would any one have any suggestions or need any further information?

Attached File  Book1.pdf ( 27.42K )Number of downloads: 8
Go to the top of the page
 
GroverParkGeorge
post Jul 19 2019, 06:25 PM
Post#2


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

It's not very easy to diagnose a problem in the tables based on a PDF, but it seems like your problem may or may not be that complicated.

It's not possible to have this situation in a Relational Database Application:

" The issue comes with having two unique primary key values (i.e., two values in the same column for one tbl) need to match one unique primary key in a different tbl."

We do NOT create relationships on Primary Keys in two different tables.

One field is the Primary Key for that table, and each value in that field is unique within that table.

One field in the related table is a Foreign Key, not a Primary Key, and each value in that field can appear multiple times because one record in the first table can be related to one or more records in that related table.

Since you've "simplified" by removing any readily identifiable context, let's say an example of what I am talking about is an Invoice and line items on that Invoice. In the Invoice table, there is a field designated as the Primary Key. Each InvoiceID is unique in that table. However, an invoice can cover one or more line items. I.e. you sell four different kinds of tools, each one being a line item. The Primary Key from each of the Invoices can appear as a Foreign Key in the Line Item table as many times as there are Line Items (i.e. four tools).

So, in this case, you may or may not have a real problem, but certainly we're not looking to define a relationship between Primary Keys. Perhaps between a Primary Key and a Foreign Key.

However, given the abstracted information provided, it's not possible to be sure that's your problem here. It may be that there are actually two Invoice tables? Or something like that.

Perhaps real example data and context will help.

Thanks.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
sub3g
post Jul 19 2019, 09:17 PM
Post#3



Posts: 2
Joined: 19-July 19



I'm aware it is tough to diagnose without the true representation of the data. I just can't disclose that information and I apologize.

I've attached additional data that represents more of my issue. Both of these tbls do indeed have primary keys (i.e., one unique numeric identifier). However, each tbl is within its own database (i.e., database A and database B) and I'm trying to make the link between them. Database B is the linking issue with Database A being 100% complete.

In the example you'll see how in Database A Cabelas changes its name to Bass Pro Shop, Wendy to Burger King, Gap to Foot Locker. You'll notice that only the name of the store changed not the location. Some of these locations are populated within Database B, but some are missing (i.e., location was never reported or the location is not needed in Database B).

Essentially, this linkage seems to be the only avenue to merge data in Database A with Database B. Through this method I was able to link ~85% of the data, but my big issue is the duplicate locations with different UniqueIDs for Database A and like I said earlier this information is important for me in relation to the time series of the data.

I appreciate the help and hope this makes a little more sense. I'd be happy to hear anyones suggestions or thoughts.

Attached File  Example.xlsx.zip ( 9.33K )Number of downloads: 2

This post has been edited by sub3g: Jul 19 2019, 09:52 PM
Go to the top of the page
 
cheekybuddha
post Jul 20 2019, 04:47 AM
Post#4


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


Hi,

You need to create a junction table to record the relationships rather than putting [Database A].TblLocation.ID_Unique as a foreign key directly into [Database B].TblLocation

So create a table along the lines of:
TblLocationMapAB
ID - Long Integer, PK, autonumber
A_ID_Unique - Long Integer, indexed
B_ID_Unique - Long Integer, indexed

(You can probably omit the ID field and just create the PK using both A_ID_Unique and B_ID_Unique, but I find the usablility of compound keys a PITA when you need to join on them later - it would be advisable however to create a UNIQUE compound index on those two fields.)

Then, you can populate the table. Since not all [Database B].TblLocation records have data for Lat/Long you may have to do this in two stages:
1. Populate easy matches:
CODE
INSERT INTO TblLocationMapAB (
  A_ID_Unique, B_ID_Unique
)   SELECT
      a.ID_Unique,
      b.ID_Unique
    FROM [Database A].TblLocation a
    INNER JOIN [Database B].TblLocation b
            ON a.Latitude = b.Latitude
           AND a.Longitude = b.Longitude
;

2. Populate matches without Lat/Long
CODE
INSERT INTO TblLocationMapAB (
  A_ID_Unique, B_ID_Unique
)   SELECT
      a.ID_Unique,
      b.ID_Unique
    FROM [Database A].TblLocation a
    INNER JOIN [Database B].TblLocation b
            ON a.[Name] = b.[Name]
           AND a.Country = b.Country
           AND a.Location = b.Location
    WHERE b.Latitude IS NULL
      AND b.Longitude IS NULL
;


From there you can create a query to show the output you show in your spreadsheet for [Database B].TblLocation, or fill in the gaps in each table.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 11:38 PM