Forum Discussion
MS Access Many Junction Tables - Design Advice
You are right. A properly normalized table design doesn't lend itself to that multicolumn interface layout. You could use a crosstab query to achieve the layout, but then it would not be editable. Another option is to go to an unbound form based on that layout, and use VBA to write the values back into the various tables.
However, the most familiar way to accomplish it is one that is illustrated in this demo on my website.
Prices will change every 3 to 6 months normally.
If I stick to the previous relationship structure that I shared, is it possible to organize the form having the supplier selection in the header and then all the other details as a continuous form?
Header carries all information from tbRequest and then user fill in ItemDescription (combobox) and fill prices and currency for that item (will probably add a checkbox like "use same currency for all items under the same supplier")
Something like:
I couldn't think how to transform my tbRequestDetails in this structure to the photo above, should I use a Query to organize?
You are right. A properly normalized table design doesn't lend itself to that multicolumn interface layout. You could use a crosstab query to achieve the layout, but then it would not be editable. Another option is to go to an unbound form based on that layout, and use VBA to write the values back into the various tables.
However, the most familiar way to accomplish it is one that is illustrated in this demo on my website.
Gorge, I've seen your DB, your videos and I still couldn't understand how to do it.
In that example of artists and songs it is a junction table with 2 tables involved and 2 keys (ArtistID and SongID). In this case I'm clear with it and the subform.
In my case I will have 3 tables involved. Do you have an example with 3 tables?
You'd manage that with TWO combo boxes on the subform instead of just one. The two combo boxes are bound to the second and third tables involved in the junction table.