MS Access: Junction/Linker Table, Lookup Property and Dependent Combo Boxes.


I'm new to Access and am the process of migrating my group's flat-file Excel worksheet to Access. I'm having an issue which I hope someone can clarify. Its in regards to table design.

This report is intended to keep track of our failed bond trades:

1) There are 4 trading books.
2) There are 4 regions (EuroClear, Mexico, Argentina, Peru)
3) 5 currencies. (USD,MXN,ARS,PEN, and FOP).
Each region can settle either its home currency or FOP (free of payment) except Euroclear. Euroclear can not only settle "USD" and "FOP", it can also settle "ARS" and "MXN" as  well.

The issue I'm having is whether I should create a junction table between Fault_Party and Fail_Reason as well as aother junction table between Book, Currency, Region.

For Fault_Party, when a trade fails its either our fault or the client's. The attributes for both are the same (7) except in the case where its my group's fault, there are 6 additional reasons on top of the 7 shared.

For the Junction table Book_Currency_Region
Each book can only settle in its Home country or Euroclear with its respective currencies

For both junction tables, I listed all the possibilities allowed. My question is, is this the correct method? Should I use the Lookup Property and combo box to give me the proper reason code? Or should I research into Dependent Combo Box's to create the proper reason code on a form instead (given who's at fault and what region the trade is settling in?)

I tried to play around with the lookup property but find it gives duplicate reasons due to bound column =2 which shows the "Short Funds" of each Fault_Group which will definitely confuse the end user.

I apologize if the wording of my problem isn't clear enough. I'm still adjusting to all the terminologies in Access. I included a copy of what I have done so far. If anyone can help me grasp the concept behind this it would be much appreciated. Thank you.

Who is Participating?
calpurniaConnect With a Mentor Commented:
In the example you uploaded, you're using tblBond_Reason as the RowSource for Fail Reason, with Bound Column set to 2. I suspect this was an oversight when you were trying out different options, but as tblBond_Reason only has a single column, bound column would need to be 1.

Another option to avoid listing duplicate reasons would be to set the RowSource to:
SELECT DISTINCT Reason FROM tblBond_Fault_Group_Reason;
Shino_skayAuthor Commented:
hey calpurnia, i fixed the bound column 2 to 1 from the tblBond_Reason RowSource for Fail Reasion in tblBond_Fail_Trades. This Select Distinct is new to me. I have to research into how to use it. What do you recommend to keep the choices limited in regards to if I choose a region, there should be only a certain amount of books related to it and if I choose "Merrill Lynch" as the fault party, I would have 13 reasons than if I were to choose "Client", which should only show 7 reasons? I'm guessing the distinct will just show all 13 regardless? Thanks.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hi, I've been away for a while. Do you still need help with this?
Shino_skayAuthor Commented:
hey calpurnia, I am working on it still.  A little over my head. I got the combo box to work showing 1 column. I need to learn how to use a dependent combo box. Thanks for checking up on me.
Shino_skayAuthor Commented:
i sort of saw something across a website. Thanks. I'm just going to award you the points for being so helpful. Unfortunately I'm only able to learn new things during downtime and I've been task to build some excel application to reconcile various workbooks. I'll reply back when I get a chance. Appreciate it and happy new year.
Shino_skayAuthor Commented:
Always a quick and helpful response. The link provided will help me design an intelligent form for the end user (one day). Thanks expert exchange.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.