|
|
Posted by alanenglefield on 04/21/06 17:50
Hi all,
I am a newbie with SQL and I have a statement that someone else wrote
for me.
It works up to a fashion but there seems to be a bug with it (or maybe
I didn't make it very clear to start with ;-P).
The statement needs to pull in details from a table. If there are more
than one record for a given 'siteID' then it needs to total (sum) most
of the fields, however, there are three fields in which it needs to
pull the _latest_ value through. The original statement uses 'last' to
achieve this but this approach seems to rely on the data being input in
the correct order which is not always the case.
The fields in question are a date field, an integer field and a
currency field so consequently, using 'max' is not possible because the
latest figure may not always be the greatest figure.
Here is the relevant SQL statement:
SQ = "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, " &
_
<snip>
" sum(tblstockresults.stkr_var_sundries) as SumOfstkr_var_sundries,
" & _
" sum(tblstockresults.stkr_adjustment) as SumOfstkr_adjustment " &
_
" FROM tblSites " & _
" INNER JOIN tblStockResults " & _
" ON tblSites.sites_siteid = tblStockResults.stkr_siteid " & _
" WHERE tblStockResults.stkr_endat Is Not Null and stkr_endat
between #" & Format("27/2/06", "dd-mmm-yyyy") & "# and #" &
Format(ToDate, "dd-mmm-yyyy") & "# " & _
" GROUP BY tblSites.sites_siteno, tblSites.sites_sitename; "
I have been searching google-groups for an answer and came across a
statement similar to the following:
select *
from tbl
group by SSN,Name
having TestDate=max(Testdate)
but I am unsure of how to implement it and also I don't think that it
will work for the Currency and Integer fields.
I have a feeling that the answer lies in nesting where clauses (!) but
that is only a phrase that I have seen bandied around these groups and
I have no idea how to begin implementing it.
If you have read this far then thank you very much for any assistance
that you feel able to give.
Regards,
Alan Englefield
Navigation:
[Reply to this message]
|