UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Complex Query Pulling Orders, Inventory, Stock Items Together, Access 2010    
 
   
jp62278
post Apr 19 2017, 02:47 PM
Post#1



Posts: 5
Joined: 19-September 16



Hi guys,
I'm having a bit of an issue with a complex query. Hoping someone can point me in the right direction.

I have three queries set up already - one showing all open orders (formula, width, length, delivery date, qty)
one pulling all inventory (formula, width, length, aggregate qty)
one pulling a list of inventory items that are stocked (formula, width, length, min, max, perDay usage)


What I've set up so far is a query joining stock items and inventory that shows me quantity on hand and looks 5 days out, subtracting perDay usage for each day. I'll attach a pic of this form for reference.

What I'd like to implement is subtraction of Order qty (rather than perDay usage) when there exists an order for that day. Currently, the 1 Day Out/2 Day Out fields are just subtractions based on number of days, so I know I've got a problem there with their being no date to relate to open orders. Also, I will have orders for sizes that we do not stock. In that case, I'd like to see those orders even though there won't be stock info (max, min, usage, etc.).

Hope this all makes sense. I've been pulling my hair out trying to figure this out but being an Access beginner, I think the complexity has me stumped. Any help is greatly appreciated.


Thanks!
Attached File(s)
Attached File  Capture.JPG ( 88.85K )Number of downloads: 13
 
Go to the top of the page
 
MadPiet
post Apr 19 2017, 03:57 PM
Post#2



Posts: 2,184
Joined: 27-February 09



Not sure this will help, but this article is a good read on the topic.
Go to the top of the page
 
jp62278
post Apr 20 2017, 08:46 AM
Post#3



Posts: 5
Joined: 19-September 16



Thanks for the response. I've read a good bit on that site. We have an inventory system, which I'm pulling from and refreshing every morning. Just trying to understand a rough idea on how to pull the information together to show what I'd like to see.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2017 - 02:01 PM