Stock level (1 Viewer)

Hi guys, can someone help me with this simple calculation in access? For our task at uni we need to make a query, however we didn't get much info about it, it's almost self-studing

I just need to make a simple calculation where I can see how much stock there is.

As a company I sell 10 different items. I have a table with all the sales on different dates.

Here is the problem I have:
I bought 100 pieces of Item1 and only sold 20pieces. So I have 80 pieces left.
Later I sold 40 pieces of Item1, so there is only 40 left.
When I put this into a query, I take the amount of goods I purchased + the amount of good I sold (from the tabel of al the sales). When I run the query, I do not see 40 as a result. I see two lines:
Item 1 purchased 100 sold 20 stock 80
Item 1 purchased 100 sold 40 stock 60
How can I put this into one line so I can see 40 as a result?

Welcome to AWF

Create an aggregate query by clicking the Totals button in the ribbon
Add the fields you need to the query
Each will say Group By in the Totals row.
Use the dropdown to change the Sold field from Group By to Sum

Thank you isladogs! I have a feeling I'm almost there!!

Here is another problem I get when I do what you told me
- So I have a list of the purchases I made (I purchased for the ten items I sell 100 pieces)
- I also have a list of the sales --> I sold Item1 two times (first time I sold 20 pieces, then I sold 40 pieces)
--> problem: when I do what you told me and make a query where I can see the amount I bought, the amount I sold and the difference (=stock level) I see this:

Item1 - 100pieces bought - 60pieces sold - stock 140
Item 3 ..........
item 4 ............

Problem 1: I don't see item2 because I didn't sell anything of it --> I want to see 100 stock
Problem 2: Item1 has now 140 stock instead of 40


It would be great if you can help me with this, if it's too complex, It's maybe better for me to leave the stock calculation beside

1. Fix the issue for items with no sales using the Nz function
Something like Nz([ItemsSold],0)
This converts null values to zero so the calculation should then work.

2. Sounds like the stock values of 100 has been added for both records.
If I'm correct it has calculated 100-20+100-40=140!

If you can't see how to fix it, open your query in design view then change to SQL view. Copy he code and paste it in your next reply together with some sample data.