Counting stock used? From sales data

[bbdsbd]

Hi

I am new to this Forum and to Access, But have a fair bit of excel knowledge.
I am currently moving away from Excel and wanting similar results in Access.

I am creating a stock sold count and got stuck already.

On a basic Level I have…

“PRODUCT” table with the Header “CODE”


=Each Row is product info by code

“SALES” table with “DATE” & “PRODUCT CODE”
=Each Row is a dated Sale with Product code.


What I am trying to see…

I want to be able to see TOTAL of each product sold at this stage (all dates).

I don’t think you can calculate this on the product table as simple as creating a USED column as a calculated field.
But in my Language (not access / SQL) I think I would do something like…

“Count occurrence (PRODUCTS.CODE) in (SALES.PRODUCT CODE)”

So just seeking some advice to which way to go about this.

I have Google'd and searched, but kept hitting brick walls, or felt there was an easier solution than the 5 queries linking back and forth some web pages explained.

Thank you
Ste

[CJ_London]

depends on the context but in Access it would be

SELECT [Product Code], count(*) AS Occurrences 
FROM SALES
GROUP BY [Product Code]

which will provide the data for all products sold

or if you wanted to work it off the product table so you could see 0 sales for products not sold

SELECT Code, T.Occurrences
FROM PRODUCT LEFT JOIN
    (SELECT [Product Code], count(*) AS Occurrences 
     FROM SALES
     GROUP BY [Product Code]) AS T
ON Product.Code=T.[Product Code]

[bbdsbd]

Thanks Ajax...But I am going to sound stupid here...
Where do I put this code?

[CJ_London]

It is a complete query - click on Create>Query Design

Then close the showtable widnow as it is not required

Then click on the SQL view (top left) and past the code

You will need to change the names of fields and tables to what you are actually using

I don’t think you can calculate this on the product table as simple as creating a USED column as a calculated field

You can't. Calculated fields are generally very limited (data changes may not be reflected in the calculation and values cannot be indexed for example), and anyway they cannot reference data in another table

[orange]

@bbdsbd,
For consideration and reference you should review this article by Allen Browne related to Stock counting and Stock on Hand.