Sum specific field where records have the same product ID

[mattshank]

I'm new to Access and SQL queries in general. I have a table of job tickets with fields for Job#, Product#, and number of products sold. I want to create a monthly stock summary. So I want to create a report that lists ever product sold and the total number of each product sold. Right now, it pulls each record from the Job tickets table and, as a result, displays records with the same product number multiple times. I want to add up the number sold of records that have the same product number.



My thinking is that I could make a query that selects records from the Job tickets table with distinct product numbers. Then loop through each product number in that recordset and run a query for each product number to add up the total number sold, but that seems like a lot of querying to me. Is there another way to do this?

[burrina]

Here are a few examples: I assume Product Number is text, if not then use whatever product number you want in your query.

Products Sold > 0
Product Number "Apples"

However; if you want all of this in a Report, you need to use a different approach and Group By for each Product, etc...
Not really sure of your db structure, you should have a separate table for products.

With more info, maybe we can advise further.

HTH

[mattshank]

I do have a separate table for products and product number is a string. But your example will only find me the number of records for one product. And it won't add the number sold for each record that has "Apple" as the product number.

I have a table that has all the products from certain jobs with fields (JobID, ProductNo, NumberSold), where JobID and ProductNo are the primary key. What I want is a report that disregards JobNo, lists all the products sold only once, and shows the total number of each product sold, regardless of whether they were sold in different jobs (JobID).

[burrina]

So, does your query show the desired results? If not, can you post a screenshot of the query? If it does return the desired results, then create a report based on that query and group by productNo. NumberSold <2

HTH

[mattshank]

I am slightly confused. As my query currently is, it shows the ProductNo. and the the NumberSold, but it shows duplicate ProductNo.s, while I want it to add up the NumberSold values.


This picture shows what my query churns out. As you can see, it shows two records with the same ProductNo. I'd like there to be only one record that has a total NumberSold of 23. And I want the query to work with infinite duplicate ProductNo.s.

[mattshank]

I think I figured it out. I grouped by ProductNo. by clicking the "Totals" button the Query Design toolbar. Then in the "Total:" row under NumberSold in Design View, I selected "Sum". It seems to do what I want it to do.

This is the SQL view of the query I believe is doing what I want to do:

SELECT tblJobStockItem.CatDeptItemNo, Sum(tblJobStockItem.NumberSold) AS SumOfNumberSold, tblMstrStockList.ValuePerItem, tblMstrStockList.ItemMultiplier
FROM tblMstrStockList INNER JOIN (tblJob INNER JOIN tblJobStockItem ON tblJob.JobNo = tblJobStockItem.JobNo) ON tblMstrStockList.CatDeptItemNo = tblJobStockItem.CatDeptItemNo
GROUP BY tblJobStockItem.CatDeptItemNo, tblMstrStockList.ValuePerItem, tblMstrStockList.ItemMultiplier, tblJob.DateCreated
ORDER BY tblJobStockItem.CatDeptItemNo;