Query on Purchases

[rbs]

Hi Everyone,

I have a query for which i list the total quantity on hand of inventory.

I then have created a query which lists all of the purchase orders relating to this inventory.

I want to limit this query to only list the most recent purchase orders so that the quanities add up to the total quanity on hand.

This will then give me the purchase orders that the stock arrived on, not all purchase orders that have ever come in.

Can anyone help?

Thanks,

Natalie.

 

[mdmilner]

I think I understand what you're doing.
You want to identify, probably on a first-in, first-out basis which purchase order your inventory came from.

I'm thinking this is not something you're going to be able to do via simple SQL, *unless* your inventory tracks each individual item, not totals by item. If you track each individual item, you could include a field that identifies the source PO and group your results on that field.

If not; if you simply track quantity – that you have 10 units of Item X, then you have a lot more work and you have to make some assumptions.

My approach here would be:

1) Find out how many of a given item you have
2) Look at each PO where that item was ordered, work backwards chronologically until you have enough raw quantity included (possibly multiple PO’s) until you are equal are greater than the current inventory
3) Artificially ‘assign’ your current inventory count working backwards chronologically
4) Write your results to a temporary/report table
5) Repeat for each item


Mike