Top 5 Query based on 3 fields, not 1

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 Sco 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 Sco 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.

Hi Paul,

I'm not sure I completely understand what your data looks like, and what you
are trying to get, but I'm pretty sure that creating a rank field is likely
the way that you will achieve what you are trying to do. In your case, it
sounds like you would want to create a field that ranks the items within a
category, and I think also for the particular vendor. To do this, you would
generally use DCount() or a correlated subquery (I prefer correlated
subqueries but either will work) to determine the number of items matching
the category and vendor of the current item that have higher scores.

Once you have done this, all that you need to do to see the top 5 for each
unique category and vendor combination is to set the criteria for the rank
field to be =5.

You can find a lot of help on this issue by searching the group for rank and
correlated subquery. I would be happy to help as well, if you can post the
table and field names involved, and maybe a small sample of what the actual
data would look like. Also, confirm whether you want to see the top 5 in
each category for each vendor, or just the top 5 in each category. Finally,
give a little more detail on how to determine the item rank (is each item
only listed once in the table for each vendor, in which case you just
compare, or is it necessary to sum the values for each item and then compare
to the sums of the other items).

HTH, Ted Allen

"PZStraube" wrote:


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 Sco 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 Sco 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.


--
PZStraube

Ted,

Yes, my shaky hands must have accidently hit the POST button twice. Sorry
about that.

Being new to anything other than fairly basic work in Access, I will
research DCount and correlated subqueries as you suggested. I am purchasing
some new books on Access and will also look through this and other Access
forums.

I think your idea to rank the items for each vendor within each category
makes a lot of sense and I will pursue that.

To answer some of your questions...You were correct that I want each
category to give me the top 5 items for each vendor (albeit some items may
show up for more than one vendor, but that is fine). Each item is listed
once in a main table - so one item equates to one record. Each item/record
contains a multitude of additional data. Through 2 preliminary queries, I
score each item on 17 different components. In the end, the total of these
17 scores (a single data field) is what I will use for this project to
determine the "top 5" for each vendor inside each category. Also contained
in each record is a field that lists which vendors carry the item. For some
items, more than one vendor name appears. So, for example, when reviewing 3
vendors, instead of 15 (3 x 5) different items in each category, there might
be only 11 due to the same item being provided by more than one vendor. As
far as naming goes, the query is named Final_Scores, the category field is
Categories, the vendor field is Vendors (which may contain multiple vendors),
the items being scored are Item, and the final score used for ranking is
Overall_Score. A simplified version of the data looks like:
Query: Final_Scores
Category Vendors Item Overall_Score
LV Acme, ABC B_454 98.3%
LB ABC D_198 97.1%
LG XYZ, Acme, DEF C_255 84.9%
etc. for a few hundred records

I appreciate the kind offer of help and may take you up on that if I can't
figure things out on my own after being pointed in the right direction by
you. Hey, part of the "excitement" of working with Access is to get your
hands dirty once you know where to dig.
Thanks very much for your time in responding to my post.

"Ted Allen" wrote:

Hi Paul,

I'm not sure I completely understand what your data looks like, and what you
are trying to get, but I'm pretty sure that creating a rank field is likely
the way that you will achieve what you are trying to do. In your case, it
sounds like you would want to create a field that ranks the items within a
category, and I think also for the particular vendor. To do this, you would
generally use DCount() or a correlated subquery (I prefer correlated
subqueries but either will work) to determine the number of items matching
the category and vendor of the current item that have higher scores.

Once you have done this, all that you need to do to see the top 5 for each
unique category and vendor combination is to set the criteria for the rank
field to be =5.

You can find a lot of help on this issue by searching the group for rank and
correlated subquery. I would be happy to help as well, if you can post the
table and field names involved, and maybe a small sample of what the actual
data would look like. Also, confirm whether you want to see the top 5 in
each category for each vendor, or just the top 5 in each category. Finally,
give a little more detail on how to determine the item rank (is each item
only listed once in the table for each vendor, in which case you just
compare, or is it necessary to sum the values for each item and then compare
to the sums of the other items).

HTH, Ted Allen

"PZStraube" wrote:


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 Sco 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 Sco 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.


--
PZStraube

Hi Paul,

