HELP!!!

  • 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
 
skc said:
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.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why do you want the filename as a value in one of the table's columns
when, you say, the filename is the SalesPerson's name and that the s/s
already holds that info - field SalesPerson?

As far as I know Excel s/s don't have AutoNumbers. What do you mean?

Suggestions:

1. Use the File > Get External Data > Import items on the main menu
to import the s/s data into a new table. Select Excel (*.xls) from
the File Type field on the file open dialog. Then follow the
directions on the import dialog.

1a. If you must have the filename in the table: After importing the
1st s/s open the receiving table in design view & add the column
"FileName." Then run this query to fill the column:

UPDATE TableName
SET FileName = "Harry Potter.xls"
WHERE FileName Is Null

Change table/column names to suit your situation.

After each import, change the s/s name & run the query.

2. Create this query to find multiple sales people per district

SELECT DISTINCT District, SalesPerson
FROM TableName
WHERE District IN (SELECT District
FROM [SELECT District, SalesPerson
FROM TableName
GROUP BY District, SalesPerson]. As A
GROUP BY District
HAVING COUNT(*) > 1)

Change table/column names to suit your situation.

3. Don't know what a PAF list is. Assume it is a list of all your
districts - one record per district? Create this query to find out
who is not covering a district. It will actually show who is not
assigned to a district in the PAF list.

SELECT P.District, D.SalesPerson
FROM PAF As P LEFT JOIN DistrictCoverage As D
ON P.District = D.District
WHERE D.District Is Null
GROUP BY P.District, D.SalesPerson

Change the table/column names to suit your situation.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFIdcIechKqOuFEgEQJ1JwCg+At/aFFyFqBxQZupmcGWUp18PkEAoJzt
4o6dZ/Yn1Pq+dKb5WYpzG0G9
=JJ06
-----END PGP SIGNATURE-----
 
Back
Top