Crosstab data comparisons with nulls

[HSXWillH]

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.

[NeoPa]

We need first to look at getting a subquery to provide data grouped by [Horse] with selection criteria of :

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.

[HSXWillH]

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.

SELECT Times.Horse, Times.Track, Avg(Times.HTime) AS AvgOfHTime
FROM Times
WHERE (((Times.Track) In ([Enter First Track],[Enter Second Track])))
GROUP BY Times.Horse, Times.Track;

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.

[FishVal]

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"

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;

P.S. Actually, better:

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;

or use ">" or "<" instead of "<>" operator in qry1.Track {operator} qry2.Track expression, like

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;

[NeoPa]

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

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

[NeoPa]

My mistake. A subquery wasn't required after all. Simply using both WHERE and HAVING clauses of a GROUP BY query was enough.

SELECT   [Horse],
         Max([HTime])-Min([HTime]) AS [TimeDiff]
FROM     [Times]
WHERE    [Track] In([Enter First Track],[Enter Second Track])
GROUP BY [Horse]
HAVING   Count(*)=2

[HSXWillH]

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.

[NeoPa]

In that case it's very similar, we just need to be a little more careful :

SELECT   [Horse],
         Max(IIf([Track]=[Enter First Track],[HTime],Null)-
         Max(IIf([Track]=[Enter Second Track],[HTime],Null) AS [TimeDiff]
FROM     [Times]
WHERE    [Track] In([Enter First Track],[Enter Second Track])
GROUP BY [Horse]
HAVING   Count(*)=2

[HSXWillH]

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.

[NeoPa]

Missing closing parentheses - ) - on both lines #2 & #3. Should have been :

SELECT   [Horse],
         Max(IIf([Track]=[Enter First Track],[HTime],Null))-
         Max(IIf([Track]=[Enter Second Track],[HTime],Null)) AS [TimeDiff]
FROM     [Times]
WHERE    [Track] In([Enter First Track],[Enter Second Track])
GROUP BY [Horse]
HAVING   Count(*)=2

[HSXWillH]

Thank you for this, well, everything. I do appreciate it.

[NeoPa]

It's a pleasure. I take it that means it worked ok for you.

[HSXWillH]

Perfectly. And it's simple enough I can modify this as needed. Thanks again.