How to calculate fields with restrictions

[Jrmrosebud]

Here is an explanation of my database and what I am trying to accomplish. The Question is at the bottom. Any help you can find would be appreciated. Thank you for looking into it!
The tables and their fields I have in the database are as follows…
EmployeeT
EmployeeID – Primary Key – Number (not an autonumber because they are county assigned)
LastName – Text
FirstName – Text
EmailAddress – Text
JobTitle – Text
MobilePhoneNumber – Text
Notes – Memo

ItemT
Item – Primary Key – Text
Description – Text
Category – Text
CostPerItem – Text
Supplier – Text
SupplierItemNumber – Text
ESBuildingQty – Number
D3Qty – Number
Stock Total – Calculated – [StockAmountESBuilding] + [StockAmountD3]
Comments – Memo

StockLocationT
StockLocation – Primary Key – Text

UsageLocationT
UsageLocation – Primary Key – Text

TransactionTypeT
TransactionType – Primary Key – Text
AddRemoveESBuilding – Text
AddRemoveD3 – Text

TransactionT
TransactionID – Primary Key – Autonumber
Item – Text
TransactionType – Text
Quantity – Number
StockLocation – Text
UsageLocation – Text
DateTime – Date/Time
EmployeeID – Number
Notes – Memo

All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either “ES Building” or “D3” when inputting the data).

For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.

How would I accomplish this?

Thank you.

 

[MarkK]

Generally a quantity of stock is calculated, not stored. Think of a bank balance, which is, in fact, a calculation as follows...

balance = deposits - withdrawals

... so all you do is record the deposits and withdrawals. Also, think about someone's age. Where do you store that? It changes with every passing second.

So in some cases, numbers are not stored, they are calculated from other numbers as their most simple definition.

Hope this helps,

 

[Jrmrosebud]

Hi,

Thank you for your response.

I have realized that the ESBuildingQty and D3Qty fields should be calculated. However, that calculation keeps getting denied by access because it involves another table (TransactionT).

How would I accomplish that calculation and also include the specific criteria that I need.

i.e. I need it to match the item from TransactionT to the Item in ItemT and then subtract the Quantity listed in TransactionT from D3Qty if the StockLocation in TransactionT = "D3" and if the TransactionType = either "Adjustment" or "Usage".

I put this into the expression builder:

IIF([TransactionT]![StockLocation] = “D3” AND ([TransactionT]![TransactionType] = “Adjustment” or “Usage”), [StockLocQtyQ]![D3QtyCalc]-[TransactionT]![Quantity], [StockLocQtyQ]![D3QtyCalc])

I get this error message when I attempt to save the table:

The expression cannot be saved because it refers to another table

Thanks

 

[MarkK]

You'll want to learn how to join tables with foreign keys linking to your primary keys. Then you can write queries that are very fast that use data from related tables. SQL syntax might be . . .

SELECT s.D3QtyCalc-t.Quantity As Result, s.D3QtyCalc
FROM TransactionT As t INNER JOIN StockLocQtyQ As s ON s.ForeignKey = t.PrimaryKey

 

[Jrmrosebud]

Thank you so much. I will work on that and try it.

 

[jdraw]

Markk's description of Stockcalculation is spot on.
I would also recommend that you include the "AgreedUponPrice" (or whatever you choose to name it) in the TransactionT. By storing the "sellingPrice/AgreedUponPrice" you do not run into issues where you use the Price in the Item table to calculate Price. If you use only the Price in the Item table, you have difficulties (inconsistencies) in your existing Orders when you decide to change Item price.
By using the AgreedUponPrice, you can allow for LoyaltyPrograms, Clearance/Sales, and other "special" prices.
If you do decide to go with only the Item Price, then you will have to include an effectiveDate concept.

Good luck with your project.

 

[Jrmrosebud]

