Use Query to find due date

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

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.accessmonster.com

mattc66 via AccessMonster.com wrote:
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
;


--
Smartin

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?

Smartin wrote:
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
;


--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200701/1

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.

mattc66 via AccessMonster.com wrote:
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?

Smartin wrote:
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

"mattc66 via AccessMonster.com" wrote:
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?

I'm not sure how relevant these are but worth a look:

FIFO and LIFO
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko32
http://www.dbazine.com/ofinterest/oi-articles/celko33

If the articles are relevant, you may be interested to know I translated the
Standard SQL into Jet SQL (well, nearly all; excuse my hissy fit about Jet's
UPDATE syntax g) he

http://groups.google.com/group/micro...758c539bfd4edc

Jamie.

--