Forum Discussion
New to MS Access 2016
MSUser0930 What you describe is actually pretty complex. Financial oriented relational database applications tend to require good understanding of the accounting behind them.
Here are some initial thoughts.
1) Quickbooks? Would you rather invest time and resources into re-inventing the tool, or would you be able to invest in a tool that already does pretty much everything most people need in this area.
2) You need main or primary tables for customers, products sold, or services provided (whichever your organization provides, or both), transactions involving customers and your products or services, details of those transactions, invoices for transactions, invoice details for those invoices, and invoice payments.
3) In addition, you probably need a table that relates customers to their respective fee structure. This would be a table usually called a junction table, in which you record the foreign key for the customer, the foreign key for the product or service, the effective date and the fee as of that date. This allows you to change fees from time to time without wrecking older invoices.
I suspect there will be additional tables, but without detailed survey and analysis of your organization, it might be hard to suss that out.
Make sure you assign proper Primary Keys to all tables, and that you properly enforce Referential Integrity between related tables.
Once your tables are set up, you can turn attention to the interface objects--forms and reports--needed to use them.