Will be sooooo simple for you guys!

[rorgan]

Hi all

I am capable of building a very simple database, however, SQL's, Expressions, etc., are a different language. Please, please can somebody help me with, what I am sure is extremely simple to most.

In simple terms - I have I have a stock database. In one table I have the stock level and in another I have the orders. How do I run a query that will subtract the orders from the stock level.

I thought it would be something simple like: Sum[Parts].[StockLevel]-[Orders].[OrderQty]? What am I doing wrong. I am going into expression builder and double clicking the fields but nothing! Am I putting it in the wrong place? Aaarrrgggghhhh! So sorry for being stupid. I am self-taught on the basic stuff and just cant seem to find the help out there that shows in a step-by-step format.#
Thank you xxx
Rachel xxxx

 

[plog]

In one table I have the stock level and in another I have the orders. How do I run a query that will subtract the orders from the stock level.

You don't. Values that can be calculated from the data you already are storing, shouldn't be stored themselves. Instead, when you need an inventory level, you calculate it.

The only table you should need to get inventory level is your orders table. Actually, a better name for it would be 'transactions' so that you can also store additions to inventory, not just subtractions. Then when you need to know how much you have, you run a simple Totals query:


SELECT PartNum, SUM(OrderQty) AS InventoryLevel FROM Orders GROUP BY PartNum;

That query will then return the levels of all your parts.

 

[rorgan]

You don't. Values that can be calculated from the data you already are storing, shouldn't be stored themselves. Instead, when you need an inventory level, you calculate it.

The only table you should need to get inventory level is your orders table. Actually, a better name for it would be 'transactions' so that you can also store additions to inventory, not just subtractions. Then when you need to know how much you have, you run a simple Totals query:


SELECT PartNum, SUM(OrderQty) AS InventoryLevel FROM Orders GROUP BY PartNum;

That query will then return the levels of all your parts.

Oh God - I knew this would happen! I have no idea what you just said. So, I make a table called Transactions. I then put in a positive or negative amount (obviously starting with a positive). Then any orders from there will be listed. Sooooo - If the first item is a transaction of +500 and I then do one for 50 I would need to run the query based on that? I know I am a dimwit, but please can you type the exact formula/expression so that I can get it right and try it out?

I really do appreciate it and know that you clever bods must hate geek-wannabes like me! :-(

 

[plog]

You're on the right track. Here's a link to help you with totals query:

https://support.office.com/en-ca/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8

The short version is this: Take your Transactions table, bring in the field that identifies which product it is and also the field which tells you how many. Click the Sigma (aka Summation) symbol in the ribbon. Then change the 'Group By' that appears underneath your quantity field to 'Sum'. Run that query and you will have your totals.

 

[jdraw]

See this from Allen Browne for a broader picture.

The general expression for your current stock on hand is

Stock on Hand = Sum(Stock Incoming) - Sum(Stock Outgoing)

 

[rorgan]

You guys are AMAZING! Thank you so much!