P
PZStraube
Hello,
I am trying to get a query to tell me the top 5 items from 3 different
vendors within each of 20 different categories using a scoring system
computed within an Access 2003 query.
From what my novice eyes have told me from reading numerous help files
is that the way Access allows you to pick the top X # of items is based
on 1 field, not 2 or more (within 1 query). The only other thing I can
think of is to do separate "top 5" queries on the 20 different
categories then merge them into a single query for the final combined
results but that makes eveything quite manual in that I have to
manually enter the category in each query instead of Access just
figuring that out for me. And, that still doesn't give me the
breakdown of each vendor's top 5, only the top 5 in that category -
which leads me to think I need 60 queries (3 vendors x 20 categories) -
all with a lot of manual intervention unless I were to suddenly develop
advanced Access programming knowledge. Since 60 queries sounds
ridiculous, I have to assume that there must be an easier way to do
this.
In the Report example shown below, please note that the 3 vendors may
have the same item show up on the Top 5 list. However, overlap is fine
between the top 5 in each category.
When I get eventually the query into a REPORT, I would set it up like
this:
Vendor A Vendor B Vendor C
Category A:
Item 1: X X
Item 2: X X
Item 3: X
Item 4: X X
Item 5: X X
Item 6: X X
Item 7: X X
Item 8: X X
Ave Score: Z1% Z2% Z3%
Category B:
Item 1: X X
Item 2: X
Item 3: X
Item 4: X
Item 5: X X
Item 6: X
Item 7: X
Item 8: X X
Item 9: X X
Item 10: X X
Ave Score: Z1% Z2% Z3%
The X's would be replaced by the final score Access computes for them.
Many thanks to anyone who can point me in the right direction.
I am trying to get a query to tell me the top 5 items from 3 different
vendors within each of 20 different categories using a scoring system
computed within an Access 2003 query.
From what my novice eyes have told me from reading numerous help files
is that the way Access allows you to pick the top X # of items is based
on 1 field, not 2 or more (within 1 query). The only other thing I can
think of is to do separate "top 5" queries on the 20 different
categories then merge them into a single query for the final combined
results but that makes eveything quite manual in that I have to
manually enter the category in each query instead of Access just
figuring that out for me. And, that still doesn't give me the
breakdown of each vendor's top 5, only the top 5 in that category -
which leads me to think I need 60 queries (3 vendors x 20 categories) -
all with a lot of manual intervention unless I were to suddenly develop
advanced Access programming knowledge. Since 60 queries sounds
ridiculous, I have to assume that there must be an easier way to do
this.
In the Report example shown below, please note that the 3 vendors may
have the same item show up on the Top 5 list. However, overlap is fine
between the top 5 in each category.
When I get eventually the query into a REPORT, I would set it up like
this:
Vendor A Vendor B Vendor C
Category A:
Item 1: X X
Item 2: X X
Item 3: X
Item 4: X X
Item 5: X X
Item 6: X X
Item 7: X X
Item 8: X X
Ave Score: Z1% Z2% Z3%
Category B:
Item 1: X X
Item 2: X
Item 3: X
Item 4: X
Item 5: X X
Item 6: X
Item 7: X
Item 8: X X
Item 9: X X
Item 10: X X
Ave Score: Z1% Z2% Z3%
The X's would be replaced by the final score Access computes for them.
Many thanks to anyone who can point me in the right direction.