Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Microsoft Access help - can't figure out query

Old 09-15-11, 09:31 AM
  #1  
Senior Member
Thread Starter
 
Join Date: Mar 2003
Posts: 789
Microsoft Access help - can't figure out query

Hi,

I was hoping someone could help me with a query I can't figure out.

I have 2 tables:
1. A table with a log of a runner ('runnerlog'), the date and the distance the runner ran that day.
2. A table with 'recognition' ('recognitions'). A recognition is defined as a total distance that a runner must have ran (all runs combined) and then he/she gets the recognition.

Let's get some sample data:

runnerlog:
John, Sept 1, 20 (John ran 20km on sept 1)
John, Sept 2, 15
John, Sept 4, 35
Lisa, Sept 1, 5
Lisa, Sept 4,10
Mark, Sept 1, 5

recognitions
bronze, 10 (a runner gets bronze once 10km in total has been ran)
silver, 50
gold, 100


So, I need to design a query that now looks at which recognitions a runner should get. Ideally, the output of the query should be:
John, 70, silver (john ran a total of 70 km, which is more than 50, so he gets silver)
Lisa, 15, bronze

(and note that Mark only ran 5k, so he gets no recognitions whatsoever yet, so the query does not report on him)

Any idea how I can write this query?
LolaRennt is offline  
Old 09-15-11, 11:50 AM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,682
Re: Microsoft Access help - can't figure out query

It's easier in 3 queries, each looking at a range, such as this for the bronze:

select name from runnerlog where distance >= 10 and distance < 50
X is offline  
Old 09-15-11, 12:11 PM
  #3  
Senior Member
Thread Starter
 
Join Date: Mar 2003
Posts: 789
Re: Microsoft Access help - can't figure out query

Originally Posted by X View Post
It's easier in 3 queries, each looking at a range, such as this for the bronze:

select name from runnerlog where distance >= 10 and distance < 50
I simplified my problem a bit of course for the sake of keeping things readable in this forum.

The issue is that I have about 15 different categories of 'recognitions' and even that can be changed by the user. The user has access to a form linked to recognitions table, and can add a recognition at will. So, tomorrow, he could put a 'wood' recognition at 75km.

So, I think splitting it in x queries will not be practical.
LolaRennt is offline  
Old 09-15-11, 12:19 PM
  #4  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,682
Re: Microsoft Access help - can't figure out query

I think the query would be quite complex then and you would have to put those 15 categories into it. I don't think having the recognitions in a separate table, with no info to join the two tables, buys you anything. Unless you write code, which you certainly can do in Access. It just wouldn't be a single query, it would be multiple queries constructed in code. That's not too hard to do.
X is offline  
Old 09-15-11, 12:47 PM
  #5  
DRG
DVD Talk Legend
 
Join Date: Aug 1999
Location: ND
Posts: 13,399
Re: Microsoft Access help - can't figure out query

Yeah, I could figure out how to do this in a few lines of php code using variables fairly easily, but in one SQL statement it seems trickier.

You need to compare a SUM for each person with a MAX of matching goals on the recognition side.

Last edited by DRG; 09-15-11 at 12:58 PM.
DRG is offline  
Old 09-15-11, 12:52 PM
  #6  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
Re: Microsoft Access help - can't figure out query

It would be easier if the recognitions table was set up like this:

recognition min max
bronze 10 49 (a runner gets bronze once 10km in total has been ran)
silver 50 99
gold 100 (some really large number here)
Shazam is offline  
Old 09-15-11, 05:06 PM
  #7  
Senior Member
Thread Starter
 
Join Date: Mar 2003
Posts: 789
Re: Microsoft Access help - can't figure out query

Originally Posted by Shazam View Post
It would be easier if the recognitions table was set up like this:

recognition min max
bronze 10 49 (a runner gets bronze once 10km in total has been ran)
silver 50 99
gold 100 (some really large number here)
Funny that you mention this, because that is the exact change I just made. But, still struggling to get build my sql statement that would get me the requested result. Any ideas?
LolaRennt is offline  
Old 09-15-11, 05:26 PM
  #8  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
Re: Microsoft Access help - can't figure out query

select a.runner, a.totalDistance, b.recognition
from (select runner, sum(distance) as totalDistance from dbo.runnerlog group by runner) a
inner join dbo.recognition b
on a.totalDistance between b.mindistance and b.maxdistance
Shazam is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.