Current stock and Calculating cost queries

N

nicole.skeeters

Registered User.
Local time
Today, 14:14
Joined
Aug 16, 2013
Messages
18
Please see this thread in regards to the tables etc I've got so far: (I cant link yet) Theory and Practice - "Database Dummy - design help?"

According to plog's suggestion, I've written a query on my tbl_transactions to calculate the current stock field. I'm not sure that I've done my expression correctly: "Current Stock: [transactiontypes].[transaction type value]*[transactions].[quantity]."

This gives me a negative value, which obviously is not correct. Is it possible that this needs to be written another way? As in, if the transaction type is 3 (which is -1 for allocation), then [transaction type value]+[quantity]. I'm just not sure if I can write this all in the same expression or if it needs to build off the expression inside another expression.

My next question is about calculating the transaction's cost. Plog had suggested that the item costs be held in another table due to the possibility of them changing, and I assumed from there that transaction cost too should be a calculated field and not a stored field. Therefore, I have a query set up as:

SELECT Transactions.[Transaction ID], Transactions.[Item ID], ItemCost.[Item Cost]
FROM Transactions LEFT JOIN ItemCost ON Transactions.[Item Cost ID] = ItemCost.ID
WHERE (((Transactions.[Transaction ID])=3));

This pulls the one transaction I have in there (trans value =3) but it will not pull the cost. :banghead:

So, I know this is two different query issues, and I can separate them into different posts if need be, but I appreciate any help anyone feels like throwing out there!

Thx
Nicole
 
Show some sample data from both tables + table structure or even better post a sample database.
 
Current Stock: [transactiontypes].[transaction type value]*[transactions].[quantity]
Click to expand...
I recall Plogs post - quantity will be a positive value and transactiontype value will be a-1 if you are issuing stock and +1 if receiveing stock - so it sounds like you have one transaction with is a issue of stock.

In your query designer, ensure the epsilon is clicked (totals) and in the total row, select sum for your current stock column. This will the add up all your receipts and issues to give you a current stock. The other columns will be set to group by and will consist only of fields that can be grouped - such as stock code and stock type. If you include a date for example the grouping will then be by the date values.

Also strongly recommend you do not use spaces in your field or table names - it will give many problems when something does not work as expected so better to get into the habit of no spaces now.
 
I've enclosed my database to date. I will be adding in some transactions this morning. Feel free to tear it apart.
 

Attachments

Quick question: why do you have the item cost in a separate table from the transactions?
 
I'd love them to be together, but it was suggested that they be in a separate table. If there's no wrong with it being in my transaction table, I'd much prefer it be there.
 
Nicole

Do you have a formula that you wish to use to calculate the item cost.

Namely if you buy bulk for an item that you will know will increase in price over a period of time do you wish to use the current available cost or the cost you actually paid. Bear in mind you cost may be a small fraction of the current cost and in fact you might be able to sell back cheaper than your supplier can supply. Complicated?

What about holding costs? Interest Paid, Warehousing etc. Do any of these affect your cost price?

Knowing this would affect the design of your tables and which ones you need.

Also I don't have anything past Access 2003 so I cannot open your sample. If you could convert and repost it would help.

Storing calculated values is not a firm rule. In fact at times it is nothing short of ridiculous.

Hope this helps a little. If you search Allen Browne he has some good advice on this subject on his web page. Allen is a respected MVP member.
 
I can see why you would store a cost against a transaction rather than against the item in the inventory - as Rain said, the costs may change over time... what I don't see is why you've been 'told' to put it in a separate table... but maybe that's my own lack of knowledge showing through ;)


but.. what's the link between the transaction and the item cost? You have 2 ID fields in your item cost table... which one should be linking to the transaction?
 
CazB said:
I can see why you would store a cost against a transaction rather than against the item in the inventory - as Rain said, the costs may change over time... what I don't see is why you've been 'told' to put it in a separate table... but maybe that's my own lack of knowledge showing through ;)


but.. what's the link between the transaction and the item cost? You have 2 ID fields in your item cost table... which one should be linking to the transaction?
Click to expand...

A separate Table would allow you to record Many different values. I can't say much more as I am unsure of what the owner wants to do.

Just to clarify my statement. Sometimes we calculate values while others you would store. The problem is knowing what to do when.

Imagine your bank calculating your balance after 20 years as a client. Or a large grocery store that sells a million bottles of milk every day. How would you calculate that.
 
