Importing existing tables and trying to create referential integrity

[smatchymo]

I'm brand new to access and I've taken a part 1 and part 2 course for work over the past several days. For practice, I wanted to play with some personal budget data I had. I have two tables, one for my categories and one for my transactions. The categories table lists the various locations I've spent money and the fields on the corresponding row list the category and subcategory for purchases at those locations. There are no duplicates of the purchase locations, so that is a primary key. My transaction table has, date of transaction, description, amount, account type, account institution, and purchase location.

I have a relationship set between the purchase location columns of the two tables, but I'm unable to enforce referential integrity. I get an error stating access can't create the relationship and enforce referential integrity. Again, in the categories table, purchase locations have no duplicates. Those are being indexed with no duplicates. I have exported both tables to excel and I've done a lookup to ensure that every purchase location referenced in the transactions table is valid and pulls up a value from the categories table. What else should I be checking for exactly? Both purchase location fields are set to text with a 255 character limit. Any help would be greatly appreciated. Thanks in advance!

Edit: I had run the "find unmatched query wizard" incorrectly. There were some entries in the transaction table that had a space at the beginning...

[CJ_London]

always better to use autonumber primary keys with related long as foreign key. The problem with text (as you have found) is spelling.

from your description, you should have more that two tables - you also need two for your accounts (institution as master, type as child) and only need to store the key to the type in your transaction table since you can look up the institution when required.

[ssanfu]

I'm with Ajax. Every table I create has an Autonumber field as the PK field.

AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


More reading:
Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques

[smatchymo]

always better to use autonumber primary keys with related long as foreign key. The problem with text (as you have found) is spelling.

from your description, you should have more that two tables - you also need two for your accounts (institution as master, type as child) and only need to store the key to the type in your transaction table since you can look up the institution when required.

But by doing that, then wouldn't I be left with corrupted data? I would have had two similar entries that I wouldn't have known about. Now I know my locations are unique which was my primary goal. As for the institution/account relationship, the same institution can have multiple accounts, ie checking, savings, and credit.