Items in stock from multiple categories

[Nocs]

Hello guys, I am new to this forum and novice in MS Access. Maybe somebody will find this problem too simple to solve but I have tried many things and I couldn't find anything simple.



I have something similar to this below. A few categories, state of the item when adding data (added, bought, broken), items names, number of each items items.
Need to know how many of each items are in stock at any time (added - bought - broken = stock number).

I hope it won't be too hard and thanks in advance for helping me!!!

[ranman256]

you want a crosstab query.
make a base query to pull all the fields you want, then,

use the query wizard to guide you, it will ask:
what query/table to use (use the base query made above)
set ItemName as row header
set Catagory as col. headers
set PiecesOnStock as value

[aytee111]

Don't agree! This is a simple totals query. Group by category, group by item, the third field is, Pieces: SUM(added - bought - broken)

[Nocs]

Thank you for your answer. In totals query ... I can't give sum arguments (added, bought, broken). Thanks!

[Nocs]

Thank you. Using this I can get sum of items, but I need "added" to be subtracted by "sold" and "broken". Thank you for helping me.

[Nocs]

Don't agree! This is a simple totals query. Group by category, group by item, the third field is, Pieces: SUM(added - bought - broken)

Thank you for your answer. In totals query ... I can't give sum arguments (added, bought, broken). Thanks!

[Nocs]

you want a crosstab query.
make a base query to pull all the fields you want, then,

use the query wizard to guide you, it will ask:
what query/table to use (use the base query made above)
set ItemName as row header
set Catagory as col. headers
set PiecesOnStock as value

Thank you. Using this I can get sum of items, but I need "added" to be subtracted by "sold" and "broken". Thank you for helping me.

[ArviLaanemets]

SELECT a.Category, a.[Item Name], SUM(Iif(a.State = "added",1,-1)*a.Pieces) AS [OnStock] FROM YourTable a GROUP BY a.Category, a.[Item Name]

[aytee111]

The way your picture looks, all those are numeric fields, they can be added and subtracted just like any other number. My post described it: ([added] - [broken] - [sold]) - or whatever your formula is.

[Nocs]

Hello everybody again. I can't manage to solve this problem I had.
Here is the exact database that I need. It is some kind of inventory. It is attached.
The main table “entry” is a collection of one entry which should include:
-OrganisationUnit (there are 5 units) ,
-InventoryType (can be of type: added, sold, lost),
-Color (of item, there are 9 types),
-StartingNo (of serial numbers array),
-EndingNo (of serial numbers array)
-CalculatedField (calculating difference between starting and ending array number).


example of item that is populating table Entry:
OrgUnit | InvType | Color | StartingNo | EndingNo |
-----------------------------------------------------------------------------------------------
Krivaja | lost | F-bijela | 58 | 65 |


What I need is to know how many of each “Color” I have on stock, in each OrganisationUnit.
“Color” on stock number should be calculated by “InventoryType” of “Color”, added - sold - lost.


example...


OrgUnit | Color | Sum of Added | Sum of Sold | Sum of Lost | In stock (Added - Sold - Lost)
--------------------------------------------------------------------------------------------------------------------------------------
Krivaja | F-bijela | 55 | 20 | 5 | 30


I am trying to solve this problem for a while, searched all over the internet but I can’t find a solution. Tried with solution you have posted but with no luck.
I am really sorry guys because I am taking your time, but I really need solution written in db so I can understand how to solve it. Probably you had such problems on the begging of your access journey
Thanks a lot in advance!!!