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.
12 1588 NeoPa 32,556
Expert Mod 16PB
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.
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.
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 32,556
Expert Mod 16PB @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).
NeoPa 32,556
Expert Mod 16PB
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
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 32,556
Expert Mod 16PB
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
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 32,556
Expert Mod 16PB
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
Thank you for this, well, everything. I do appreciate it.
NeoPa 32,556
Expert Mod 16PB
It's a pleasure. I take it that means it worked ok for you.
Perfectly. And it's simple enough I can modify this as needed. Thanks again.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John |
last post by:
I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.
The crosstab query...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: jb |
last post by:
Query Help Please-
I want to make:
name date value
lee sept 40
lee oct 27
lee nov 50
dan dec 50
jen jan 30
|
by: jkearns |
last post by:
Hello,
I made a report from a crosstab query following the steps onlined in
MSDN's Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get...
|
by: Federico Balbi |
last post by:
Hi,
I was wondering if PGSQL has a function similar to binary_checksum() of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean...
|
by: Penny |
last post by:
(Access 2003 Multiuser Split DB, Windows XP Pro)
Hi All,
I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same...
|
by: imani_technology_spam |
last post by:
I re-designed a predecessor's database so that it is more properly
normalized. Now, I must migrate the data from the legacy system into
the new one. The problem is that one of the tables is a...
|
by: m.wanstall |
last post by:
I have a crosstab query that compiles data for Months of the year. I
have a stacked select query on top of that crosstab query that uses
the latest 2 months data and exports it to a fixed length...
|
by: Mr.Tom.Willems |
last post by:
Hello people,
I am ussing an MS access database to enter and manage data from lab
tests.
until now i was the only one handeling the data so i had no need for a
controle on how missing data was...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |