Many-to-One relationship/SUM function question

[JackDanIce]

Hi,

I'm a very basic user of Access and databases in general so this question may be naive; unfortunately I tried to find an answer online but I couldn't - I suspect I wasn't using the correct search term words.

I have a database (built by a third party) with several forms, that a user inputs data into to create a "Quote" (end purpose, salesman to generate a quote to a customer to lease a car). The quote ID number acts as the primary key/foreign key across all the various tables to ensure a record of data per quote can be maintained. At the top of each form, the quote ID number is displayed. The quote ID numbers are distinct and unique per "quote" the sales person creates.

On one form, the user has an option to enter Dealer Fit options to the "quote" these are specific options to the car they ultimately lease. The entry of this data is like a very mini spreadsheet, with one column for each item and another column for the cost of the item. Hence all the items together have a many-to-one map to the quote ID.

There is also a button which calculates the sum of the dealer fit items after they have been entered by a user. A separate save button saves the dealer fit items to a table so that they can be retrieved if the quote needs to be edited (as all other data entered by the salesperson).

The developer who built the database for me, claims I must include a third column for the quote ID and then manually populate it with the quote ID (at the top of the form) so that the items map correctly to the quote ID being entered/modified and a sum value of the Dealer Fit options is displayed to the user. My argument is there shouldn't be a need for the user to manually enter the quote ID, when the button to calculate the sum of the entered Dealer Fit options is clicked. I believe the items could be saved to a temp table, the quote_ID is then appended to this and then the sum of the Dealer Fit options is calculated with the value returned to the form to display this to the salesperson.

Alternatively, I do not understand why a quote ID is required at all, if the instance of the form at that very moment only maps to a single and unique quote ID.

Unfortunately, this developer is adamant their way is correct, that the user HAS to enter the item, price and quote ID and there are no alternatives to implement my request of only having two columns to input the Dealer Fit options which are just the item and price.


Apologies for the lengthy explanation, but can anyone confirm if I am correct (and perhaps suggest how to implement) or explain if not?

Thank you in advance,
Jack

 

[ranman256]

You are right..if the form has the QUOTE ID, and the form is a single record, then you can use the sub-form to calc totals (based on ID)
NOW..if the sub-form is NOT linked in the MASTER/CHILD fields, this wont happen. But if QUOTE ID is the master and child link property, no need to enter it.
Not knowing the tables / forms is a hiderance so I cant tell.

But when you have a master record, then all sub-tables linked will fill in the ID for you. (if fields are linked)

 

[JackDanIce]

Thanks ranman, that's what I thought but wanted to see if others agreed or not. Will wait to see if any further replies.