Certainly, this is a classic Many-to-Many relationship:
Each Client can use one or more Crates
Each Create can be used by one or more Clients.

To model this, you must create a Linking table composed of the primary keys
of each of the other tables. It would be better and easier if you had a
single primary key for the ClientInfo table that was an autonumber field.
Given that, your table would look like this:

ClientCrate
========
ClientID (fk)(cpk)
CrateNumber (fk)(cpk)

Notice that you will make a compound primary key (cpk) of BOTH fields in
your linking table. You would then create a relationship between ClientInfo
and ClientCrate on ClientID and another between Crate and ClientCrate on
CrateNumber.

If you MUST have a compound PK in ClientInfo (this would take a lot of
convincing on my part), you would need to add ClientName to ClientCrate and
add that field to the relationship as well.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



"Jodie" wrote in message
...
Currently I'm working on a data base that tracks inventory for clients.

I am using 4 tables
- Client Info (primary key Client Name and Client ID)
- Inventory (primary Key Crate number)
- History (Primary Key HID auto number)
- Crate Contents (Primary Key CID auto number)

Each client has several crates and they can't be duplicated, but crate
numbers can be duplicated if used by a different clients.

Is there a way to do this?
--
Thanks,
Jodie