G
Guest
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.