show highest from each category only?

[r]

I have a table showing user test scores from multiple locations. There is a
field for location number, and each location is assigned a unique number.
There could be 5 locations, or 20; the number could vary.

I need to pull the user record with the highest scores from each location.
So if there are 20 locations, there should be 20 records showing each
locations highest scoring record; if there are 6 locations, then 6 resulting
records.

Can this be done in a single query, if so, I'd like to learn how.

Thanks.

 

[LeAnne]

SELECT LocationNumber, MAX([TestScore]) AS HighestScore
FROM YourTable
GROUP BY LocationNumber;

hth,

LeAnne

 

[r]

Thanks! Unfortunately, I forgot to mention another criteria (sigh) ... it
also has a date/time stamp, and if two records have the same score from a
location, I need to use the one that finished first. *sheepish grin* How
could I add that in??

Thanks so much, LeAnne...!

SELECT LocationNumber, MAX([TestScore]) AS HighestScore
FROM YourTable
GROUP BY LocationNumber;

hth,

LeAnne

I have a table showing user test scores from multiple locations. There is a
field for location number, and each location is assigned a unique number.
There could be 5 locations, or 20; the number could vary.

I need to pull the user record with the highest scores from each location.
So if there are 20 locations, there should be 20 records showing each
locations highest scoring record; if there are 6 locations, then 6 resulting
records.

Can this be done in a single query, if so, I'd like to learn how.

Thanks.

 

[r]

Actually, I worked out the soonest time stamp thing. What I CAN'T seem to
bring in is the UserID of the resulting records! I'm guessing it's because
I can't "groupby" on the IDs. How do I pull them in and not end up with all
the records showing again?

Thanks! Unfortunately, I forgot to mention another criteria (sigh) ... it
also has a date/time stamp, and if two records have the same score from a
location, I need to use the one that finished first. *sheepish grin* How
could I add that in??

Thanks so much, LeAnne...!

SELECT LocationNumber, MAX([TestScore]) AS HighestScore
FROM YourTable
GROUP BY LocationNumber;

hth,

LeAnne

I have a table showing user test scores from multiple locations.

There
is a

 

[LeAnne]

r,

I'm a bit confused. What is meant by "if two records have the same score
from one location?" And when you say "the one that finished first," are
you referring to the one with the *earlier* Date/Time stamp? Does a user
take a test twice at the same location on the same date, one in the
morning & one in the afternoon? Can a user take a test at one or more
locations? Can a location test one or more users? And how many tests are
there? Can a location offer one or more tests? Can one or more tests be
taken by one or more users at one or more locations? What if a user
takes the same test at different locations? Are the tests themselves
timed (like the SATs, with start & stop times), or is the user's time
upon completion of the test recorded?

I'm afraid I can't offer more specific advice about constructing your
query without some info about your db structure.

L.

Thanks! Unfortunately, I forgot to mention another criteria (sigh) ... it
also has a date/time stamp, and if two records have the same score from a
location, I need to use the one that finished first. *sheepish grin* How
could I add that in??

Thanks so much, LeAnne...!

SELECT LocationNumber, MAX([TestScore]) AS HighestScore
FROM YourTable
GROUP BY LocationNumber;

hth,

LeAnne

I have a table showing user test scores from multiple locations. There is a
field for location number, and each location is assigned a unique number.
There could be 5 locations, or 20; the number could vary.

I need to pull the user record with the highest scores from each location.
So if there are 20 locations, there should be 20 records showing each
locations highest scoring record; if there are 6 locations, then 6 resulting
records.

Can this be done in a single query, if so, I'd like to learn how.

Thanks.

 

[r]

Yes, I thought I had the rest of the query figured out, but seems every step
is creating another stumbling point for me.

Let me explain what we're doing, and the db layout. And thank you for your
time. (I'd be happy to send you the db - it is small as it has no live data
in it yet.)

There are 4 tables:
tblShop - (shopID, shopName,shopNumber) - lists shop locations
tblTest - (testID, testName, testFinal) - lists existing test numbers
tblUser - (userID, First, Last, ShopNum) - lists users who've signed up
tblUser2Test - (autonumID, userIDnum, testIDnum, timestamp) - all user test
results

I need a query with the following criteria:

* Top Score on Final from each Shop
* If multiple users with same top score, the one who finished first only
* User must have taken a Final (testFinal yes/no) AND another test that is
not a Final
* Need to also display ShopID, ShopName, ShopNumber, UserID, First & Last

