473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Ms Access advanced calculation query, stock level? :S

Hi :-)

I'm using Ms Access 2003 to create a DB for my school project and I need to accomplish the following:-

* Calculate balances for stock (inclusive of sales and replenishment to stock)

My table (StockDetails):

StockId (PK)
OriginalStock
AmountSold
AmtReceived


I designed a query that says:
SELECT StockDetails.StockId, StockDetails.OriginalStock, StockDetails.AmountSold, StockDetails.AmtReceived, [OriginalStock]-[AmountSold]+[AmountRcd] AS Balance
FROM StockDetails;

However, from this query I'm only able to have it function (calculate) correctly for the first entry, every other entry goes back to :-

*Calculate from the original (OriginalStock) amount e.g. if the OriginalStock is 15 and 1 item was sold then it would be 14 as the Balance BUT 15 still remains as the original stock. So the next time I try to conduct a sale of this item it will once again assume the 15 as the OriginalStock.

Thus I would like to have the Balance to be the same as the OriginalStock amount each time the level is altered either by sale or replenishment.

I've tried designing many queries but none have been successful to accomplish the desirable. Some gave the response 'Circular reference........'

Any assistance rendered to achieve the result desired would be greatly appreciated.

Thanks a mil!
Aug 11 '09 #1
4 7586
ChipR
1,287 Expert 1GB
Your database design is making this difficult for you. To track stock correctly, you should have the items in one table and the sales transactions in another, or split into two. Consider something like this:

tblStockDetails
StockId - Long (pk)
OriginalStock - Long

tblSalesTransaction
StockId - Long (pk) (fk)
SaleDate - Date (pk)
AmountSold - Long
Price(optional)

tblShipment
ShipmentNumber - Long (pk)
StockID - Long (fk)
AmtReceived - Long

Then you have a totals query for each table grouped by StockID, join those with the tblStockDetails and you get your current onhand quantities easily.
Aug 11 '09 #2
Thank a lot ChipR!

I changed the design of my DB to what you've suggested and it works well.
I've created the totals queries for the individual tables and that works well also.

However, I don't think I understand the 'join' aspect of it, because when I try to do it, I get the values duplicating for each StockId. (e.g. if I've received 10 for StockId 1 then I'm seeing that 10 for all StockIds)
Aug 11 '09 #3
ChipR
1,287 Expert 1GB
Great!
Just make sure that you have an Inner Join by having a line between the StockID in each table in your query. Or in the SQL syntax it should say INNER JOIN on ...
Aug 11 '09 #4
Thank you very much ChipR!
Appreciate.
Aug 11 '09 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Jason | last post by:
I will explain (or at least try to) first and then give an example after. I need to append a number of rows from several tables into one master table. Unfortunately there are certain columns...
1
by: Cady Steldyn | last post by:
Example: Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty ------------------------------------------------------------------ 12/09/2003 | A100 | 20 | 0 ...
2
by: Astra | last post by:
Hi All Wondered if you could help me with the below query. I have 1 simple table called STOCKCATS that consists of 2 fields. These fields are called CATID and LEVEL. The contents of this...
4
by: Michael John | last post by:
Dear Oracle Developers, my task is to make up a Oracle View from a Pivot table in MS Access. Given are two tables to join: T_FIRM: FIRM_ABBR VARCHAR2(3 BYTE), FIRM_LONG ...
13
by: royaltiger | last post by:
I am trying to copy the inventory database in Building Access Applications by John L Viescas but when i try to run the database i get an error in the orders form when i click on the allocate...
0
by: Limpor | last post by:
Hello, I am new to learning java, and i am trying to build the class for a calculation card game, unfortunately i can't get the public Card top() and Card takeTop() method in the Stock class. Can...
7
by: helraizer1 | last post by:
Hey, I have a table called TblStock with 10 columns but 3 which need the focus here. These three columns are 'Stock Level', 'Re-order Level' and 'Re-order Quantity'. The Stock level fields...
9
by: longmanm | last post by:
I realise there isn't a direct replacement of countif for excel in access but I am struggling to get aggregate functions to work for me in access 2003 queries. I am creating a stock management db,...
2
by: sazd1 | last post by:
I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: PId PDate ItemId Description Price Quantity ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.