Multiple Totals in one Query

[Guest]

I have monthly sales data for a number of tenants. I want to get a YTD Sum
and ad Running Year Sum i.e. June_2004 to July_2005. I have no problem doing
either in a seperate query, but am having problems getting them into one
query. Is there anyway to create a single query to do this using the Access
query design form, or do I have to write this in SQL or join two queries? I
can do the SQL, I was just wondering if there was an easier way.

2 Tables Tenant and Sales linked by Store_ID.

Sales has Sales_ID, Store_ID, Amount, Date.

 

[Guest]

Use this SQL statement for your query.
SELECT Tenant.Store_ID, Sum([Sales-1].Amount) AS Total,
Sum((IIf(Format([SaleDate],"yyyy")=Format(Date(),"yyyy"),[Amount],0))) AS
YTD,
Sum((IIf(Format([SaleDate],"yyyy")=Format(DateAdd("yyyy",-1,Date()),"yyyy"),[Amount],0))) AS [Last Year]
FROM Tenant LEFT JOIN [Sales-1] ON Tenant.Store_ID = [Sales-1].Store_ID
WHERE ((([Sales-1].SaleDate) Between [Enter start date] And [Enter end date]))
GROUP BY Tenant.Store_ID;

Change Sales-1 to your table name. Change SaleDate to your field name (you
should not use 'Date' as a field name as it is a reserved named in Access).

I put in more than you were asking for so you can leave it in or remove them
(Last Year).

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not do all this in a report? That's what they're for.

In the report - get YTD by grouping by Year(date_column) and have a
=Sum(Amount) in a year footer.

For running sum use the Running Sum property of a TextBox w/ Amount as
the ControlSourc. Set the property to "Over Group" if you want to have
each the Year or "Over All" if you want a running sum for the whole
report.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9alq4echKqOuFEgEQLCkgCg7IxazqylLuz3+eGexUQG7foOJqsAoMzX
JIBzc21o35T/b1KVk93bwiQs
=UzTA
-----END PGP SIGNATURE-----