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

SQL --> Access Query Conversion Question

P: 1
I'm inserting into a table, a value every five (5) minutes. How can I query for the min & max 15 minute sustained value for the day? This would be the three lowest & highest contiguous combined values for the requested 24 hour period (i.e. 10:10, 10:15 & 10:20), or whatever the case may be. The three values in a row that have the lowest & highest sums will be returned.

The following works perfectly in SQL Server, but for the life of me, I can't get the syntax correct for Access!!

SELECT Min(NewVal) AS MinValue, Max(NewVal) AS MaxValue
FROM
(Select A.Flow + B.Flow + C.Flow As NewVal
From MinMaxTest A
Inner Join MinMaxTest B
On A.Date_Time = DateAdd(n, -5, B.Date_Time)
Inner Join MinMaxTest C
On B.Date_Time = DateAdd(n, -5, C.Date_Time)
)AS A
WHERE MinMaxTest.Date_Time='02/28/07';

METADATA:
[DATE_TIME] [FLOW]
2/28/07 5:00 PM 3.560384212
2/28/07 5:05 PM 3.051757813
2/28/07 5:10 PM 3.814697266
2/28/07 5:15 PM 3.560384212
2/28/07 5:20 PM 3.814697266
2/28/07 5:25 PM 3.814697266
2/28/07 5:30 PM 3.814697266
2/28/07 5:35 PM 3.814697266
2/28/07 5:40 PM 3.814697266
2/28/07 5:45 PM 3.560384212
2/28/07 5:50 PM 3.560384212
2/28/07 5:55 PM 3.306070867
2/28/07 6:00 PM 3.560384212
2/28/07 6:05 PM 3.306070867
2/28/07 6:10 PM 3.814697266
2/28/07 6:15 PM 3.306070867
2/28/07 6:20 PM 3.306070867
2/28/07 6:25 PM 3.814697266
2/28/07 6:30 PM 3.814697266
2/28/07 6:35 PM 3.306070867
2/28/07 6:40 PM 3.814697266
2/28/07 6:45 PM 3.814697266
2/28/07 6:50 PM 3.814697266
2/28/07 6:55 PM 3.814697266
2/28/07 7:00 PM 3.814697266
2/28/07 7:05 PM 3.814697266
2/28/07 7:10 PM 3.814697266
2/28/07 7:15 PM 3.814697266
2/28/07 7:20 PM 3.814697266
2/28/07 7:25 PM 3.814697266
2/28/07 7:30 PM 3.814697266
2/28/07 7:35 PM 3.560384212
2/28/07 7:40 PM 3.814697266
2/28/07 7:45 PM 3.560384212
2/28/07 7:50 PM 3.560384212
2/28/07 7:55 PM 3.814697266

If anyone could help me figure this out, I'd really appreciate it!!

Steve
Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3 X.MinValue, X.MaxValue
  2. FROM (Select A.Flow + B.Flow + C.Flow As MinValue,
  3. A.Flow + B.Flow + C.Flow As MaxValue, A.Date_Time
  4.        From   (MinMaxTest A
  5.               Inner Join MinMaxTest B
  6.                  On A.Date_Time = DateAdd(n, -5, B.Date_Time))
  7.               Inner Join MinMaxTest C
  8.                  On B.Date_Time = DateAdd(n, -5, C.Date_Time)
  9.        )AS X
  10. WHERE X.Date_Time='02/28/07'
  11. ORDER BY X.MinValue ASC, X.MaxValue DESC;
  12.  
Mary
Mar 17 '07 #2

Post your reply

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