If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Use Query to find due date
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 |
#3
|
|||
|
|||
Use Query to find due date
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 |
#4
|
|||
|
|||
Use Query to find due date
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 |
#5
|
|||
|
|||
Use Query to find due date
"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. -- |
Thread Tools | |
Display Modes | |
|
|