Again, my db is very small since it's only being used to set everything up.
I can send it if you like.

Thanks again.

r,

I'm a bit confused. What is meant by "if two records have the same score
from one location?" And when you say "the one that finished first," are
you referring to the one with the *earlier* Date/Time stamp? Does a user
take a test twice at the same location on the same date, one in the
morning & one in the afternoon? Can a user take a test at one or more
locations? Can a location test one or more users? And how many tests are
there? Can a location offer one or more tests? Can one or more tests be
taken by one or more users at one or more locations? What if a user
takes the same test at different locations? Are the tests themselves
timed (like the SATs, with start & stop times), or is the user's time
upon completion of the test recorded?

I'm afraid I can't offer more specific advice about constructing your
query without some info about your db structure.

L.

Thanks! Unfortunately, I forgot to mention another criteria (sigh) ... it
also has a date/time stamp, and if two records have the same score from a
location, I need to use the one that finished first. *sheepish grin* How
could I add that in??

Thanks so much, LeAnne...!

SELECT LocationNumber, MAX([TestScore]) AS HighestScore
FROM YourTable
GROUP BY LocationNumber;

hth,

LeAnne

r wrote:

I have a table showing user test scores from multiple locations.

There
is a

field for location number, and each location is assigned a unique number.
There could be 5 locations, or 20; the number could vary.

I need to pull the user record with the highest scores from each location.
So if there are 20 locations, there should be 20 records showing each
locations highest scoring record; if there are 6 locations, then 6 resulting
records.

Can this be done in a single query, if so, I'd like to learn how.

Thanks.

 

[LeAnne]

Yes, I thought I had the rest of the query figured out, but seems every step
is creating another stumbling point for me.

Let me explain what we're doing, and the db layout. And thank you for your
time. (I'd be happy to send you the db - it is small as it has no live data
in it yet.)

There are 4 tables:
tblShop - (shopID, shopName,shopNumber) - lists shop locations
tblTest - (testID, testName, testFinal) - lists existing test numbers
tblUser - (userID, First, Last, ShopNum) - lists users who've signed up
tblUser2Test - (autonumID, userIDnum, testIDnum, timestamp) - all user test
results

Hi r,

You're on the right track. You basically have 3 entities: Users; Tests;
and Locations (aka Shops). It looks to me like these entities are
related many-to-many. Each User can take one OR MORE tests. Each User
can be tested at one OR MORE locations. Each Test can be offered at one
OR MORE locations. And each location can offer one OR MORE tests.

Since Access cannot represent m:n relationships, you need "resolver"
tables to break up the m:n into 2 1:m relationships. You've already got
one, tblUser2Test. You just need 1 more; let's call it tblTestShop.
tblTestShop will resolve the relationship between Shops & Tests. Put the
pk's from tblTests and tblShops into this resolver as fk's, and give the
TestShop table a pk of its own (say, TestLocID). (Resolver tables can
also contain other information that is pertinent to the unique
combination of fields in a record...in this case, that would be ShopID +
TestID. So TestLoc could contain additional fields if you like:
TestDate; TestProctor; etc..). Finally, put TestLocID into your
User2Test table as an fk, instead of TestID, and include the timestamp &
Test score.

Once your entities and relationships are in place, queries should become
much easier. Although, honestly, this late in the day, I can't think of
a way off the top of my head to do all that you ask in a single gonzo
query. Interspersed below are some suggestions to get you started:

I need a query with the following criteria:

* Top Score on Final from each Shop

SELECT Shops.ShopName, Shops.ShopLoc, Max(UserTest.Score) AS MaxOfScore
FROM Shops INNER JOIN (Tests INNER JOIN (TestLoc INNER JOIN (Users INNER
JOIN UserTest ON Users.UserID = UserTest.UserID) ON TestLoc.TestLocID =
UserTest.TestLocID) ON Tests.TestID = TestLoc.TestID) ON Shops.ShopID =
TestLoc.ShopID
WHERE (((Tests.[Final?])=-1))
GROUP BY Shops.ShopName, Shops.ShopLoc;

* If multiple users with same top score, the one who finished first only

Same top score on same test at same shop? Final score or any score?

* User must have taken a Final (testFinal yes/no) AND another test that is
not a Final
* Need to also display ShopID, ShopName, ShopNumber, UserID, First & Last

Why don't you play around a bit with the modified design & see what you
come up with? If I think of anything else I'll post back.

hth,

LeAnne