I have data stored in this format:
Name | Order Date | Order Amount
---
I need it displayed such as:
Name | Jun 16-30 | Jul | Aug | Sep | Oct | ... | May | Jun 1 - 15
---
With order amount summed under each date range/month.
Is there a way to accomplish this with a pivot, or sum with a case? I need to sum where day/month is between dates, but year is not important.
The general format for using conditional aggregation in MS Access is:
select name,
sum(iif(datepart("month", OrderDate) 6 and datepart("day", OrderDate) between 16 and 30, OrderAmount, 0)) as [Jun 16-30],
sum(iif(datepart("month", OrderDate) = 7, OrderAmount, 0) as [Jul],
. . .
from t
group by name;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments