Question on Lookup

[Sam]

So I have 3 tables related through their PKs.
I have input data in table 02Enquiries through a Form, where the user has option to make multiple Choices and these are stored in the table.
After, s/he has made a final choice from the choices, the 3rd table needs to be filled in. As much as I have tried using the Lookup Wizard, I'm not able to input one of the Choices (02Enquiries) into Choice (Table1). It always puts in all the Choices. Is there a way to select one of the Choices.

[Sam]

Update - OK, I could run a Query and flattened the Choices.Values Thanks to MS Support site.

[mike60smart]

Hi

Your relationships are wrong.

01Units table has a PK of UnitID

The related table should have a field named UnitID which would be the FK
You would then set up a 1 to Many relationahip between these 2 tables by linking the PK to the FK.
It is also recommended that you do not use MVF in tables

[Sam]

Is it mandatory to use the same name for the PK/FK. The Choices.Values are the UnitID numbers from 01Units table.

So I should use, Choice1, Choice2, Choice3 as fields for the Choices an intending buyer may make, instead of keeping the choices together. I did consider that.

[ArviLaanemets]

As Mike did write - don't use multi-value fields, they are evil

So I should use, Choice1, Choice2, Choice3 as fields for the Choices an intending buyer may make, instead of keeping the choices together.

So choices must be UnitID's from table 01Units. And you have several such values in single row of 02Enquiries!
Instead remove the field Choices from 02Enquiries at all. In this table, you'll keep the general info about specific inquiry. And you need another table, where you enter info about which units are included, like
EnquiryDetails: EnquiryDetailID, EnquiryID, UnitID, ... (you may have here additional fields about specific unit in this Enquiry containing info you can't have in 01Units table, like inquired quantity, current price, etc.). NB! There may be any number of rows in EnquiryDetails table linked to single row in 02Enquiries table.

[Sam]

That's what I am thinking. If you see my other post
https://www.accessforums.net/showthr...413#post503413
I have broken down the choices that a prospect may make into different entries in Enquiries, linked to the same Prospect.

[mike60smart]

Hi Sam
Can you upload a zipped copy of your database?

[Sam]

NewAttempt.zip

It is still a work-in-progress. I have not made most of the tables. Going step by step. But here's a copy.