By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,066 Members | 1,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,066 IT Pros & Developers. It's quick & easy.

Crosstab data comparisons with nulls

P: 57
I have a table, Times. fields are: Horse, Track, HTime

Basically, my goal is to find out the difference between any track's timing, using only horses that have run at each of the 2 user-selected tracks.

My data table looks like this right now: (for simplicity sake, it has only 3 tracks; there are in actuality over 100)

Horse1 ... CT ... 52.50
Horse1 ... LRL ... 53.50
Horse1 ... PIM ... 53.00
Horse2 ... CT ... 53.00
Horse2 ... LRL ... 53.25
Horse3 ... LRL ... 52.95
Horse3 ... PIM ... 53.25
Horse4 ... PIM ... 53.15
Horse4 ... CT ... 52.75

I have tried the avenue of a crosstab called Test, rowheading is the Horse, columnheading is the Track and data-fills is the average HTime for each horse at each track.

What I want to do though is figure a method so a user can put in 2 tracks and get only the average difference in times for all horses that have run at both selected tracks. If a horse hasn't run at both tracks, his times aren't calculated.

Is there a manner to allow the user to select which columns matter (ie, CT and LRL or any combination the user desires out of a potential 100+ columns) and then only count the horses that ran both tracks?

Am I going the wrong way here with crosstabs? I am not code-smart but can get examples to work if given a way forward.
Apr 23 '09 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,709
We need first to look at getting a subquery to provide data grouped by [Horse] with selection criteria of :
Expand|Select|Wrap|Line Numbers
  1. WHERE [Track] In('TrackA','TrackB')
Max([HTime])-Min(HTime) would need to be returned(SELECT clause).
A HAVING clause would select only those records where Count(*)=2.

Once this subquery has been sorted, simply use this in the FROM clause of your outer query and Bob's your uncle.

PS. For help on subqueries see Subqueries in SQL.
Apr 23 '09 #2

P: 57
I have gotten moderately far off the information you've provided, however I'm still unable to determine where the Count(*) comes from.

I'm going very slowly, but here's the SQL code i have currently.

Expand|Select|Wrap|Line Numbers
  1. SELECT Times.Horse, Times.Track, Avg(Times.HTime) AS AvgOfHTime
  2. FROM Times
  3. WHERE (((Times.Track) In ([Enter First Track],[Enter Second Track])))
  4. GROUP BY Times.Horse, Times.Track;
  5.  
This brings me 6 rows of data that hit either selected track. Now I need to figure out how to filter out the horses that don't hit both selected tracks. I imagine that's where the Count comes in, but I'm not sure of how to work that.

I appreciate your initial guidance here and hope you can help me out further.
Apr 23 '09 #3

FishVal
Expert 2.5K+
P: 2,653
I guess the dataset thus obtained should be self-joined to get times per horse in one record thus making possible to calculate differences. This, BTW, filters dataset to give records for horses having results for both tracks.

Something like the following:

let us say the query you've posted is saved under name "qry"

Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.Horse, qry1.AvgOfTime - qry2.AvgOfTime AS AvgTimeDiff FROM qry AS qry1 INNER JOIN qry AS qry2 ON qry1.Horse=qry2.Horse AND qry1.Track<>qry2.Track AND qry1.AvgOfTime>=qry2.AvgOfTime;
  2.  
P.S. Actually, better:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qry1.Horse, Abs(qry1.AvgOfTime - qry2.AvgOfTime) AS AvgTimeDiff FROM qry AS qry1 INNER JOIN qry AS qry2 ON qry1.Horse=qry2.Horse AND qry1.Track<>qry2.Track;
  2.  
or use ">" or "<" instead of "<>" operator in qry1.Track {operator} qry2.Track expression, like

Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.Horse, Abs(qry1.AvgOfTime - qry2.AvgOfTime) AS AvgTimeDiff FROM qry AS qry1 INNER JOIN qry AS qry2 ON qry1.Horse=qry2.Horse AND qry1.Track>qry2.Track;
  2.  
Apr 23 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
@HSXWillH
Good attitude. If I haven't posted further tonight please feel free to bump the thread. I just need a little time to prepare a post (a bit hectic at work atm).
Apr 23 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
My mistake. A subquery wasn't required after all. Simply using both WHERE and HAVING clauses of a GROUP BY query was enough.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Horse],
  2.          Max([HTime])-Min([HTime]) AS [TimeDiff]
  3. FROM     [Times]
  4. WHERE    [Track] In([Enter First Track],[Enter Second Track])
  5. GROUP BY [Horse]
  6. HAVING   Count(*)=2
Apr 23 '09 #6

P: 57
NeoPa,

I put this query together and it returns solid information; however I might not have been totally clear on my initial question.

The Max - Min just returns the difference; what I was hoping for was the change from Track 1 to Track 2. order of entry does matter.

So a 1/2 second climb from CT to LRL is different than a 1/2 second drop from LRL to CT yet with the min/max structure, it reads the same.

I'm going to head down the path of a subquery of Time where Track = [Select First Track] and another of Track = [Select Second Track] and then compare those two results.

If this is the wrong tactic, would you please let me know? Thank you. Your help has been very useful in me getting this far.
Apr 24 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
In that case it's very similar, we just need to be a little more careful :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Horse],
  2.          Max(IIf([Track]=[Enter First Track],[HTime],Null)-
  3.          Max(IIf([Track]=[Enter Second Track],[HTime],Null) AS [TimeDiff]
  4. FROM     [Times]
  5. WHERE    [Track] In([Enter First Track],[Enter Second Track])
  6. GROUP BY [Horse]
  7. HAVING   Count(*)=2
Apr 24 '09 #8

P: 57
Okay, I see what this intending to do; I just get an error saying

synatx error (missing operand) in the expression and the cursor highlights the AS in AS [TimeDiff].

I have checked all the ( and they seem to be right. Thoughts on where this error is from? Thanks again.
Apr 24 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
Missing closing parentheses - ) - on both lines #2 & #3. Should have been :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Horse],
  2.          Max(IIf([Track]=[Enter First Track],[HTime],Null))-
  3.          Max(IIf([Track]=[Enter Second Track],[HTime],Null)) AS [TimeDiff]
  4. FROM     [Times]
  5. WHERE    [Track] In([Enter First Track],[Enter Second Track])
  6. GROUP BY [Horse]
  7. HAVING   Count(*)=2
Apr 24 '09 #10

P: 57
Thank you for this, well, everything. I do appreciate it.
Apr 24 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
It's a pleasure. I take it that means it worked ok for you.
Apr 24 '09 #12

P: 57
Perfectly. And it's simple enough I can modify this as needed. Thanks again.
Apr 24 '09 #13

Post your reply

Sign in to post your reply or Sign up for a free account.