Hi everyone,
I am in the middle of creating a new fee database that will track our customer's fee schedule and ultimate use it to export and import the data into our fee system.
First of all, sales people usually needs to create a fee schedule for each customer, the fee schedule consist of company level fees, and account level fees. Ideally the Sales people will maintain the fee in the database, and then once operations group complete the setup they will go in and activate the fee. What I am trying to do is streamline the process for Operations group so that they don't have to re-key the fees into the fee system.
I started creating multiple tables but stuck at how I can "auto-create" all standard fee records when a new company is created. Another word, I want the database to create 150 fee records from the "standard fee table" every time a new customer or account number is created in the customer or account tables. I have tried doing the Append query, but unable to add the customer id or account number during the "run query" process. It will append the record to the table, but without the Customer ID or Account Number, which means I will have to run another "update query" to update. I am not sure if this is the best way to achieve this task. I feel like if I have to do 2 separate queries, there will be room for mistake especially if multiple user is in the database and access won't know what account number or customer id to update.
I have also tried creating a "listbox" where users will select all record from the standard fee table, and then select an customer id from the combobox and then click on "Create". it works, but not sure if it is the most user friendly way to do it.
Can someone advise what will be the best approach to handle the "auto-creation" of records automatically?
Thank you in advance!!