Return only the latest reading of the day 3

[svankley]

I have 1 table called T_WWPS_1_Daily_Values' with 3 columns ('Date', 'COL1', 'COL2').
What i want to do is generate a report of the Pump Runtimes. Everything works fine except - if, as seen below, there are 2 readings for the same day,

Month Day Year Date Pump 1 Daily Runtime Pump 2 Daily Runtime
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20
7 5 2006 7/5/2006 2:56:26 PM 0.10 0.45
7 5 2006 7/5/2006 12:25:53 PM 7.10 10.20

I want to just display the last (12:25:53pm) reading.
So it would look like this:

Month Day Year Date Pump 1 Daily Runtime Pump 2 Daily Runtime
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20
7 5 2006 7/5/2006 2:56:26 PM
7 5 2006 7/5/2006 12:25:53 PM 0.10 10.20


I have now:

SELECT DatePart("m",T_WWPS_1_Daily_Values.Date) AS [Month], DatePart("d",T_WWPS_1_Daily_Values.Date) AS [Day], DatePart("yyyy",T_WWPS_1_Daily_Values.Date) AS [Year], T_WWPS_1_Daily_Values.Date, T_WWPS_1_Daily_Values.COL1, T_WWPS_1_Daily_Values.COL2, [COL1]+[COL2] AS [PS1 RunTime Total]
FROM T_WWPS_1_Daily_Values
WHERE (((DatePart("m",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidMonth) And ((DatePart("yyyy",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidYear))
ORDER BY DatePart("d",T_WWPS_1_Daily_Values.Date);

I tried changing the select line to add the max function: max(DatePart("d",T_WWPS_1_Daily_Values.Date)) AS [Day]
but i keep getting the "You tried to execute a query that does not include the specified expression 'DatePart("m",T_WWPS_1_Daily_Values.Date)' as part of an aggregate function???

Any ideas -thoughts would be greatly appreciated,

Steve

 

[lespaul]

Try this:

SELECT DatePart("m",T_WWPS_1_Daily_Values.Date) AS [Month], DatePart("d",T_WWPS_1_Daily_Values.Date) AS [Day], DatePart("yyyy",T_WWPS_1_Daily_Values.Date) AS [Year], Max(T_WWPS_1_Daily_Values.Date), T_WWPS_1_Daily_Values.COL1, T_WWPS_1_Daily_Values.COL2, [COL1]+[COL2] AS [PS1 RunTime Total]
FROM T_WWPS_1_Daily_Values
WHERE (((DatePart("m",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidMonth) And ((DatePart("yyyy",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidYear))
GROUP BY DatePart("m",T_WWPS_1_Daily_Values.Date), DatePart("d",T_WWPS_1_Daily_Values.Date), DatePart("yyyy",T_WWPS_1_Daily_Values.Date),
T_WWPS_1_Daily_Values.COL1, T_WWPS_1_Daily_Values.COL2, [COL1]+[COL2]
ORDER BY DatePart("d",T_WWPS_1_Daily_Values.Date);

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[svankley]

I still get 2 listings for the 5th.
Query result:
Month Day Year Expr1003 Pump 1 Daily Runtime Pump 2 Daily Runtime PS1 RunTime Total
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20 10.3000026717782
7 5 2006 7/5/2006 2:56:26 PM
7 5 2006 7/5/2006 12:25:53 PM 0.10 10.20 10.3000026717782

 

[svankley]

SORRY - I'm an absolute idiot!!! I contradicted myself. What I want display is the last time for each day that there is a record. When I copy/pasted my first question, I forgot to delete the "7 5 2006 7/5/2006 2:56:26 PM" line in the desired results - Sorry!! 8^(

This is what I'm trying to achieve:

Month Day Year Date Pump 1 Daily Runtime Pump 2 Daily Runtime
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20
7 5 2006 7/5/2006 12:25:53 PM 0.10 10.20

 

[lespaul]

but you said you wanted the Max time, and in this example you're getting the Min time (12:25 comes before 2:56).

There aren't any other fields in the table?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[svankley]

Like I said - I'm an idiot! 8^)
You're correct, I've been so swamped the past couple days, I can't even think straight.
What I want returned is:
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20 10.3000026717782
7 5 2006 7/5/2006 2:56:26 PM

Yes, there are other fields but I'm doing a straight inner join on them, and that all works fine. The only problem I still have left is this issue of returning multiple times for a given day. I can only have one record on the report for each day and that should be the last reading for that particular date in the database.

Thanks so much for your help with this,

Steve

 

[lespaul]

Ok, what you need to do is have a join statement that gets some unique identifer along with the max date and join into that. I'd need more information about your real query to help.

your original question though:

I tried changing the select line to add the max function: max(DatePart("d",T_WWPS_1_Daily_Values.Date)) AS [Day]
but i keep getting the "You tried to execute a query that does not include the specified expression 'DatePart("m",T_WWPS_1_Daily_Values.Date)' as part of an aggregate function???

is caused by adding non aggregate fields to the SELECT statement and not adding them to a GROUP BY clause (plus I think you should get the Max Of the [Date] field, not the split date - and another aside, you should refrain from using keywords (like Date or Time) as field names).

If you have:

SELECT SUM(Sales) From TableName

the query will return a single field with the total of that field. If however you want to get a total of each customer you would add CustomerID to both the SELECT statement and the GROUP BY clause:

SELECT CustomerID, Sum(Sales) From TableName GROUP BY CustomerID

(for more information on why this is required see my posting in Thread701-1258387 about 1/2 way down).

So, to correct your original problem you need to add every field in the SELECT statement that is not an aggregate (MAX, MIN, SUM, COUNT, AVG are all examples of aggregates) to a GROUP BY clause.

HTH

Leslie

 

[svankley]

OK, as requested, here is the full info and queries.
I inherited this problem from another company at a water treatment plant.
There is a SCADA system that is used to display process data and record values to the Access database for 9 stations (1, 4, 5, 13, 14, 19, 23 & 28).
Each station's pump runtime is recorded from the SCADA system into the database at 11:58pm every night, and then whenever the state required reports need to be generated, a report is ran.
Basically what this query is supposed to do is run a query on each stations stored data, return a recordset from each - link the dates and join them into a single recordset to be displayed on an Access report.

Here is the actual complete data and queries (formatted without line wrapping, so it will appear propery in notepad) - I'll use station #1 for an example:

Table data for 7/06:

Month Day Year Date Pump 1 Daily Runtime Pump 2 Daily Runtime Pump 3 Daily Runtime Pump 4 Daily Runtime Discharge Flow *1000 Gals Pump 1 KW Pump 2 KW Pump 3 KW Pump 4 KW FIR_A_0120 FIR_B_0120 Vault Meter PS1 RunTime Total PS1 Total Flow PS1 TOTAL KW
7 1 2006 7/1/2006 12:25:53 PM 0.10 10.20 0.00 1.20 20.90 0.08 13.75 0.00 1.87 2.56 2.64 11.5000028386712 5.20000004768372 15.7024290934205
7 5 2006 7/5/2006 2:56:26 PM 14.70 2.28 3.27 5.54999995231628
7 5 2006 7/5/2006 12:25:53 PM 0.10 10.20 0.00 1.20 20.90 0.08 13.75 0.00 1.87 2.56 2.64 11.5000028386712 5.20000004768372 15.7024290934205

==================
Q_WWPS_1_MONTHLY query used by report query below:

SELECT DatePart("m",T_WWPS_1_Daily_Values.Date) AS [Month], DatePart("d",T_WWPS_1_Daily_Values.Date) AS [Day], DatePart("yyyy",T_WWPS_1_Daily_Values.Date) AS [Year], T_WWPS_1_Daily_Values.Date, T_WWPS_1_Daily_Values.COL1, T_WWPS_1_Daily_Values.COL2, T_WWPS_1_Daily_Values.COL3, T_WWPS_1_Daily_Values.COL4, T_WWPS_1_Daily_Values.COL5, T_WWPS_1_Daily_Values.COL6, T_WWPS_1_Daily_Values.Col7, T_WWPS_1_Daily_Values.COL8, T_WWPS_1_Daily_Values.COL9, T_WWPS_1_Daily_Values.COL10, T_WWPS_1_Daily_Values.COL11, [COL1]+[COL2]+[COL3]+[COL4] AS [PS1 RunTime Total], [Col10]+[COL11] AS [PS1 Total Flow], [COL6]+[COL7]+[COL8]+[COL9] AS [PS1 TOTAL KW]
FROM T_WWPS_1_Daily_Values
WHERE (((DatePart("m",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidMonth) And ((DatePart("yyyy",T_WWPS_1_Daily_Values.Date))=Forms!F_Main!hidYear))
ORDER BY DatePart("d",T_WWPS_1_Daily_Values.Date);


=======================
Report Query (used to generate all data for the report):

SELECT Q_WWPS_1_MONTHLY.Date, Q_WWPS_1_MONTHLY.Day, Q_WWPS_1_MONTHLY.[PS1 RunTime Total], Q_WWPS_4_MONTHLY.[Station 4 Run Time Total], Q_WWPS_5_MONTHLY.[Station 5 Run Time Total], Q_WWPS_13_MONTHLY.[Station 13 Run Time Total], Q_WWPS_14_MONTHLY.[Station 14 Run Time Total], Q_WWPS_19_MONTHLY.[PS19 RunTime Total], Q_WWPS_23_MONTHLY.[PS23 RunTime Total], Q_WWPS_28_MONTHLY.[St 28 DPP Run Time Total], Q_WWPS_28_MONTHLY.[PS 28 BP Run Time Total]

FROM Q_WWPS_1_MONTHLY INNER JOIN (Q_WWPS_4_MONTHLY INNER JOIN (Q_WWPS_5_MONTHLY INNER JOIN (Q_WWPS_13_MONTHLY INNER JOIN (Q_WWPS_14_MONTHLY INNER JOIN (Q_WWPS_19_MONTHLY INNER JOIN (Q_WWPS_23_MONTHLY INNER JOIN Q_WWPS_28_MONTHLY
ON (Q_WWPS_23_MONTHLY.Year=Q_WWPS_28_MONTHLY.Year) AND (Q_WWPS_23_MONTHLY.Day=Q_WWPS_28_MONTHLY.Day) AND (Q_WWPS_23_MONTHLY.Year=Q_WWPS_28_MONTHLY.Year))
ON (Q_WWPS_19_MONTHLY.Month=Q_WWPS_23_MONTHLY.Month) AND (Q_WWPS_19_MONTHLY.Day=Q_WWPS_23_MONTHLY.Day) AND (Q_WWPS_19_MONTHLY.Year=Q_WWPS_23_MONTHLY.Year))
ON (Q_WWPS_14_MONTHLY.Year=Q_WWPS_19_MONTHLY.Year) AND (Q_WWPS_14_MONTHLY.Day=Q_WWPS_19_MONTHLY.Day) AND (Q_WWPS_14_MONTHLY.Year=Q_WWPS_19_MONTHLY.Year))
ON (Q_WWPS_13_MONTHLY.Month=Q_WWPS_14_MONTHLY.Month) AND (Q_WWPS_13_MONTHLY.Day=Q_WWPS_14_MONTHLY.Day) AND (Q_WWPS_13_MONTHLY.Year=Q_WWPS_14_MONTHLY.Year))
ON (Q_WWPS_5_MONTHLY.Month=Q_WWPS_13_MONTHLY.Month) AND (Q_WWPS_5_MONTHLY.Day=Q_WWPS_13_MONTHLY.Day) AND (Q_WWPS_5_MONTHLY.Year=Q_WWPS_13_MONTHLY.Year))
ON (Q_WWPS_4_MONTHLY.Year=Q_WWPS_5_MONTHLY.Year) AND (Q_WWPS_4_MONTHLY.Day=Q_WWPS_5_MONTHLY.Day) AND (Q_WWPS_4_MONTHLY.Month=Q_WWPS_5_MONTHLY.Month))
ON (Q_WWPS_1_MONTHLY.Year=Q_WWPS_4_MONTHLY.Year) AND (Q_WWPS_1_MONTHLY.Day=Q_WWPS_4_MONTHLY.Day) AND (Q_WWPS_1_MONTHLY.Month=Q_WWPS_4_MONTHLY.Month)

GROUP BY Q_WWPS_1_MONTHLY.Date, Q_WWPS_1_MONTHLY.Day, Q_WWPS_1_MONTHLY.[PS1 RunTime Total], Q_WWPS_4_MONTHLY.[Station 4 Run Time Total], Q_WWPS_5_MONTHLY.[Station 5 Run Time Total], Q_WWPS_13_MONTHLY.[Station 13 Run Time Total], Q_WWPS_14_MONTHLY.[Station 14 Run Time Total], Q_WWPS_19_MONTHLY.[PS19 RunTime Total], Q_WWPS_23_MONTHLY.[PS23 RunTime Total], Q_WWPS_28_MONTHLY.[St 28 DPP Run Time Total], Q_WWPS_28_MONTHLY.[PS 28 BP Run Time Total];

========================

 

[Golom]

This just selects one record for each [Date] ... that being the one with the latest time on that date.

SELECT DatePart("m",D.[Date]) AS [Month]
     , DatePart("d",D.[Date]) AS [Day]
     , DatePart("yyyy",D.[Date]) AS [Year]
     , D.[Date]
     , D.COL1, D.COL2, D.COL3, D.COL4, D.COL5
     , D.COL6, D.Col7, D.COL8, D.COL9, D.COL10, D.COL11
     , [COL1]+[COL2]+[COL3]+[COL4] AS [PS1 RunTime Total]
     , [COL10]+[COL11] AS [PS1 Total Flow]
     , [COL6]+[COL7]+[COL8]+[COL9] AS [PS1 TOTAL KW]

FROM T_WWPS_1_Daily_Values D

WHERE (((DatePart("m",D.[Date]))=Forms!F_Main!hidMonth) 
  And ((DatePart("yyyy",D.[Date]))=Forms!F_Main!hidYear))
  [red]AND D.[Date] = (Select MAX([Date]) From T_WWPS_1_Daily_Values X
                  Where X.[Date] BETWEEN cDate(Int(D.[Date])) 
                                 AND cDate(Int(D.[Date])) + 1)[/red]

ORDER BY DatePart("d",D.[Date]);

Here you don't appear to need the "Group By" because you are not using any aggregate functions.

SELECT Q01.Date, Q01.Day
     , Q01.[PS1 RunTime Total]
     , Q04.[Station 4 Run Time Total]
     , Q05.[Station 5 Run Time Total]
     , Q13.[Station 13 Run Time Total]
     , Q14.[Station 14 Run Time Total]
     , Q19.[PS19 RunTime Total]
     , Q23.[PS23 RunTime Total]
     , Q28.[St 28 DPP Run Time Total]
     , Q28.[PS 28 BP Run Time Total]

FROM Q_WWPS_1_MONTHLY Q1 
     INNER JOIN (Q_WWPS_4_MONTHLY Q4 
     INNER JOIN (Q_WWPS_5_MONTHLY Q5 
     INNER JOIN (Q_WWPS_13_MONTHLY Q13 
     INNER JOIN (Q_WWPS_14_MONTHLY Q14 
     INNER JOIN (Q_WWPS_19_MONTHLY Q19 
     INNER JOIN (Q_WWPS_23_MONTHLY Q23 
     INNER JOIN Q_WWPS_28_MONTHLY Q28
ON (Q23.[red]Month[/red]=Q28.[red]Month[/red]) AND (Q23.Day=Q28.Day) AND (Q23.Year=Q28.Year) 
ON (Q19.Month=Q23.Month) AND (Q19.Day=Q23.Day) AND (Q19.Year=Q23.Year)) 
ON (Q14.[red]Month[/red]=Q19.[red]Month[/red]) AND (Q14.Day=Q19.Day) AND (Q14.Year=Q19.Year)) 
ON (Q13.Month=Q14.Month) AND (Q13.Day=Q14.Day) AND (Q13.Year=Q14.Year)) 
ON (Q05.Month=Q13.Month) AND (Q05.Day=Q13.Day) AND (Q05.Year=Q13.Year)) 
ON (Q04.Year=Q05.Year)   AND (Q04.Day=Q05.Day) AND (Q04.Month=Q05.Month)) 
ON (Q01.Year=Q04.Year)   AND (Q01.Day=Q04.Day) AND (Q01.Month=Q04.Month)

 

[lespaul]

and that's why you're tipmaster of the week! Congratulations and thanks for pitching in here!

les

 

[Golom]

Even being TMOW doesn't prevent typos.

In the second query
[tt]
Q_WWPS_1_MONTHLY Q1 should be Q_WWPS_1_MONTHLY Q[red]0[/red]1
Q_WWPS_4_MONTHLY Q4 should be Q_WWPS_4_MONTHLY Q[red]0[/red]4
Q_WWPS_5_MONTHLY Q5 should be Q_WWPS_5_MONTHLY Q[red]0[/red]5
[/tt]

 

[svankley]

The individual queries work fine but the second one (main report query) keeps giving me a "Syntax error in the join operation" error. I made the 3 changes you said in your last post. Is it one of the parenthesis in the on clauses? I keep confusing myself with the nesting.

Thanks so much so far,

Steve

 

[svankley]

With everyones help, I got it to work:

SELECT Q01.Date, Q01.Day, Q01.[PS1 RunTime Total], Q04.[Station 4 Run Time Total], Q05.[Station 5 Run Time Total], Q13.[Station 13 Run Time Total], Q14.[Station 14 Run Time Total], Q18.[PS18 Run Time Total], Q19.[PS19 RunTime Total], Q23.[PS23 RunTime Total], Q28.[St 28 DPP Run Time Total], Q28.[PS 28 BP Run Time Total]
FROM Q_WWPS_1_MONTHLY AS Q01 INNER JOIN (Q_WWPS_4_MONTHLY AS Q04 INNER JOIN (Q_WWPS_5_MONTHLY AS Q05 INNER JOIN (Q_WWPS_13_MONTHLY AS Q13 INNER JOIN (Q_WWPS_14_MONTHLY AS Q14 INNER JOIN (Q_WWPS_18_MONTHLY AS Q18 INNER JOIN (Q_WWPS_19_MONTHLY AS Q19 INNER JOIN (Q_WWPS_23_MONTHLY AS Q23 INNER JOIN Q_WWPS_28_MONTHLY AS Q28 ON (Q23.Month=Q28.Month) AND (Q23.Day=Q28.Day) AND (Q23.Year=Q28.Year)) ON (Q19.Month=Q23.Month) AND (Q19.Day=Q23.Day) AND (Q19.Year=Q23.Year)) ON (Q18.Month=Q19.Month) AND (Q18.Day=Q19.Day) AND (Q18.Year=Q19.Year)) ON (Q14.Month=Q18.Month) AND (Q14.Day=Q18.Day) AND (Q14.Year=Q18.Year)) ON (Q13.Month=Q14.Month) AND (Q13.Day=Q14.Day) AND (Q13.Year=Q14.Year)) ON (Q05.Month=Q13.Month) AND (Q05.Day=Q13.Day) AND (Q05.Year=Q13.Year)) ON (Q04.Year=Q05.Year) AND (Q04.Day=Q05.Day) AND (Q04.Month=Q05.Month)) ON (Q01.Year=Q04.Year) AND (Q01.Day=Q04.Day) AND (Q01.Month=Q04.Month);

Thanks so much very everyones help!!!

Steve