How can I make Access think there is a primary key

Posted by user3692757 on Stack Overflow See other posts from Stack Overflow or by user3692757
Published on 2014-05-30T21:00:06Z Indexed on 2014/05/30 21:26 UTC
Read the original article Hit count: 222

Filed under:

primary-key

I have a table and I'm trying to join it with another table, but it doesn't have a distinctive primary key. The two tables do share similarities, “Acct” and “Location”. If I could concatenate “Acct&Location” it would become a primary key, but Access won’t let me make a primary key from a calculation.

I provided a small sample below.

Each hospital has an “Acct”, but the “Acct” will show up once for each “Location”. How can I make join these in a relationship? I connected the two in a relationships and tried to “Enforce Referential Integrity”, but it indicated “No unique index found for the referenced field of the primary key”.

Also, if I run a “Find UnMatched Query” it doesn’t find anything. I think its because I can’t make it realize that in combination “Acct” and “Location” can be perceived as primary keys when used in conjunction of each other.

Acct 1 2 3 1 2 3 1 2 3|

Location ABI ABI ABI NHO NHO NHO NTX NTX NTX

I tried to load an image to illustrate it better, but I haven't made enough post.

Related posts about primary-key

How to explain to users the advantages of dumb primary key?

as seen on Stack Overflow - Search for 'Stack Overflow'
Primary key attractiveness I have a boss(and also users) that wants primary key to be sophisticated/smart/attractive control number(sort of like Social Security number, or credit card number format) I just padded the primary key(in Views) with zeroes to appease their desire to make the control number… >>> More
Can a Compound key be set as a primary key to another table?

as seen on Stack Overflow - Search for 'Stack Overflow'
Can a compound key be set as a primary key to another table? For instance i have the tables: Books -with Primary Key:Product_ID Client -with Primary key: Client_ID Clients_Books -with Compound Primary Key:Product_Id and Client_ID I want to set this compound Primary key from Clients_Books as… >>> More
Setting the primary key of a object type table in Oracle

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two Oracle questions. How can I set the primary key of a table when the table is made up of an object type? e.g. CREATE TABLE object_names OF object_type I have created a Varray type, CREATE TYPE MULTI_TAG AS VARRAY(10) OF VARCHAR(10); but when I try to do SELECT p.tags… >>> More
use of EntityManagerFactory causing duplicate primary key exceptions

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey guys, my goal is create an EntityManager using properties dependent on which database is in use. I've seen something like this done in all my Google searches(I made the code more basic for the purpose of this question): @PersistenceUnit private EntityManagerFactory emf; private EntityManager… >>> More
Mapping a child collection without indexing based on database primary key or using bag

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a existing parent-child relationship I am trying to map in Fluent Nhibernate: [RatingCollection] -- [Rating] Rating Collection has: ID (database generated ID) Code Name Rating has: ID (database generated id) Rating Collection ID Code Name I have been trying to figure out which permutation… >>> More

Developer IT