Query to clasify inventory

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which columns are: product, units, exp date.
I want to do a query that gives me two columns: inventory less than a year,
inventory More than a year.

How can I do this?

Thanks,

FA
 
SELECT Product,
Sum(IIF(ExpDate>DateDiff("yyyy",-1,Date()),Units,Null)) as
TotalCurrentUnits,
Sum(IIF(ExpDate>DateDiff("yyyy",-1,Date()),Null,Units)) As TotalOlderUnits
FROM YourTable
GROUP BY Product

If you just want a list of products in two columns

SELECT IIF(ExpDate>DateDiff("yyyy",-1,Date()),Product,Null) as Current,
IIF(ExpDate>DateDiff("yyyy",-1,Date()),Null,Product) As Older
FROM YourTable
 
I want the actual amounts.
I used the first query.
The older column is empty and it shouldn't.

Actually, as the inventory is of pharmaceutical products one of the columns
should be short date (any expiration date from today to 12 months from now)
and the rest are good date.

Any other suggestions?
 
Can you post an example of the output you want? Is it something like

Product; Year-Month; No of Units
Aspirin 2004-11 120
Aspirin 2004-12 180
Aspirin 2005-01 90

Or is it something else.
 
I worked my way modifing the Datediff formula that you sent me and obtained
what I wanted =

The output have three columns:
Product More Than 12mth Less than 12 mth
Aspirin 100 20

I change the formula for expdate-date()>365.

Thank you very much.

FA
 
Back
Top