469,643 Members | 2,003 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

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

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

Jan 6 '06 #1
5 7013
PS. I know this is an Access group but I am doing this in Access.

Alan

Jan 6 '06 #2
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
Jan 7 '06 #3
PS. I know this is an Access group but I am doing this in Access.

Alan

Jan 7 '06 #4

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

Jan 9 '06 #5
<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


Jan 10 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Cady Steldyn | last post: by
6 posts views Thread by paii | last post: by
7 posts views Thread by Arild Larsen | last post: by
1 post views Thread by ????? | last post: by
9 posts views Thread by JJM0926 | last post: by
4 posts views Thread by Philip_collins | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.