Efficient Process?

[compooper]

Here's the idea:



There are 5 companies, A,B,C,D,E.
Each Company provides various services, and sends invoices either monthly or biweekly.

The services that were ordered are on a seperate system & will be linked later.

For now, the database system should do the following:
- create invoice summaries (for each company and for the overall business)
- find possible errors that might be in a particular invoice (using predefined thresholds)
- find data for a particular client (ie. services ordered for a client in a specific time period)

My question to you is if the following is an efficient process:
- user imports invoice by an importing form I have developed, & the user indicates which company the invoice is from
- invoice merges to its corresponding company table, for example all invoices for Company A in one table
- important data is extracted from the company tables and merged together, such as client name, service, price & company name
- all queries, forms and reports use data from the table above (not the separate company tables)

Is this an efficient process?
There are approx. 20 invoices that will be imported every month.
This system has to work in the long run.

[Bob Fitz]

I would have the following tables:

tblCompany
CompID PK
CompName
and any other fields needed for info about each company

tblInvoices
InvID PK
CompID FK
InvRef
InvDate
InvAmount
and any other fields needed for info about each invoice

tblInvDet
InvDetID PK
InvID FK
and any other fields needed for info about the details of each invoice

You might also consider a table for info on the "services" that you mentioned.