G
GarethG
I have a pivot table(excel)
I have a rather large Access 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
:
:
ZZZ190 : F
There are around 500+ records in this list.
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(excel) 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 Access 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.
Thanks
GarethG
I have a rather large Access 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
:
:
ZZZ190 : F
There are around 500+ records in this list.
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(excel) 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 Access 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.
Thanks
GarethG