Nested SQL/Subquery & Aggregate functions

[stuartmcmillan]

Hi! I'm trying to write some sql that groups by one value (a shop branch) from a subset of data (a set of sales, selected by tranasaction dates). Basically, I want to be able to set a date range and it adds all the sales up per branch, instead of grouping it by branch AND sales. So far I have the folowing non-working code....

SELECT [Tbl Transactions].Branch, Sum([Tbl Transactions].Nett), Sum([Tbl Transactions].Gross)
FROM [Tbl Transactions]
GROUP BY [Tbl Transactions].Branch
WHERE [Tbl Transactions].Ref = (SELECT [Tbl Transactions].Ref FROM [Tbl Transactions] HAVING ([Tbl Transactions].[Transaction Date] Between #10/30/2005# And #11/7/2005#))

The dates I have selected are just an arbitary test.

Free pair of outdoor socks to the winner... 🙂

Many thanks in advance!
Stuart

[stuartmcmillan]

OK, I spoke to soon.... Managed to do it...

SELECT [Tbl Transactions].Branch, Sum([Tbl Transactions].Nett), Sum([Tbl Transactions].Gross)
FROM (SELECT [Tbl Transactions].Branch, [Tbl Transactions].Nett, [Tbl Transactions].Gross FROM [Tbl Transactions] WHERE ([Tbl Transactions].[Transaction Date] Between #$datestart# And #$dateend#))
GROUP BY [Tbl Transactions].Branch