Unanswered: Good design for multiple formulas of one set of data?
I am moving from what was an Excel sheet to a hopefully more elegant Access application. One key part of the application is that there is a list of multiple contracts and each one has a unique formula that incorporates different fields from 2 separate tables.
I would like to end up with a list of contract #s (as column 1) then another field with the formula's calculated result (column 2), which in Excel would obviously be very easy. However, there will be many contracts that will be added and it needs to be easily updated by the eventual user. I'm not sure of the best way to go about doing this in Access.
1. Create a query based on contract # and put all the information in Expression builder which would result in an unwieldy hard to update (for others) solution
2. Create individual queries for each contract then another query that has a column for each contract with a DLOOKUP referring to each individual query's result