Get min week for each record

[umair434]

Hi all,

I have a table with products information (week, inventory, orders coming from supplier).

I want to find out what week is the first order coming in.

I have tried using dlookup to find week but, not working.

Database attached (with table called desired output which is what I want).

any suggestions?

Thanks everyone

 

[Cronk]

You've complicated things by splitting a start of week into week number and year components.

Try this for a start
Select PROD_NBR, Min([Year] & Format([Week],"00")) From Orders where [From Supplier]>0 group by PROD_NBR

 

[umair434]

Thanks, Cronk.

That helps. How would Iadd the [From supplier] field, to give me the quantity that was received, for that time period i.e. when the first quantity came, and how much was it.

 

[plog]

Your explanation doesn't really make sense, so I tried to duplicate your Desired results. You would think that the first order would just be the lowest numbered Week/Year permutation, but it seems your definition is the record where the inventory is at its lowest, so I used that.

First you will need a sub-query to find the lowest inventory for every PROD_NBR:

SELECT Orders.PROD_NBR, Min(Orders.Inventory) AS LowestInventory
FROM Orders
GROUP BY Orders.PROD_NBR;

Paste that into a query and save it as 'sub_FirstOrder'. Then you create another query using that and Orders to find the exact record (or records) that match the lowest inventory:

SELECT Orders.PROD_NBR, Orders.Week, Orders.Year, Orders.Inventory, Orders.[From Supplier]
FROM sub_FirstWeek INNER JOIN Orders ON (sub_FirstWeek.LowestInventory = Orders.Inventory) AND (sub_FirstWeek.PROD_NBR = Orders.PROD_NBR);

That query will produce your desired results. This may produce multiple records if there are 2 records in Orders that both have the lowest inventory for a PROD_NBR.

 

[umair434]

Thanks guys. That works.

It's been a while since I used access and hopefully things will come back to me quickly.