MS Access: Junction/Linker Table, Lookup Property and Dependent Combo Boxes.
Hi,
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.
Fail-Report.mdb
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.
Fail-Report.mdb
ASKER
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.
Hi, I've been away for a while. Do you still need help with this?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
Another option to avoid listing duplicate reasons would be to set the RowSource to:
SELECT DISTINCT Reason FROM tblBond_Fault_Group_Reason