G
GarethG
I have a pivot table (I seem to be saying that a lot in this place) 
I have a rather large database that cannot be handled by excel in whole
(300,000 records +10,000avg each month)
I use the pivot tables to accomplish most of the jobs I require.
Within the Database, however I have several tables, one of which
contains a list of all customer account codes and with them a sales
person reference
i.e.
ABC123 : H
REF234 : P
There are around 500+ records in this list.
I use MS Query to cross reference this list with the actual sales table
within my DB to group sales by Sales Person.(using the account code to
cross reference-Primaray Key)
The problem is this:
At the end of the month the database is updated with the latest sales.
During that month up to 200 new customer accounts may have been
generated.
The pivot table will ignore these new accounts because they do not have
a sales person associated to them.
I believe that I could use postal data to create the closest match.
That is:
If no sales person code is present for this account take the post code
and match it to the closest post code from an account that DOES have a
sales person associated.
I was thinking of setting up a pre-process system that would update my
sales rep list before the pivot table updates for the new month.
Is there a way of getting Excel to look at records in the sales table
and compare records to the sales rep list and have it add sales rep
codes to a new account code based on a nearest match for post code.
(I shall also post this in an Access forum incase it is an Access
orientated solution rather than an Excel solution)
Thanks
GarethG
I have a rather large database that cannot be handled by excel in whole
(300,000 records +10,000avg each month)
I use the pivot tables to accomplish most of the jobs I require.
Within the Database, however I have several tables, one of which
contains a list of all customer account codes and with them a sales
person reference
i.e.
ABC123 : H
REF234 : P
There are around 500+ records in this list.
I use MS Query to cross reference this list with the actual sales table
within my DB to group sales by Sales Person.(using the account code to
cross reference-Primaray Key)
The problem is this:
At the end of the month the database is updated with the latest sales.
During that month up to 200 new customer accounts may have been
generated.
The pivot table will ignore these new accounts because they do not have
a sales person associated to them.
I believe that I could use postal data to create the closest match.
That is:
If no sales person code is present for this account take the post code
and match it to the closest post code from an account that DOES have a
sales person associated.
I was thinking of setting up a pre-process system that would update my
sales rep list before the pivot table updates for the new month.
Is there a way of getting Excel to look at records in the sales table
and compare records to the sales rep list and have it add sales rep
codes to a new account code based on a nearest match for post code.
(I shall also post this in an Access forum incase it is an Access
orientated solution rather than an Excel solution)
Thanks
GarethG