We have a database of approximately 500,000 customers.
We have 20 sales reps that cover the entire country. Their territories are defined by a range of zip codes with the following structure:
A particular sales reps records might look like this:
SalesTerritoryId SalesId BeginningZip EndingZip
136 84 90000 92199
137 84 92200 92299
138 84 92300 92599
139 84 92600 92899
140 84 93000 93199
141 84 93200 93499
The sales reps can only see their customers in an Access database form using the following many to many query as the source for the form:
FROM dbo_tblSalesTerritories, dbo_tblCustomer
WHERE (((dbo_tblCustomer.CustomerTypeId)=2 Or (dbo_tblCustomer.CustomerTypeId)=3 Or (dbo_tblCustomer.CustomerTypeId)=4 Or (dbo_tblCustomer.CustomerTypeId)=6) AND ((Left([Zip],5)) Between [dbo_tblSalesTerritories].[BeginningZip] And [dbo_tblSalesTerritories].[EndingZip]) AND ((dbo_tblSalesTerritories.SalesId)=[Forms]![frmMainMenu]![SalesId]));
This query is very slow to execute, particularly if the sales rep has a lot of zip code ranges in their territory. And the actual query includes some sub queries to lookup customer history which slows it down even further.
The front end database is Access 2003. The backend is MS SQL Server 2008.
Finally to my question. Is this the best way of structuring the data? If yes, is there a better method for querying the customer records for each sales rep? I thought of a pass-through query, but don't know how to pass the SalesId to the query.
Any help would be gratefully appreciated?