RainLover said:
Nicole

Do you have a formula that you wish to use to calculate the item cost.
Click to expand...

This is meant to be a pretty straightforward, simple database. Its to manage the stock of supplies and track departmental requests, basically. There will be no bulk - we're talking giving employees pens and scissors etc. The current formula that I was trying to use was item cost * item quantity input in the transaction.

CazB said:
but.. what's the link between the transaction and the item cost? You have 2 ID fields in your item cost table... which one should be linking to the transaction?
Click to expand...

Well, I did have a one to many relationship set up between [tbltransactions].[item cost] and [tblitem cost].[id1] but that doesnt make any sense quite frankly so I deleted it. In tblitem cost, ID = autonumber and ID1 is the tblinventory pk.
 
You need a simple table structure like the attached. Note the left join between tblTransactions and tblDepartments. I've also attached images of the queries

The query to see current stock would be like this

Code: 
SELECT tblItems.ItemID, tblItems.ItemDesciption, Sum([Quantity]*[factor]) AS CurrentStock
FROM tblItems INNER JOIN (tblTransactionTypes INNER JOIN tblTransactions ON tblTransactionTypes.TransactionTypeID = tblTransactions.TransactionTypeID) ON tblItems.ItemID = tblTransactions.ItemID
GROUP BY tblItems.ItemID, tblItems.ItemDesciption

and a query to value transactions would be like this

Code: 
SELECT TblDepartments.DepartmentID, TblDepartments.DepartmentName, tblItems.ItemID, tblItems.ItemDesciption, tblTransactions.TransactionDate, tblTransactionTypes.TransactionDescription, [Quantity]*[factor]*[ItemCost] AS TransactionValue
FROM TblDepartments RIGHT JOIN (tblItems INNER JOIN (tblTransactionTypes INNER JOIN tblTransactions ON tblTransactionTypes.TransactionTypeID = tblTransactions.TransactionTypeID) ON tblItems.ItemID = tblTransactions.ItemID) ON TblDepartments.DepartmentID = tblTransactions.DepartmentID

Your transactions types would have something like the following values

tblTransactionTypesTransactionTypeIDTransactionDescriptionFactor1Stock Received from supplier12Issue to Department-13Returns from Department14Stock Returned to Supplier-1
 

Attachments

  • ScreenHunter_02 Aug. 27 19.12.jpg
    ScreenHunter_02 Aug. 27 19.12.jpg
    27.2 KB · Views: 116
  • ScreenHunter_03 Aug. 27 19.15.jpg
    ScreenHunter_03 Aug. 27 19.15.jpg
    38 KB · Views: 103
  • ScreenHunter_08 Aug. 27 19.24.jpg
    ScreenHunter_08 Aug. 27 19.24.jpg
    63.9 KB · Views: 113
The attached sample database is only missing the current cost.

First you need to decide if this is suitable for you.
 

Attachments

CJ: What is "[factor]" supposed to represent?

Rain: Thank you for the sample database. I'm not quite sure how to get my data to fit into your model.
 
see the screenshots I sent - it is the name of a field in tblTransactionTypes
 
Wow completely overlooked that. Derp. So you're replacing what I have as "transaction type value" (1 or -1) as factor?
 
Yup:) I also removed spaces from names to encourage good practice:D
 
Also, just realised the contents of the table did not come out as expected - should be this

Code: 
[B][U]tblTransactionTypes[/U][/B]
TransactionTypeID    TransactionDescription        Factor
1                    Stock Received from supplier     1
2                    Issue to Department             -1
3                    Returns from Department          1
4                    Stock Returned to Supplier      -1
 
Ah that makes SO much more sense - I tried deciphering that table to no avail. Before you answered, I just switched "factor" to "transaction value" and got it to work! :D
 
Nicole

Now that you have received all this advice, have you designed all the Tables and their Relationships.

If you have it would be a good idea to post a pic of the Relationships Window so that it can be checked for correctness.
 
You must log in or register to reply here.

Similar threads

C
Aggregate query loses aggregate
Replies
7
Views
205
ClaraBarton
C
R
Current Inventory Calculations
Replies
6
Views
468
tvanstiphout
T
T
Creating stock balance by month
Replies
5
Views
463
mike60smart
mike60smart
A
Query Does Not Work
Replies
18
Views
640
Pat Hartman
P
J
Solved Pulling data from three tables into multiple queries.
Replies
3
Views
313
Jomat
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom