Best way to set up tables

[gavfrancis]

Hi, I'm starting to use Access to organise the reports that we receive
from our resellers and I'm not too sure how to sort out the tables. My
aim is to import data from the excel sheet that we receive into Access
(or maybe link to it?). I'll receive a new report from about 20
different companies every month.

I've seen that using relative tables could be useful but I'm not too
sure what the best way is to format them. Do I use 1 table per company
and then update each table every month or have different tables another
way such as 1 that lists the agent and their country, another that
lists what was sold, to who and the product code and finally what the
gross and nett prices are.

Generally I'll be running queries to see which agent has sold what but
sometimes I'll need to look at lots of agents and if they've sold to
the same end user.

Any ideas?

Thanks, Gavin

 

[Arvin Meyer [MVP]]

The way I'd do it is to link each Excel file to your database temporarily,
until you can extract the data to a single table. Use a CompanyID field in
the new table to add the company identification. The query to do the append
would look something like this:

INSERT INTO tblNewTable ( CompanyID )
SELECT ExcelLink.*, 87 AS Expr1
FROM ExcelLink;

87 being the CompanyID of the company that sent that particular spreadsheet.
I'd also add a date/time field to the new table so that you can
differentiate when the new records were added.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access