Query with multiple results

[MochaTrpl]

I have 4 separate queries that I am curious if there is a way to
achieve the same results on a single query.

$ total for the day
$ total for the week
$ total for the month
$ total for the year

Each one is part of the same form, like a switchboard to display the
totals like a dashboard. The form timer is set to refresh the form to
update the results. Unfortunitly, the locks up the system for a few
seconds during the update. If a single query could be built, would
it be faster to update? Is there a simple way to achieve the same
results?

 

[Douglas J Steele]

Assuming your table has a field TransactionDate, you could use something
like:

SELECT Sum(IIf([TransactionDate] = Date(), [SalesAmount], 0)) AS DailySales,
Sum(IIf(Format([TransactionDate], "yyyyww") = Format(Date(), "yyyyww"),
[SalesAmount], 0)) As Weekly Sales, Sum(IIf(Format([TransactionDate],
"yyyymm") = Format(Date(), "yyyymm"), [SalesAmount], 0)) As MonthlySales,
Sum(IIf(Year([TransactionDate]) = Year(Date()), [SalesAmount], 0)) As
YTDSales FROM MySalesTable

"MochaTrpl" wrote in message

I have 4 separate queries that I am curious if there is a way to
achieve the same results on a single query.

$ total for the day
$ total for the week
$ total for the month
$ total for the year

Each one is part of the same form, like a switchboard to display the
totals like a dashboard. The form timer is set to refresh the form to
update the results. Unfortunitly, the locks up the system for a few
seconds during the update. If a single query could be built, would
it be faster to update? Is there a simple way to achieve the same
results?