Use Query to find due date

[mattc66 via AccessMonster.com]

Hi All,

I need some help..Some would say allot of help.

I have a product that I need to report to our sales department the date of
availablity.

Example Product X is out of stock negative -8 with the following qty due and
due dates.

qty due date
2 1/09
5 1/13
3 1/16

I want to return the date of 1/16 as the next date we will have stock
available.

I have a table that contains the qty and due dates. How would I setup a query
to obtain this data?

Matt

 

[Smartin]

Hi All,

I need some help..Some would say allot of help.

I have a product that I need to report to our sales department the date of
availablity.

Example Product X is out of stock negative -8 with the following qty due and
due dates.

qty due date
2 1/09
5 1/13
3 1/16

I want to return the date of 1/16 as the next date we will have stock
available.

I have a table that contains the qty and due dates. How would I setup a query
to obtain this data?

Matt

This works... not sure how useful (generic) it is.

If your table is called StockExpected...

SELECT MIN(SE1.DueDate)
FROM StockExpected AS SE1
WHERE
(SELECT SUM(Qty) FROM StockExpected AS SE2
WHERE SE2.DueDate <= SE1.DueDate
HAVING SUM(Qty) > 8)

8

;

 

[mattc66 via AccessMonster.com]

I got your example to work as well. However there are about 10 different
products and the qty needed may change. So I see I'll need another query with
the current Product qty needed. Is that what you'd do?

Hi All,

[quoted text clipped - 18 lines]

Matt

This works... not sure how useful (generic) it is.

If your table is called StockExpected...

SELECT MIN(SE1.DueDate)
FROM StockExpected AS SE1
WHERE
(SELECT SUM(Qty) FROM StockExpected AS SE2
WHERE SE2.DueDate <= SE1.DueDate
HAVING SUM(Qty) > 8)

8

;

 

[Smartin]

Glad to hear it, but no, that's not what I would do. I would look for a
way to make it generic to the qty needed (and product, and date, etc.)
Confidentially though, I'm not there yet.

It's easy with VBA to substitute the parameter you need. But this is the
Queries forum, and in the [masochistic?] spirit of deriving SQL
solutions, I, for one would like to one.

I got your example to work as well. However there are about 10 different
products and the qty needed may change. So I see I'll need another query with
the current Product qty needed. Is that what you'd do?

Hi All,

[quoted text clipped - 18 lines]

Matt

This works... not sure how useful (generic) it is.

If your table is called StockExpected...

SELECT MIN(SE1.DueDate)
FROM StockExpected AS SE1
WHERE
(SELECT SUM(Qty) FROM StockExpected AS SE2
WHERE SE2.DueDate <= SE1.DueDate
HAVING SUM(Qty) > 8)

8

;