Could use some help with this one. I have an application for our Rock Quarry that creates and print tickets of loads going out. If they are not on account, we have the ticket pull in the board price of the type of rock and then multiply times the tons on the load.
If they are on account, we leave the price field blank on the ticket and show zero dollar amount. This is due to some customers getting special pricing based on volume (on some types, not necessarily on others), etc. So right now, our office manager can pull an Excel export from the application for the previous days load tickets, but all "Account" customers will show a charge of zero. She manually goes in and inserts the special price into the spreadsheet 1 ticket at a time (sometimes 80 or 90 tickets a day).
I am looking into maintaining a table of all our account customers as a record, with fields for all types of rock we sell as fields in the record (Currently about 30 different types). If a customer is getting a special price different than the board price, she could go in and enter the special price for that item or items. If there is no special price, the field would be left blank. (See example)
My question is, how to update the Sales table "UnitPrice" field that shows $0.00, with a special price from the "SpecialPrice" table. Example, if the customer was "Bob Carter Construction", and they purchased a rock type of "1" Clean", and in the "SpecialPrice" table, Bob Carter Construction in the 1" Clean field shows a special price of $12.00, I want to update that record to $12.00. But if Bob Carter Construction also purchases 2" Clean rock, and they have a special price of $11.00, then update that sales ticket record to $11.00.