Access Intermediate Table

[s1monUK]

Hi,
I have 2 tables - one contains flowers, another contains bouquets. I cannot make a one to many straight away so i have a composite key in a third table called BouquetDetails - this will tell the user what bouquet has what flowers in.

Ive got my forms working to add bouquets and flowers but i cannot get a form to join the two together using my third table - it keeps saying "A related record in blah blah is needed".

Ive set the third table to have a composite key and still no luck no matter what i do.

I have a diagram of my relationships or I can email my database if you need more info - can anyone point me in the right direction?

many thanks,
Simon.

 

[barbola]

I think u need an ID field in your detail table just to identify each row and eliminate duplicates as follows:

tblFlowers
fldFlower
data:
Rose
Carnation
Daisy

tblBouquets
fldBouquet
data:
RosesCarn
RosesDais
CarnDais


tblBouquetDetails
fldID fldBouquet fldFlower
1 RoseCarn Rose
2 RoseCarn Carnation
3 RoseDais Rose
4 RoseDais Daisy
5 CarnDais Carnation
6 CarnDais Daisy



Thanks!
Barb E.

 

[Remou]

Hi
You will get this error if you try to add a record to your intermediate table that mentions either a flower or a bouquet that you have not put in the flower or bouquet table.

 

[s1monUK]

Hi - its strange - thats exactly what ive done but when i try to enter - i get the "a related record" error.

here is an image of my diagram - its the 3 tables from the left im working on.

http://www.pekinbantams.com/image2.jpg

can you see an obviouse problem?

Simon

 

[Remou]

Hi check your last 2 relationships you have related orders to bouquets and visa versa.

 

[barbola]

Your join between tblBouquetContents and tblBouquet should be between both BouquetID fields. You are joinining the FlowerID field to the BouquetID field and getting an error.



Thanks!
Barb E.

 

[s1monUK]

thanks barb, but if you do that you get a one to one - oh man! can it be done?

 

[barbola]

If you doubleclick the relationship line, you can select the one-to-many option which will show every record and tblBouquetContents and only those in tblBouquet that match.



Thanks!
Barb E.

 

[s1monUK]

but any other apart frmo what i have now changes to a one to one.

 

[barbola]

hmmm. check the properties for the fields in tblBouquetContent to make sure the primary keys are set as both FlowerID and BouquetID (i think u said you have a composite) and make sure that duplicates are allowed.

Thanks!
Barb E.

 

[Remou]

Hi
Have you noticed that you have related OrderID in tblOrderLine to BouquetID in tblBouquet? Also you have related BouquetID in tblOrderLine to OrderID in tblOrders. This seems an unlikely set-up.