workaround to "not in aggregate" query problem

  • Thread starter Thread starter Dick Watson
  • Start date Start date
D

Dick Watson

By way of example, I have a table of data points named test:

dataset x y
1 1.5 0.5
1 1.7 0.3
1 1.8 -0.2
1 1.9 -0.5
2 2.8 0.9
2 2.9 0.3
2 3.1 -0.25
2 3.3 -0.75

I have a query:

SELECT test.dataset, Min(test.y) AS MinPos_y
FROM test
WHERE (((test.y)>0))
GROUP BY test.dataset;

But the problem is that I need to know the test.x values that were
associated with those test.y values identified as the Min value greater than
0.

Obviously, this doesn't work since test.x is not part of the agregate, but
also obvioulsy, I cannot group on test.x and get the desired result:

SELECT test.dataset, test.x, Min(test.y) AS MinPos_y
FROM test
WHERE (((test.y)>0))
GROUP BY test.dataset;

I've tried various forms of queries that look, simplified, like this:

SELECT datasets.dataset, DLookUp("x","SELECT test.dataset, Min(test.y) AS
MinOfy
FROM test
WHERE (((test.y)>0))
GROUP BY test.dataset","dataset = " & [datasets].[dataset]) AS MinPos_y
FROM [SELECT DISTINCT test.dataset
FROM test]. AS datasets;

or things similar to this:

SELECT datasets.dataset,
(SELECT x
FROM test
WHERE (((test.y)=[MinPos_y]) AND ((test.dataset)=datasets.dataset))) AS x,
(SELECT Min(test.y) AS MinPos_y
FROM test
WHERE (((test.y)>0) AND ((test.dataset)=datasets.dataset))
GROUP BY test.dataset) AS MinPos_y
FROM [SELECT DISTINCT test.dataset
FROM test]. AS datasets;

All to No Joy.

Any hints how to get the x and aggregated y values back together again?

Thanks in advance!
 
How about:

SELECT T.Dataset, T.x, T.y
FROM test as T
INNER JOIN (SELECT Dataset, Min([y] as MinPosY
FROM test
WHERE [Y] > 0
GROUP BY Dataset) as S
ON T.dataset = S.dataset
and t.y = s.y

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
I figured there was a way I was missing. I've been here before but I don't
play with Access consistently enough to remember this kinda stuff. My real
problem is much more complicated than the case I pose here, but this looks
like the path.

Thanks!
 
Actually, that should have read:
SELECT T.Dataset, T.x, T.y
FROM test as T
INNER JOIN (SELECT Dataset, Min([y] as MinPosY
FROM test
WHERE [Y] > 0
GROUP BY Dataset) as S
ON T.dataset = S.dataset
and t.y = s.MinPosY
Click to expand...
Click to expand...

The following will work too:

SELECT t.dataset, t.x, t.y
FROM test as T
WHERE T.y = (SELECT Min(Y)
FROM test
WHERE [y] > 0
AND dataset = t.dataset)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Dick Watson said:
I figured there was a way I was missing. I've been here before but I don't
play with Access consistently enough to remember this kinda stuff. My real
problem is much more complicated than the case I pose here, but this looks
like the path.

Thanks!

Dale Fye said:
How about:

SELECT T.Dataset, T.x, T.y
FROM test as T
INNER JOIN (SELECT Dataset, Min([y] as MinPosY
FROM test
WHERE [Y] > 0
GROUP BY Dataset) as S
ON T.dataset = S.dataset
and t.y = s.y
Click to expand...
Click to expand...
 
Yes. I had so much transalation to do to get back to my real case that I
sorted those out along the way. They KEY was the inner join to the aggregate
as subquery.

I now have nine Access query objects (two of which have these embedded
aggregate queries inside them) all chained together to get one average
elapsed time to get to an interpolated location derived from thousands of
time/location data points for flights along a given route. Pretty cool stuff.
So neat when it works!

Thanks again!
 
Back
Top