Forum Discussion
Current Date in Select Query
SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID,
(SELECT SUM(AmountOfChange)
FROM qryLCAmends AS T2
WHERE T2.LCID = T1.LCID
AND T2.Expirey2 < DATE()) AS LCBalance
FROM qryLCAmends AS T1
ORDER BY LCID;This will return the detail rows returned by the outer query, and the sum of the AmountOfChange values for the current LCID value in each row? Is that what's required, rather than returning the balance per transaction per subset of distinct LCID value? If the latter, you might like to take a look at Balances.zip in my public databases folder at:
https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg
This little demo file illustrates a number of queries for returning balances in different contexts, either over all rows or per group.
Tony2021 You need DATE() function. DATE() function will return current date.
select date() as [CurrentDate];In case of your SQL, it will be-
SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID
(SELECT SUM(AmountOfChange)
FROM qryLCAmends AS T2
WHERE T2.LCID = T1.LCID
AND T2.Expirey2 < date())
AS LCBalance
FROM qryLCAmends AS T1
ORDER BY LCID;- Hi Harun, thank you for the response.
Sorry but I am not a programmer.
I am not exactly sure how to combine both of the select queries?
I dont think its like this, correct?
select date() as [CurrentDate];
SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID
(SELECT SUM(AmountOfChange)
FROM qryLCAmends AS T2
WHERE T2.LCID = T1.LCID
AND T2.Expirey2 < date())
AS LCBalance
FROM qryLCAmends AS T1
ORDER BY LCID;
thank you for your help.SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID,
(SELECT SUM(AmountOfChange)
FROM qryLCAmends AS T2
WHERE T2.LCID = T1.LCID
AND T2.Expirey2 < DATE()) AS LCBalance
FROM qryLCAmends AS T1
ORDER BY LCID;This will return the detail rows returned by the outer query, and the sum of the AmountOfChange values for the current LCID value in each row? Is that what's required, rather than returning the balance per transaction per subset of distinct LCID value? If the latter, you might like to take a look at Balances.zip in my public databases folder at:
https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg
This little demo file illustrates a number of queries for returning balances in different contexts, either over all rows or per group.
- thank you very much. thanks for that link as well. A lot of info there. many thanks...
