Unanswered: using foreign key to save from form to table
I have a table SalesAgent, table Sales, table Commissions. AgentID links all three tables. I also have table Customers. I have a Customers form, with subform Sales. This subform contains CustomerID and AgentID. There is a button on the subform "Add to My Commissions". I would like certain records from the subform to save to the Commissions table: AgentID, CustomerID, SaleDate, TotalSales. I would also like a field from SalesAgent to save as well.
Another question might be "is this advisable?". Is there any reason you want to put this in another table? Why not get this information in a query? If you start along this path you need to then account for modifications\ deletions (order get cancelled, mistakes are made when inputting etc). These are called update anomalies, and occur when you start duplicating data around the place.
Unless there is a really compelling case, I would not use a commission table of this nature.
The reason I want this information to save automatically in a separate table is so nothing gets missed. When a SalesAgent is very busy, they may not always remember to update their Commissions in the table. The query will extract each Agent's Sales when they are prompted for their ID. If each SalesID is automatically saved with the AgentID, then when the Agent runs the query, all of his sales will show, allowing him to calculate his commissions.
I am very new to Access and databases in general, I am trying to design a program that is very large and complex. I apologize if I'm not communicating my problems effectively. I know what I want the program to do, I just need to know how to do it. And sometimes what I want to do may not be what should be done, but it still needs to be done. Am I better to not have a Commissions table at all, and just have a dynamic query for each AgentID? And further, one that a manager can see the commissions for all agents under him?
Thank you for questioning my design. You were right, there really isn't any need for a Commissions table. There were only 2 fields that weren't in any other tables, and they were easily added to the Sales table. So that problem has been resolved. Now if only the rest were so easy...