Joining 3 Table with Referential Integrity

[Lance]

I am tring to design a small Point Of Sale database for a small bar. I have 3
tables called 01Genre, 02Categories and 03Items.

Table 01Genre has a Primary Key (GenreID, auto#) and GenreName, for instance
"Drinks, Beer, Food, Retail".

Table 02Categories has the 01Genre.GenreID, CategoryID (auto#) and
CategoryName, for instance "Cocktails, Martinis, Well" for Drinks, "OnTap and
Bottled" for Beer, etc.

Table 03Items has the 02Categories.CategoryID, ItemID (auto#) and ItemName,
for instance "Screwdriver, Mojito, Cuba Libre" for Cocktails, "Cosmo, Apple,
Dry" for Martinis, "Corona, MDG, Coors" for OnTap, etc.

In the relationships window I joined the 01Genre.GenreID to the
02Categories.CategoryID and enforced referential integrity. This works fine.

I then tried to do the same thing by joining the 02Categories.CategoryID to
the 03Items.CategoryID, but when I try to enforce referential integrity, it
gives me an error "no unique index found for the referenced field of the
primary table".

I can join the 02Categories.CategoryID and 03Items.CategoryID only with a
one-to-one relationship.

What am I missing?

All help is appreciated....

Lance

 

[Douglas J. Steele]

What Primary Keys do you have set?

 

[Lanceland]

For 01Genre - the GenreID

For 02Categories - the GenreID and CategoryID

For 03Items - the CategoryID and ItemID

 

[Douglas J. Steele]

The problem is that your Primary Key for 02Categories doesn't correspond to
what you're using as the Foreign Key in 03Items.

Either change the Primary Key of 02Categories to strictly CategoryID, or
else have a Unique Index on CategoryID. If you haven't changed the Access
defaults, you'll already have an index created to CategoryID, because Access
by default creates indexes for all fields whose names end in ID. Simply set
the Unique Index property for that index to True, and you should be okay.

 

[Lanceland]

Doug,

You are correct! It worked (I changed the primary key to strickly
CategoryID).

Thanks for your help. If you happen to be in Santo Domino, DR anytime soon,
let me know and the drinks are on me....