DVD Talk Forum

DVD Talk Forum (https://forum.dvdtalk.com/)
-   Tech Talk (https://forum.dvdtalk.com/tech-talk-10/)
-   -   Microsoft Access help - can't figure out query (https://forum.dvdtalk.com/tech-talk/594542-microsoft-access-help-cant-figure-out-query.html)

LolaRennt 09-15-11 09:31 AM

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?

X 09-15-11 11:50 AM

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

LolaRennt 09-15-11 12:11 PM

Re: Microsoft Access help - can't figure out query
 

Originally Posted by X (Post 10924644)
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.

X 09-15-11 12:19 PM

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.

DRG 09-15-11 12:47 PM

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.

Shazam 09-15-11 12:52 PM

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)

LolaRennt 09-15-11 05:06 PM

Re: Microsoft Access help - can't figure out query
 

Originally Posted by Shazam (Post 10924761)
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?

Shazam 09-15-11 05:26 PM

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


All times are GMT -5. The time now is 09:59 AM.


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