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.