GROUPBY EXPRESSION PROBLEM

[zendigui]

Hi all,

I have a table with

Date | Hour | Stock | Price
1/1/07 10:00 NOK 20$
1/1/07 10:00 DELL 24$
1/1/07 10:30 DELL 23$
1/1/07 11:00 DELL 22$
1/1/07 11:15 DELL 20$
1/1/07 11:15 NOK 22$
1/1/07 12:00 NOK 19$
1/1/07 12:15 NOK 16$



The "GROUPBY MAX MIN "query results I have now are:

Date | Stock | MAX | MIN |
1/1/07 DELL 24 20
1/1/07 NOK 22 16

The results I want are:

Date | Stock | MAX | Max HOUR | MIN | MinHour |
1/1/07 DELL 24 10:00 20 11:15
1/1/07 NOK 22 11:15 16 12:15

in other words I need my query to give me the times the Max $ and Min $ has been reached. any idea??

thanks guys

 

[jedraw]

Here's a solution, but it is done step wise using the original table and 4 derived queries -
1- MinMax, 2 - Mins, 3 - Maxs, 4 - Requested

- My sample table is TekTips20070119
with fields(based on your question)

mDate,MHour,stock, price

1. "MinMax" is a query that gets the Min and Max prices for stocks for a day. It does not deal with Times(Hours)

Code for MinMax:

SELECT TekTips20070119.mDate, TekTips20070119.stock, Max(TekTips20070119.price) AS MaxOfprice, Min(TekTips20070119.price) AS MinOfprice
FROM TekTips20070119
GROUP BY TekTips20070119.mDate, TekTips20070119.stock
ORDER BY TekTips20070119.mDate;

Output of MinMax = your current output:
mDate stock MaxOfprice MinOfprice
1/1/2007 DELL $24.00 $20.00
1/1/2007 NOK $22.00 $16.00

2. "Mins" is a query that gets the Times for each of the MinPrice for the stocks for the day.

Code for Mins:

SELECT MinMax.mDate, MinMax.stock, TekTips20070119.price AS MinPrice, TekTips20070119.mHour AS minHour
FROM MinMax INNER JOIN TekTips20070119 ON (MinMax.mDate = TekTips20070119.mDate) AND (MinMax.MinOfprice = TekTips20070119.price) AND (MinMax.stock = TekTips20070119.stock);

Output for Mins:
mDate stock MinPrice minHour
1/1/2007 DELL $20.00 11:15:00 AM
1/1/2007 NOK $16.00 12:15:00 AM

3. "Maxs" is a query that gets the Times for each of the MaxPrice for the stocks for the day.

Code for Maxs:

SELECT MinMax.mDate, MinMax.stock, MinMax.MaxOfprice, A.mHour AS maxHour
FROM TekTips20070119 AS A INNER JOIN MinMax ON (A.mDate = MinMax.mDate) AND (A.stock = MinMax.stock) AND (A.price = MinMax.MaxOfprice);

Output of Maxs:
mDate stock MaxOfprice maxHour
1/1/2007 DELL $24.00 10:00:00 AM
1/1/2007 NOK $22.00 11:15:00 AM

4. "requested" is a query that provides the output you requested.

Code for requested:

SELECT Maxs.mDate, Maxs.stock, Maxs.MaxOfprice, Maxs.maxHour, MINs.MinPrice, MINs.minHour
FROM Maxs INNER JOIN MINs ON (Maxs.mDate = MINs.mDate) AND (Maxs.stock = MINs.stock);

Output of requested:

mDate stock MaxOfprice maxHour MinPrice minHour
1/1/2007 DELL $24.00 10:00:00 AM $20.00 11:15:00 AM
1/1/2007 NOK $22.00 11:15:00 AM $16.00 12:15:00 AM


Hope this is helpful.

 

[zendigui]

thanks for your reply

what you're doing basically is finding the Mins and the Maxs. Using other queries you search the min and the max from the original table to find the times.

I thought there was a way to do it using the expression option. query within a query.

How about crosstable queries is can't it do the job?

thanks,

ONCE YOU STOP LEARNING YOU STOP LEADING

 

[PHV]

What have YOU tried so far and where in your code are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zendigui]

I actually have no code. I was wondering if the results can be achieved by an expression such as Vlookup but that's excel not access.