Jdraw, I'm assuming that you are including that as if we are selling items? The cost in Item is how much we purchase the item for. We don't sell items after we purchase them, they are moved between two stock locations and distributed from either stock location to one of 12 sites to be used. The cost is included in itemt so that we can get an estimate of how much we are spending on each item per year or per site (I plan to create reports for this). Would you still include a second field referring to an aggreeduponprice? Thank you for your input.

 

[CJ_London]

However, that calculation keeps getting denied by access because it involves another table (TransactionT).

that sounds like your calculation is in a table - which is not want Mark is saying - it should be in a query or form or report

 

[jdraw]

I was assuming your transactions were for Sales.
But it may still be an issue if you do any analysis of purchases/suppliers etc.

Also, for clarity, your original post described how you have set up your tables. It did not explain your database - its purpose and the business it is intended to support. A description of the business and facts typically identifies what your database should support.

Good luck.

 

[Jrmrosebud]

Jdraw, I appologize for any lack of clarity. We are an Emergency Medical Service and we only purchase supplies for use. There are two locations where the stock is stored. It initially comes into one location (ES Building) and some of it is then moved to the second location (D3). It is then distributed to one of 12 other locations for use. I am attempting to track the stock as it comes in to the ES building and is then moved to D3 and/or sent to the other locations for use. I need to be able to calculate ESBuildingQty and D3Qty from ItemT based on that data that is being input into Quantity, TransactionType, and StockLocation from TransactionT. Is that a better explanation?

 

[jdraw]

Perhaps.
As I understand your business from your posts, somebody somewhere, independent of you and your group,requests supplies. Then somebody, again independent of you/your group, orders said supplies from some vendor or vendors. Magically, supplies arrive at the ES Building. But since you haven't shown any link between you/your group and finance or procurement, there really seems to be no check as to what was ordered was received. Or if the received goods/supplies match any order. Back orders and/or partial shipments or substitutions seem to be extraneous to your part of the business.
Who determines what supplies should be sent to location D3, or any of the remaining 12 locations?
All I'm really saying is that your business, or your description of the business, runs in "silo mode", and your database is furthering that approach. Perhaps you just haven't stepped back to see the "holistic business process(es). I'm sure you keep and can share and analyze your emergency records. If that is true, why not do the same with supplies -- from identification of need, to various sources, to alternatives etc.

I just don't think you looking at the business needs. You seem focused on only a small part, and that in itself may be all you need. But what does the business need? What is the issue you are trying to solve? Does inventory and movement operate totally independent of procurement and finance?
This type of observation is meant to help you design a database that meets your business needs; and to ensure you have done the analysis/digging to get all the facts. Once you have all the facts, you can discard those that are outside your scope, but at least you considered them. You haven't mentioned scope or fit with other groups.

Good luck.

 

[Jrmrosebud]

Jdraw,

All locations make up the system. They are all geographically close to each other. A crew from one of the 12 stations requests the supplies that they need because their supplies need to be restocked. The Lt then pulls the supplies from either stock location. They simply attempt to keep a stock at each location. At this point there is a very poor system in excel that is being used to track what supplies are where and how much is being sent to the stations for use.

In total, here is what I would ultimately like for the database to accomplish...

1) Track the inventory that is purchased
2) Maintain an up to date list of the inventory that is in stock at the ES Building
3) Maintain an up to date list of the inventory that is in stock at D3
4) Track what inventory goes out to which units/stations
5) Track what is being spent per item (based on unit price x quantity used)
6) Track what is being spent per station (again unit price x quantity used)
7) Calculate total volume of each item on hand
8) Calculate total used quantity
9) Track the volume of broken/expired inventory
10) Calculate the total cost of broken/expired stock

As for the emergency reports...they are kept and tracked on a State level that is higher than the system level that I work for. I also have not been tasked with tracking the purchasing, although I can see how this database would help with that in an indirect fashion. Also, the ES Building is where the main inventory is held and the inventory at D3 is a smaller inventory. There is about 15 miles between the two inventory piles so if it is not located at D3 it is not difficult to acquire what is needed from the ES Building.

Thanks,

Jackie