Copy record with max value by week

[edsearl]

I would like to see an example of coding that would copy the entire record that contains the maximum value in a field by week or month to another table.

 

[dhookom]

I would like to see some table and significant field names as well as significant raw data and desired results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[edsearl]

CTI_MTRIDX YrMoDaHr CTH_KWH CTH_KVARH CTH_KVAH CTH_PF Week
SUB01250 2007061108 8864.099821 -4932.899901 10144.24798 -0.8738 24
SUB01250 2007061109 9185.399815 -4706.099905 10320.80162 -0.89 24
SUB01250 2007061110 9393.299811 -4498.199909 10414.79159 -0.9019 24
From the above data I would want the record with the max value in the CTH_KVAH field to appear in another table. Like:
SUB01250 2007061110 9393.299811 -4498.199909 10414.79159 -0.9019 24
The only way I know how to do it is with a couple of queries.
I wanted to do it in a module.

 

[VRoscioli]

Will YrMoDaHr always be unique? If so, you can try something like:

SELECT * FROM tblTable WHERE YrMoDaHr IN (SELECT Max(YrMoDaHr) FROM tblTable);

-V

 

[dhookom]

Is there a good reason why you wouldn't create the queries and then run them from your module?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[edsearl]

Maybe I should have said SQL.
What I have done in the past was to creat a query in design mode, group it by week to get the max CTH_KVAH, then link that query back to the table to get the rest of the record.
It seemed clumsy. I wanted it to be easier for someone in the future to work on.

Actually it is the CTH_KVAH field.
It is not unique.
I want only one record. The first would do.
And I need to group by the week number.

 

[PHV]

What is the SQL code of your 2 queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VRoscioli]

Does this do what you want:

SELECT TOP 1 * FROM tblTable WHERE YrMoDaHr IN (SELECT Max(YrMoDaHr) FROM tblTable);

-V

 

[edsearl]

Do I add By Week to the end of it?
Like:
SELECT TOP 1 * FROM tblTable WHERE [CTH_KVAH] IN (SELECT Max([CTH_KVAH]) FROM tblTable) By [Week];

 

[edsearl]

Or rather, Group By.

 

[PHV]

Again, what is YOUR actual SQL code grouping by week ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[edsearl]

The first query:
SELECT StationDaylyPeaksT.CTI_MTRID, StationDaylyPeaksT.Year, StationDaylyPeaksT.Week, Max(StationDaylyPeaksT.KW) AS KW
FROM StationDaylyPeaksT
GROUP BY StationDaylyPeaksT.CTI_MTRID, StationDaylyPeaksT.Year, StationDaylyPeaksT.Week;

Then, the second:
SELECT MV90SubKeys.CM_NAME, MaxKwWeekQ.CTI_MTRID, MaxKwWeekQ.Year, MaxKwWeekQ.Week, [KW]/1000 AS MW, [KVAR]/1000 AS MVAR, StationDaylyPeaksT.DayOfYear, StationDaylyPeaksT.Month, StationDaylyPeaksT.Day, StationDaylyPeaksT.Hour, StationDaylyPeaksT.DayOfWeek INTO SubWeeklyT
FROM (MaxKwWeekQ LEFT JOIN StationDaylyPeaksT ON (MaxKwWeekQ.KW = StationDaylyPeaksT.KW) AND (MaxKwWeekQ.Week = StationDaylyPeaksT.Week) AND (MaxKwWeekQ.Year = StationDaylyPeaksT.Year) AND (MaxKwWeekQ.CTI_MTRID = StationDaylyPeaksT.CTI_MTRID)) LEFT JOIN MV90SubKeys ON MaxKwWeekQ.CTI_MTRID = MV90SubKeys.CM_CUSTID
ORDER BY MV90SubKeys.CM_NAME, MaxKwWeekQ.Year, MaxKwWeekQ.Week;

 

[PHV]

What about this ?

SELECT S.CM_NAME, D.CTI_MTRID, D.Year, D.Week, D.KW/1000 AS MW, D.KVAR/1000 AS MVAR, D.DayOfYear, D.Month, D.Day, D.Hour, D.DayOfWeek
INTO SubWeeklyT
FROM (StationDaylyPeaksT AS D
INNER JOIN MV90SubKeys AS S ON D.CTI_MTRID = S.CM_CUSTID)
INNER JOIN (SELECT CTI_MTRID, [Year], Week, Max(KW) AS MaxKW
FROM StationDaylyPeaksT GROUP BY CTI_MTRID, [Year], Week
) AS M ON D.CTI_MTRID = M.CTI_MTRID AND D.Year = M.Year AND D.Week = M.Week AND D.KW = M.MaxKW
ORDER BY S.CM_NAME, D.Year, D.Week

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[edsearl]

Thank you. I'll try it.