Two fields in a table, link to the same Look-up Table (1 Viewer)

[Never Hide]

Hello everyone,

I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*

When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error"

I think the problem is that the Relationship is defined as One-to-Many,
so my Question is is there a way to define the relationship as Zero-to-Many, or maybe there's another way around this problem?

Any input is greatly appreciated

 

[jdraw]

Can you tell us in plain English WHAT you are trying to do?
Perhaps you could give a simple example to show what you have and what you want.

When you see fields in tables such as, place1, place2,... it usually indicates a design issue -- but no always.

Do you have a situation where you have Many Products and Many Storage locations?
If so, you may have the classic Many to Many set up.

Many Products in many locations, and many locations with many products.

Products<----->StorageLocations which is resolved by a junction table

Products----->ProductIsStoredAt<-------StorageLocations

This set up tells you which Product is stored at which StorageLocation.

 

[Never Hide]

Hi jdraw,

my situation is this.
One product can be stored in only one place so you'd have a value in "StoragePlaceID1" and null in "StoragePlaceID2".
One product can be stored in only 2 different places so you'd have a value in "StoragePlaceID1" and a value in "StoragePlaceID2".
You can also have a product which would have null for both "StoragePlaceID"
(i.e.you store in your database a product for your "CustomerSupport" which is a service so you don't have a storageplace for that )

I hope this makes a bit sense

 

[jdraw]

?? I don't follow ??

 

[Pat Hartman]

I think we're having trouble picturing how a thing can be in two places at once but that is neither here nor there as far as your problem goes.

When the relationship is optional, you need to mark the FK field as not required in the table definition.

In the query you use to update the location, you don't need to join to the lookup table. Just use a combo on the form to pick from. If you do elect to join to the lookup table because you want to display additional fields, make sure you are selecting the StorageID value from the Product table rather than the StoragePlace table.

 

[Never Hide]

Thanks a lot Pat, that did the trick