Question...

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

Hi,

I have around 10 spreadsheets that contain information on
Sales People covering different parts of the UK.

Each spreadsheet is named after the Sales person, e.g.
BillJones.xls. Each spreadsheet has the following columns:

ID: Autonumber e.g. 1
District: Text e.g. NW2
SalesPerson: Bill Jones

What I want to do is the following:

1. Import the spreadsheets into Access in one huge table
(amalgamated) with the filename in a fieldname so I know
where it came from.

2. Pull off a list of duplicates i.e. 2 or more Sales
people covering the same district

3. Finding out who does not cover certain districts when
joined with a PAF list of districts.

Please can someone give me some pointers in Access 2000.

Skc
 
Hi,

I have around 10 spreadsheets that contain information on
Sales People covering different parts of the UK.

Each spreadsheet is named after the Sales person, e.g.
BillJones.xls. Each spreadsheet has the following columns:

ID: Autonumber e.g. 1
District: Text e.g. NW2
SalesPerson: Bill Jones
Click to expand...

I take it that different sheets will have duplicate ID's? Does every
row of BillJones.XLS have "Bill Jones" in the SalesPerson column?
What I want to do is the following:

1. Import the spreadsheets into Access in one huge table
(amalgamated) with the filename in a fieldname so I know
where it came from.
Click to expand...

Create your Table with fields ID (Autonumber), XLS_ID, District,
SalesPerson, Filename. Use File... Get External Data... Link to link
to each spreadsheet in turn; create an Append query based on the
linked table. In a vacant Field cell type

Filename: "BillJones.XLS"

or whatever the filename is; append this to the master table.
2. Pull off a list of duplicates i.e. 2 or more Sales
people covering the same district
Click to expand...

Use the Find Duplicates query from the query wizard.
3. Finding out who does not cover certain districts when
joined with a PAF list of districts.
Click to expand...

If the "PAF List" is an Access Table, use the Unmatched Query Wizard.
 
Back
Top