Hi everyone,
Background:
I am working on a project and I'm stuck. After a series of user entries and queries, I generate a table that contains, among other things, zip codes for customers. I have another table which has fields for Contractor and the zip codes they are assigned to. Multiple entries for each contractor if they have multiple zip code jurisdictions.
I need to attach these contractors to my first table, creating 3 new fields PrimaryCont, Secondary, Tertiary. Usually I would use a crosstab query to get this done. However, some contractors are given preference over others and the total list can only have a certain percentage be assigned to a certain contractor.
My requirements for assigning a contractor:
1. certain contractors are given priority over other contractors
2. only a certain % of the total may be assigned to a particular contractor.
My issues:
I think I can come up with a way to decide who would go first but for the life of me cant figure out how to incorporate that into a crosstab query to get the results as I want them
Thanks a bunch everyone. Let me know if further clarification is needed.