I have taken over a database, where it does not appear to be the best design.
We have many customers who have a Parent Company and many child companies(as many as 10) that we do business with. We have roughly 100 transactions daily, and there are many reports that show the totals based on date, product purchased, etc...
These reports do not combine the Parent and Child totals, so the method has been to put them into excel for further processing.
Is there are more efficient way to do this?
Can I use a Master Customer Table, with a customer ID(no they do not currently have a customer ID, it is all based on the name), and have all the Parent and Child names as fields in the customer record? If I did that, could I then write queries which would pull all transactions for any customer that is a child or parent of the customer ID?
If I did this method, I imagine I would have to add the customer ID to the customer table, and set all the parent and child customers to the same customer ID.
To make a query work, I could possibly add the customer ID field to the transaction table, and have to go back through and add the proper customer ID to each record. This would allow a query against the customer ID and could give me the totals I need for reports?
Am I even close guys? I have not used Access in a long time, and I am not certain of the best method in this case.
Any help would be greatly appreciated.