No problem about the double post, we've all done it by accident at least once.

I think you will find it much easier to do what you want to do if you can
separate the records for each vendor. I'm not sure what your source data
structure is like, but would it be possible to transform your sample data:

Query: Final_Scores
Category Vendors Item Overall_Score
LV Acme, ABC B_454 98.3%
LB ABC D_198 97.1%
LG XYZ, Acme, DEF C_255 84.9%

Into something more like the format:
LV ABC B_454 98.3%
LV Acme B_454 98.3%
LB ABC D_198 97.1%
LG Acme C_255 84.9%
LG DEF C_255 84.9%
LG XYZ C_255 84.9%

This will make it much easier to use DCount() or a correlated subquery
because both methods will use the value of category and vendor on the current
record to set the criteria of the domain to compare to. Your existing vendor
field has a list of vendors, so if you used it to set the domain to calculate
the rank, you would be ranking the item in comparison to all other items
using that same identical list of vendors.

If you can't easily get the data into that format with your existing data
structure, there may be other options such as using a query that generates
all unique combinations of category and vendor as the source, and calculating
the rank off of that (which wouldn't be very difficult). Or, I think you may
have mentioned that you only have three vendors, in that case the query could
probably calc the rank for each of the vendors for every record (the first
part of the calc in that case would be to check to see if the vendor name is
in the list).

Just to get you started though, here is a sample query with correlated
subquery that would rank the item within the category only.

SELECT q.*, 1 + (SELECT Count(VT.*) FROM Final_Scores AS VT WHERE
VT.Category = q.Category AND VT.Overall_Score q.Overall_Score) AS
ItemCategoryRank FROM Final_Scores AS q

This would return all records with their rank within the category, although
items with the same score would have the same rank (if you want a tiebreaker
you would usually modify slightly to use the item number to break ties).

In the query above, I have aliased the main query as q to save typing, and
within the subquery I aliased the same query again as VT (which stands for
virtual table, although you could use any alias). These represent two
distinct instances of the same source query. The references to q.fieldname
are referring to the values of the main query fields for a particular record
when calculating the calculated field.

The part of the query containing:
1 + (SELECT Count(VT.*) FROM Final_Scores AS VT WHERE VT.Category =
q.Category AND VT.Overall_Score q.Overall_Score)

is the actual correlated subquery. It is basically saying to count all
records in Final_Scores where the category matches the category of the
current record, and the Overall_Score is greater than that of the current
record. Of course, this will return 0 for the highest score, so it adds one
to the result (you could also use = as the criteria, it's just a slight
difference in the way ties are handled). Normally, if using the query
builder, you would just type this expression in the field source row in one
of the columns of the query (similar to the way you would type any other
calculated expression).

If you had vendor data in the modified format suggested above, all you would
have to do would be to add "AND VT.Vendor = q.Vendor" to the subquery and the
rank would be for the combination Vendor and Category. You could easily have
three calculated columns for each record, the Category Rank, the Vendor Rank,
and the combination Category/Vendor Rank.

Using DCount is very similar to the correlated subquery, it's just a
different syntax. Most posts seem to feel that the correlated subqueries
will work faster than DCount() which is a built in function.

HTH, Ted Allen

"PZ Straube" wrote:

Ted,

Yes, my shaky hands must have accidently hit the POST button twice. Sorry
about that.

Being new to anything other than fairly basic work in Access, I will
research DCount and correlated subqueries as you suggested. I am purchasing
some new books on Access and will also look through this and other Access
forums.

I think your idea to rank the items for each vendor within each category
makes a lot of sense and I will pursue that.

To answer some of your questions...You were correct that I want each
category to give me the top 5 items for each vendor (albeit some items may
show up for more than one vendor, but that is fine). Each item is listed
once in a main table - so one item equates to one record. Each item/record
contains a multitude of additional data. Through 2 preliminary queries, I
score each item on 17 different components. In the end, the total of these
17 scores (a single data field) is what I will use for this project to
determine the "top 5" for each vendor inside each category. Also contained
in each record is a field that lists which vendors carry the item. For some
items, more than one vendor name appears. So, for example, when reviewing 3
vendors, instead of 15 (3 x 5) different items in each category, there might
be only 11 due to the same item being provided by more than one vendor. As
far as naming goes, the query is named Final_Scores, the category field is
Categories, the vendor field is Vendors (which may contain multiple vendors),
the items being scored are Item, and the final score used for ranking is
Overall_Score. A simplified version of the data looks like:
Query: Final_Scores
Category Vendors Item Overall_Score
LV Acme, ABC B_454 98.3%
LB ABC D_198 97.1%
LG XYZ, Acme, DEF C_255 84.9%
etc. for a few hundred records

I appreciate the kind offer of help and may take you up on that if I can't
figure things out on my own after being pointed in the right direction by
you. Hey, part of the "excitement" of working with Access is to get your
hands dirty once you know where to dig.
Thanks very much for your time in responding to my post.

"Ted Allen" wrote:

Hi Paul,

I'm not sure I completely understand what your data looks like, and what you
are trying to get, but I'm pretty sure that creating a rank field is likely
the way that you will achieve what you are trying to do. In your case, it
sounds like you would want to create a field that ranks the items within a
category, and I think also for the particular vendor. To do this, you would
generally use DCount() or a correlated subquery (I prefer correlated
subqueries but either will work) to determine the number of items matching
the category and vendor of the current item that have higher scores.

Once you have done this, all that you need to do to see the top 5 for each
unique category and vendor combination is to set the criteria for the rank
field to be =5.

You can find a lot of help on this issue by searching the group for rank and
correlated subquery. I would be happy to help as well, if you can post the
table and field names involved, and maybe a small sample of what the actual
data would look like. Also, confirm whether you want to see the top 5 in
each category for each vendor, or just the top 5 in each category. Finally,
give a little more detail on how to determine the item rank (is each item
only listed once in the table for each vendor, in which case you just
compare, or is it necessary to sum the values for each item and then compare
to the sums of the other items).

HTH, Ted Allen

"PZStraube" wrote:


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 Sco 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 Sco 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.


--
PZStraube

Hi Paul,

One correction to my earlier post, please replace Count(VT.*) with Count(*)
in my earlier query. I normally just use the *, but I put in the VT. to help
show you what the query is counting, thinking that it would work either way.
But, later, I was thinking about it and I thought that I should try it to
make sure - and sure enough it gave a syntax error.

-Ted Allen

"Ted Allen" wrote:

Hi Paul,

No problem about the double post, we've all done it by accident at least once.

I think you will find it much easier to do what you want to do if you can
separate the records for each vendor. I'm not sure what your source data
structure is like, but would it be possible to transform your sample data:

Query: Final_Scores
Category Vendors Item Overall_Score
LV Acme, ABC B_454 98.3%
LB ABC D_198 97.1%
LG XYZ, Acme, DEF C_255 84.9%

Into something more like the format:
LV ABC B_454 98.3%
LV Acme B_454 98.3%
LB ABC D_198 97.1%
LG Acme C_255 84.9%
LG DEF C_255 84.9%
LG XYZ C_255 84.9%

This will make it much easier to use DCount() or a correlated subquery
because both methods will use the value of category and vendor on the current
record to set the criteria of the domain to compare to. Your existing vendor
field has a list of vendors, so if you used it to set the domain to calculate
the rank, you would be ranking the item in comparison to all other items
using that same identical list of vendors.

If you can't easily get the data into that format with your existing data
structure, there may be other options such as using a query that generates
all unique combinations of category and vendor as the source, and calculating
the rank off of that (which wouldn't be very difficult). Or, I think you may
have mentioned that you only have three vendors, in that case the query could
probably calc the rank for each of the vendors for every record (the first
part of the calc in that case would be to check to see if the vendor name is
in the list).

Just to get you started though, here is a sample query with correlated
subquery that would rank the item within the category only.

SELECT q.*, 1 + (SELECT Count(VT.*) FROM Final_Scores AS VT WHERE
VT.Category = q.Category AND VT.Overall_Score q.Overall_Score) AS
ItemCategoryRank FROM Final_Scores AS q

This would return all records with their rank within the category, although
items with the same score would have the same rank (if you want a tiebreaker
you would usually modify slightly to use the item number to break ties).

In the query above, I have aliased the main query as q to save typing, and
within the subquery I aliased the same query again as VT (which stands for
virtual table, although you could use any alias). These represent two
distinct instances of the same source query. The references to q.fieldname
are referring to the values of the main query fields for a particular record
when calculating the calculated field.

The part of the query containing:
1 + (SELECT Count(VT.*) FROM Final_Scores AS VT WHERE VT.Category =
q.Category AND VT.Overall_Score q.Overall_Score)

is the actual correlated subquery. It is basically saying to count all
records in Final_Scores where the category matches the category of the
current record, and the Overall_Score is greater than that of the current
record. Of course, this will return 0 for the highest score, so it adds one
to the result (you could also use = as the criteria, it's just a slight
difference in the way ties are handled). Normally, if using the query
builder, you would just type this expression in the field source row in one
of the columns of the query (similar to the way you would type any other
calculated expression).

If you had vendor data in the modified format suggested above, all you would
have to do would be to add "AND VT.Vendor = q.Vendor" to the subquery and the
rank would be for the combination Vendor and Category. You could easily have
three calculated columns for each record, the Category Rank, the Vendor Rank,
and the combination Category/Vendor Rank.

Using DCount is very similar to the correlated subquery, it's just a
different syntax. Most posts seem to feel that the correlated subqueries
will work faster than DCount() which is a built in function.

HTH, Ted Allen

"PZ Straube" wrote:

Ted,

Yes, my shaky hands must have accidently hit the POST button twice. Sorry
about that.

Being new to anything other than fairly basic work in Access, I will
research DCount and correlated subqueries as you suggested. I am purchasing
some new books on Access and will also look through this and other Access
forums.

I think your idea to rank the items for each vendor within each category
makes a lot of sense and I will pursue that.

To answer some of your questions...You were correct that I want each
category to give me the top 5 items for each vendor (albeit some items may
show up for more than one vendor, but that is fine). Each item is listed
once in a main table - so one item equates to one record. Each item/record
contains a multitude of additional data. Through 2 preliminary queries, I
score each item on 17 different components. In the end, the total of these
17 scores (a single data field) is what I will use for this project to
determine the "top 5" for each vendor inside each category. Also contained
in each record is a field that lists which vendors carry the item. For some
items, more than one vendor name appears. So, for example, when reviewing 3
vendors, instead of 15 (3 x 5) different items in each category, there might
be only 11 due to the same item being provided by more than one vendor. As
far as naming goes, the query is named Final_Scores, the category field is
Categories, the vendor field is Vendors (which may contain multiple vendors),
the items being scored are Item, and the final score used for ranking is
Overall_Score. A simplified version of the data looks like:
Query: Final_Scores
Category Vendors Item Overall_Score
LV Acme, ABC B_454 98.3%
LB ABC D_198 97.1%
LG XYZ, Acme, DEF C_255 84.9%
etc. for a few hundred records

I appreciate the kind offer of help and may take you up on that if I can't
figure things out on my own after being pointed in the right direction by
you. Hey, part of the "excitement" of working with Access is to get your
hands dirty once you know where to dig.
Thanks very much for your time in responding to my post.

"Ted Allen" wrote:

Hi Paul,

I'm not sure I completely understand what your data looks like, and what you
are trying to get, but I'm pretty sure that creating a rank field is likely
the way that you will achieve what you are trying to do. In your case, it
sounds like you would want to create a field that ranks the items within a
category, and I think also for the particular vendor. To do this, you would
generally use DCount() or a correlated subquery (I prefer correlated
subqueries but either will work) to determine the number of items matching
the category and vendor of the current item that have higher scores.

Once you have done this, all that you need to do to see the top 5 for each
unique category and vendor combination is to set the criteria for the rank
field to be =5.

You can find a lot of help on this issue by searching the group for rank and
correlated subquery. I would be happy to help as well, if you can post the
table and field names involved, and maybe a small sample of what the actual
data would look like. Also, confirm whether you want to see the top 5 in
each category for each vendor, or just the top 5 in each category. Finally,
give a little more detail on how to determine the item rank (is each item
only listed once in the table for each vendor, in which case you just
compare, or is it necessary to sum the values for each item and then compare
to the sums of the other items).

HTH, Ted Allen

"PZStraube" wrote:


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 Sco 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 Sco 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.


--
PZStraube