Sum function in Access Queries

Question

I am trying to Sum up some data and make my query not so long but I don't know what I am doing wrong.

Here is an example set of data and what the total field on the query is set for:

Group By    Group By    Group By       Group By       Group By      Group By        Sum             Sum      Sum                   Where (Not Like                                                                                                                                                                                             "_FRT" and Not Like "MISG"

TransYr        Branch        Customer      Name             Inv#              Inv Date        Net Sales      Qty      Cost Value          Product Class

2015             70               CustCode     CustName     063538          12/10/2015       15.40          1          1.85                    S20

2015             70               CustCode     CustName     063538          12/10/2015       46.20          3          5.55                    S30

2015             70               CustCode     CustName     063538          12/10/2015     119.00          5        24.00                    S81

2015             70               CustCode     CustName     063538          12/10/2015     277.20        18        33.30                    SM

I added the fields to a report and tried to run it. When I do it pops up a window "Enter Parameter Value - Product Class." I have my parameter set in the query so I click OK. It then asks the same for Net Sales, Qty, and Cost Value. I'm not looking for anything other than the total for these fields, which i have marked as Sum.

The report runs but leaves the last three field blank like I didn't specify anything for them.

What am I doing wrong?

Thanks,

Jessica

Answer 1

Re-reading that above made me realize what I was missing and what I was doing wrong.

In the report, I was using the fields NetSalesValue, QtyInvoiced, and CostValue, when I should have been using SumOfNetSalesValue, SumOfQtyInvoiced, and SumOfCostValue.

"Sum(dbo_ARTRNDETAIL.NetSalesValue) AS SumOfNetSalesValue, Sum(dbo_ARTRNDETAIL.QtyInvoiced) AS SumOfQtyInvoiced, Sum(dbo_ARTRNDETAIL.CostValue) AS SumOfCostValue"

I don't do this a lot so I forget some of the little things. Then I post up here and you all either help or remind me and either way to me it is a big help and I greatly appreciate it.

The report is now displaying correctly.

Answer 2

The query works. It is a select query and if I just open it, it runs fine and gives me the data I am looking for.

It's in the report that it is not working. That is why I am confused.

Here is the SQL text:

SELECT dbo_ARTRNDETAIL.TrnYear, dbo_ARTRNDETAIL.Branch, dbo_ARTRNDETAIL.Customer, dbo_ARCUSTOMER.Name, dbo_ARTRNDETAIL.Invoice, dbo_ARTRNDETAIL.InvoiceDate, Sum(dbo_ARTRNDETAIL.NetSalesValue) AS SumOfNetSalesValue, Sum(dbo_ARTRNDETAIL.QtyInvoiced) AS SumOfQtyInvoiced, Sum(dbo_ARTRNDETAIL.CostValue) AS SumOfCostValue

FROM dbo_ARTRNDETAIL INNER JOIN dbo_ARCUSTOMER ON dbo_ARTRNDETAIL.Customer = dbo_ARCUSTOMER.Customer

WHERE (((dbo_ARTRNDETAIL.ProductClass) Not Like "_FRT" And (dbo_ARTRNDETAIL.ProductClass) Not Like "MISG"))

GROUP BY dbo_ARTRNDETAIL.TrnYear, dbo_ARTRNDETAIL.Branch, dbo_ARTRNDETAIL.Customer, dbo_ARCUSTOMER.Name, dbo_ARTRNDETAIL.Invoice, dbo_ARTRNDETAIL.InvoiceDate

HAVING (((dbo_ARTRNDETAIL.TrnYear)=2015) AND ((dbo_ARTRNDETAIL.Branch)="70"))

ORDER BY dbo_ARTRNDETAIL.Customer, dbo_ARTRNDETAIL.Invoice;

Answer 3

Whenever Access asks for a Parameter Value, it means that you have some fieldname - Product Class in this case - that it doesn't recognize. What is the Recordsource property of this Report? It should be the name of the Query you're using, and that query should include a field named [Product Class]. Does it?

As Scott says, posting the query grid doesn't work too well in the forum. Instead, select SQL from the leftmost dropdown on the query design window and choose SQL, then post the complete SQL text. The SQL is the real query - the grid is just a tool to build SQL.

Answer 4

Just copy and paste the SQL statement behind the query

Answer 5

That did not column correctly above. It looks like the Product Class header moved to the next row.

Product Class = Where (Not Like "_FRT" and Not Like "MISG"

I hope it is understandable. Please let me know if it is not or if I need to provide more information.

Thank you,

Jessica