Access database needs help!

[chuffy]

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.

[dsmacs]

Is there are more efficient way to do this?

Yes, all reporting can be achieved in Access as long as the various components can be linked, ie have a relationship.

Can I use a Master Customer Table, ...
To speed-up processing the inclusion of a unique account numbering system would be preferable...
Keep one customer table and include another field called parentID. If Company is Parent than ParentID = 0, if Child then ParentID = Parent Account Number.

Yes, the transaction table will need to include a reference to the customer ID.

Regards
Darren

[NTC]

there should be a table just for customers.

you need two columns; one can be autonumber if you want (assuming no autonumber field exists) - - but essentially it is the unique ID per record/customer. Perhaps this field already exists - it typically does exist just as a primary key ID, and that is sufficient (no duplicates)

the second column can be text or number - but it is for the purposes of cross referencing or grouping the customers into their family....so you put a common value for all the records that are part of parent XYZ.

then you will be able to sort & report by both individual customer as well as 'parent' company....

[chuffy]

I have heard that I should use 0 for the value of the Parent company in the Parent ID field.

I now have a Customer ID (Account Number) and a Parent ID which is set to the Parent company (Customer ID) for all the child companies of the Parent.

I have the Company ID linked to my transactions table, and I can query all accounts individually, however, how do I write a query to sum all the transactions for both the Parent and all of its child companies combined?

[NTC]

A query that makes a sum is an 'aggregate query'. This is an embedded feature in Access. Look for the epsilon E symbol when in query design. You should have an Access textbook - and they all cover this topic.

Your first step is to make a regular query that will result in all the correct records. Your second step then is either to change it to an aggregate query or possibly make a new aggregate query using the first query as its source.

hope this helps.