SQL query - combine sum() with getting latest record by date field

[alanspamenglefield]

Hello group,
I have an SQL statement which pulls data from a table as follows:
" SELECT tblSites.sites_siteno, " & _
" tblSites.sites_sitename, " & _
" Sum(tblStockResults.stkr_result) AS SumOfstkr_result, " & _
" Sum(tblStockResults.stkr_takings) AS SumOfstkr_takings, " & _
" Sum(tblStockResults.stkr_gp) AS SumOfstkr_gp, " & _
" last(tblStockResults.stkr_closestkval) AS LastOfstkr_closestkval,

" & _
" Last(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays, " & _
" Last(tblStockResults.stkr_endat) AS LastOfstkr_endat, " & _
" sum(tblStockResults.stkr_cashdiff) as SumOfstkr_cashdiff, " & _
" sum(tblstockresults.stkr_resultcost) as SumOfstkr_resultcost "
" FROM tblSites " & _
" INNER JOIN tblStockResults " & _
" ON tblSites.sites_siteid = tblStockResults.stkr_siteid " & _
" WHERE tblStockResults.stkr_endat Is Not Null " & _
" GROUP BY tblSites.sites_siteno, tblSites.sites_sitename; "
The three 'last' statements must be the latest values (by date -
stkr_endat) from that particular site (siteid) but the 'sum' statements

must remain as a sum of all values from that site.
However, if the data is originally fed into the table out of date order

then this always pulls up the last record to be physically entered
instead of the latest record by date (if that makes sense!)
How can I change this so that it always pulls up the latest dated
record (from stkr_endat) and not just the last one to be entered?
Thanks in advance,
Alan

[alanspamenglefield]

PS. I know this is an Access group but I am doing this in Access.

Alan

[Wayne Gillespie]

On 6 Jan 2006 09:05:37 -0800, al****************@yahoo.co.uk wrote:

Hello group,
I have an SQL statement which pulls data from a table as follows:
" SELECT tblSites.sites_siteno, " & _
" tblSites.sites_sitename, " & _
" Sum(tblStockResults.stkr_result) AS SumOfstkr_result, " & _
" Sum(tblStockResults.stkr_takings) AS SumOfstkr_takings, " & _
" Sum(tblStockResults.stkr_gp) AS SumOfstkr_gp, " & _
" last(tblStockResults.stkr_closestkval) AS LastOfstkr_closestkval,

" & _
" Last(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays, " & _
" Last(tblStockResults.stkr_endat) AS LastOfstkr_endat, " & _
" sum(tblStockResults.stkr_cashdiff) as SumOfstkr_cashdiff, " & _
" sum(tblstockresults.stkr_resultcost) as SumOfstkr_resultcost "
" FROM tblSites " & _
" INNER JOIN tblStockResults " & _
" ON tblSites.sites_siteid = tblStockResults.stkr_siteid " & _
" WHERE tblStockResults.stkr_endat Is Not Null " & _
" GROUP BY tblSites.sites_siteno, tblSites.sites_sitename; "
The three 'last' statements must be the latest values (by date -
stkr_endat) from that particular site (siteid) but the 'sum' statements

must remain as a sum of all values from that site.
However, if the data is originally fed into the table out of date order

then this always pulls up the last record to be physically entered
instead of the latest record by date (if that makes sense!)
How can I change this so that it always pulls up the latest dated
record (from stkr_endat) and not just the last one to be entered?
Thanks in advance,
Alan

Use Max instead of Last. eg -

Max(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays
Wayne Gillespie
Gosford NSW Australia

[alanspamenglefield]

PS. I know this is an Access group but I am doing this in Access.

Alan

[alanspamenglefield]

Wayne Gillespie wrote:

Use Max instead of Last. eg -

Max(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays
Wayne Gillespie
Gosford NSW Australia

OK, but this will olny work for the date field (stkr_endat), if I use
it for the other two fields then it will return the *highest* value
which will not always be the correct one.

stkr_Closestkval is a currency value and stkr_SoHDays is a numerical
value.

Thanks,

Alan

[Anthony England]

<al****************@yahoo.co.uk> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Wayne Gillespie wrote:

Use Max instead of Last. eg -

Max(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays
Wayne Gillespie
Gosford NSW Australia

OK, but this will olny work for the date field (stkr_endat), if I use
it for the other two fields then it will return the *highest* value
which will not always be the correct one.

stkr_Closestkval is a currency value and stkr_SoHDays is a numerical
value.

Thanks,

Alan

The following SQL is not tested, but may work as it is. If it does need
tweaking, it at least gives you the idea for a general solution.
On thing that crosses my mind is what happens if tblStockResults has two
records for the same site number on the same date. Perhaps you have an
index which prevents this, but if not, you could use the primary key of the
table to make sure that the subquery always selects one record. The
subqueries then need ORDER BY stkr_endat DESC, stkr_id DESC assuming that
stkr_id is the name of this field:
SELECT
S.sites_siteno,
S.sites_sitename,
T.*,
(SELECT TOP 1 stkr_closestkval FROM tblStockResults
WHERE T.stkr_siteid=tblStockResults.stkr_siteid
ORDER BY stkr_endat DESC) AS LastOfstkr_closestkval,
(SELECT TOP 1 stkr_SoHDays FROM tblStockResults
WHERE T.stkr_siteid=tblStockResults.stkr_siteid
ORDER BY stkr_endat DESC) AS LastOfstkr_SoHDays,
(SELECT TOP 1 stkr_endat FROM tblStockResults
WHERE T.stkr_siteid=tblStockResults.stkr_siteid
ORDER BY stkr_endat DESC) AS LastOfstkr_endat
FROM (SELECT
stkr_siteid,
Sum(stkr_result) AS SumOfstkr_result,
Sum(stkr_takings) AS SumOfstkr_takings,
Sum(stkr_cashdiff) as SumOfstkr_cashdiff,
Sum(stkr_resultcost) as SumOfstkr_resultcost
FROM tblStockResults
WHERE stkr_endat Is Not Null
GROUP BY stkr_siteid) AS T
INNER JOIN tblSites AS S
ON S.sites_siteno=T.stkr_siteid