Trying to explore a dataset in MS Access
The stuff of nightmares
Order IDs: Unique vs Distinct:
Seriously, it does not support count(distinct ID)..?!
select [Sub-Category], round(sum(profit))
from orders
group by [Sub-Category]
order by sum(profit) desc
Better do something with those tables.
SELECT year(order_date)
,round(sum(profit))
from orders
group by year(order_date)
order by year(order_date) asc
Profit going up nicely..
Lets see it without returns:
SELECT year(order_date)
,round(sum(profit))
from orders
where order_id not in (select [order id] from returns)
group by year(order_date)
order by year(order_date) asc
Those returns are problematic in 2017..
Let's do a join with a case when on those returns:
SELECT A.*, IIF (B.returned is null,"No", b.returned) as ret2
from orders A
left join returns B
on A.order_id = B.[order id]
IIF you mean..
Works nicely
Query a query?
SELECT ret2, year(order_date), round(sum(profit))
from ReturnedYesnostring
group by ret2, year(order_date)
order by year(order_date) asc
All cool.
