Trying to calculate on query to pull a report

[heart01]

Hello All, I have been trying to work this out myself based on a response that I received yesterday which worked however I think I am lost in the process. I have a form which I have 6 calculated fields plus information fields like date, type etc. However, I now am gravitating to using the Query for these fields to calculate instead of calculating on the form. So simply the fields below are what I have calculated on the form;

Form Field 1. Name GST =[cost per unit]*[Qty]*0.1
Form Field 2. Name GST Per Item = [GST]/[Qty]
Form Field 3. Name Freight Cost per item =[freight cost]/[Total GST incl]
Form Field 4. Name Total cost order bf GST =[cost per unit]*[qty]
Form Field 5. Name Total GST incl = [total cost oder bf GST&freight]+[GST]
Form Field 6. Name Total Freight+GST =[freight cost] + [Total GST incl]

Now these work on the form but not on the query. modelled from an answer i received yesterday for another query

SELECT Products.Type, Sum([Qty]-[Qty Out]AS EXPr1
FROM Products
GROUP BY Products.Type;

I tried replicating the concept in the Sql section of the query using the above criteria 1-6 however of course I am receiving all sorts of error messages.

So my mission is to be able to have all 6 calculations and other categories working on the query so that I can pull a report on ALL fields. If someone could give me a head start I would greatly appreciate

[jimatqsi]

Heart01,
It sounds like you are being a little too ambitious with jumping in the editing the sql directly. Not too ambitious, but too quick. Take it slow, it took me a very long time to reach the point where I could code the sql commands without botching the job.

Let Access code the sql for you ... take that sql command you typed above and look at it in the Access query editor. You'll see there how you can accomplish the same thing without typing all that sql code. You'll see one column looks like this: Expr1:[Qty]-[QtyOut].

You'll also see the Grouping row is visible, and this query is grouped by the "Type" column. You turn that grouping on and off by right-clicking in the bottom half of the query editor; that's where you can select Sums, Averages, Min, Max and other options that come with Grouping.

Go back and forth between the SQL code view and the GUI Query editor (by right-clicking in the top half of the query editor). Make a change in one place and go see how that changed the appearance in the other. You'll learn a lot about SQL like that.

I think that you'll see that adding all 6 of those calculations to your query will really be a breeze. Not at first, but soon. Try it.

Jim

[heart01]

Hi Jim, I understand that I am in the thick of it and jumpig in at the deep end..I have been doing what you suggested now for the better part of 6 hours and I am clearly not understanding what Im doing. It appears info is just now floating around in my head. I have in the SQL of the query the following;

SELECT Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Cost Per Unit], Products.QTY, Sum([Cost per unit]*[Qty]) AS [Total Cost of Order before GST and Freight], Sum([Cost Per Unit]*[Qty]*0.1) AS GST, Sum([GST]/[Qty]) AS [gst per item], Sum([Total Cost of Order before GST and Freight]+[GST]) AS [TOTAL GST incl], Products.[Freight Cost], Sum([Freight Cost]/[Total GST Incl]) AS [Freight Cost Per Item], Sum([Freight Cost]+[Total GST incl]) AS [GRAND TOTAL Freight + GST incl], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost], Sum([Qty]-[Qty Out]) AS [Remaining Stock]
FROM Products
GROUP BY Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Cost Per Unit], Products.QTY, Products.[Freight Cost], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost];

I dont know if this makes any sense and is quite a lot of information (set up in the Query design) I apologise but if it makes any sense to yourself or anyone on where it is I am going wrong I would be greatful for help in putting it right

kind regards
deb

[MMcCarthy]

Hi Deb

Aggregate functions can get complicated, I'll have to put some rules together one of these days for an Insight when I get the time.

There are a few basic rules that you have to keep in mind.

• Any field in the SELECT that is not aggregated (summed, counted, etc.) must be included in the Group By clause.
• All aggregated fields will grouped by all non aggregated fields. By this I mean if I run the following query ..
  
  

  SELECT SalaryScale, Department, Count(EmpName)
  FROM EmpTable
  GROUP BY SalaryScale, Department

  This will not give me the number of employees on a particular salary scale and the number of employees in each department. What it will give me is the number of employees on a particular salary scale in each department.
• Use of the HAVING clause can be a difficult concept to get but it's similar to the WHERE clause except it works on the grouped by fields rather than the SELECT fields.

Now looking at your query code you seem to have added all the fields from the SELECT that are not aggregated to the GROUP BY clause so that's good. However, my suspicion is that you have too many fields displaying. If you run a query ...

SELECT Products.ID, Products.Type, Sum(Products.Qty)
FROM Products
GROUP BY Products.ID, Products.Type

It will just return the quantity for each product id regardless of type. You need to remove product id from the select and group by to get the qty per type. Does this make sense.

You said you were getting errors but I don't see anything obvious to cause the sql not to actually run. I suspect your problems are with the various aggregates. My suggestion would be to use a test query to check each of them out individually and see if they throw an error. It can be a lot easier to manage than a large query like you have.

[jimatqsi]

Deb,
Anybody who spent 6 hours trying to work through it themselves needs and deserves some extra help :)

Mary is right, work through some small tests, try to get your query right without any of the Summed fields and then add 1 field at a time. By switching the view back and forth between Design View and SQL View you will learn a ton about how the SQL code is done.

All that aside, I have something concrete for you. You have mistake in your formatting of a name you chose for one of your Sums.
Sum([Freight Cost]+[Total GST incl]) AS [GRAND TOTAL [Freight + GST incl] is an illegal field name. You cannot have "+" in the name you are giving that sum. My personal habit is to avoid long names and avoid including spaces in the name. It just gets me into trouble eventually. If I want a space in a name for readability, I use an underline. Otherwise I avoid special characters completely.

Jim

[jimatqsi]

To be more clear, in your code:
AS [GRAND TOTAL Freight + GST incl]
should be
AS [GRAND TOTAL Freight plus GST incl]
or in my view
AS [GTFandGST]

[MMcCarthy]

@jimatqsi
Good catch Jim. I missed that one :)

[heart01]

Hello again, Im starting to feel really dumb and I think my hubby is going to throw me out. I have taken all your suggestions above and have succeded in having the calculations show on the report from the query all but two. I am receving the error message Subqueries cannot be used in the expression <expression>. (Error 3203) I think im starting to understand what this is about but I have now been flipping about the search to no avail I have been trying various ways to sort this issue out...

the SQL that I have in the query is as follows;
PS( I apologies for the huge amount of info)

SELECT 
Products.[Date Recvd], 
Products.[Invoice NO], 
Products.Suppliers, 
Products.[Product Name], 
Products.Type, Products.Size, 
Products.[Load Index], 
Products.[Cost Per Unit], 
Products.QTY, 
Sum([Cost per unit]*[Qty]) AS [Total Cost of Order before GST and Freight], 
Sum([Cost Per Unit]*[Qty]*0.1) AS GST, ([GST]/[Qty]) AS [gst per item], 
 Sum([Total Cost of Order before Freight]+[GST]+[Freight Cost]) AS [TOTAL GST incl], 
Products.[Freight Cost], 
Sum([Freight Cost]/[QTY]) AS [Freight Cost Per Item], 
 Sum([Freight Cost]+[Total GST incl]) AS [GRAND TOTAL Freight plus GST incl],
Products.[Freight Company], 
Products.Comments, 
Products.[Date Outgoing], 
Products.[Type Out], 
Products.[Qty Out], Products.Freight, 
Products.[Freight Send Cost], 
Sum([Qty]-[Qty Out]) AS [Remaining Stock]
 
FROM Products
GROUP BY
Products.[Date Recvd],
 Products.[Invoice NO], 
Products.Suppliers, 
Products.[Product Name], 
Products.Type, 
Products.Size, 
Products.[Load Index], 
Products.[Cost Per Unit], 
Products.QTY, 
Products.[Freight Cost], 
Products.[Freight Company], 
Products.Comments, 
Products.[Date Outgoing], 
Products.[Type Out], 
Products.[Qty Out], 
Products.Freight, 
Products.[Freight Send Cost], 
Products.[Freight Cost Per Item];

The two pieces of info I have bolded are what I am getting the error messages on. If you would be so kind as to advise me if I am on the right track I would appreciate...
kind regards
Deb

[MMcCarthy]

OK You've done really well, just one more rule to learn. You can't use an alias name for a field in a calculation.

So ...

Sum([Total Cost of Order before Freight]+[GST]+[Freight Cost]) AS [TOTAL GST incl], 

becomes

Sum(([Cost per unit]*[Qty])+[GST]+[Freight Cost]) AS [TOTAL GST incl], 

the second one gets even more compicated :)

One suggestion I would make would be to build one query with the Total Cost of Order before Freight and the Total GST incl calculated. Then build another query using that query as the base instead of the product table.

[jimatqsi]

Deb,
I've been programming for 30+ years and I still get that "dumb" feeling. There's always some new language, hardware, database, platform of some kind or a combination of all of the above. Winsock is kickin' my butt right now.

You just have to build an immunity to it, it's a mirage. You're not dumb.

Jim

[heart01]

Oh by George I think she's got it.....See me doing a little dance...Where do I send the flowers and chocolates?...thank you so much for all your support and patience...I really appreciate your sharing your knowledge and I hope that I am able to also do this one day...I made a new query and used the second code as suggested from msquared as my grand totals...again thank you thank you thank you...mwahhh

[MMcCarthy]

OK if you run and save this query as qryFreight1 ...

SELECT  
Products.[Date Recvd],  
Products.[Invoice NO],  
Products.Suppliers,  
Products.[Product Name],  
Products.Type, 
Products.Size,  
Products.[Load Index],  
Products.[Cost Per Unit],  
Products.QTY,  
Sum([Cost per unit]*[Qty]) AS [TotalCost],  
Sum([Cost Per Unit]*[Qty]*0.1) AS GST, 
Products.[Freight Cost],  
Sum([Freight Cost]/[QTY]) AS [FreightCostPerItem],  
Products.[Freight Company],  
Products.Comments,  
Products.[Date Outgoing],  
Products.[Type Out],  
Products.[Qty Out], 
Products.Freight,  
Products.[Freight Send Cost],  
Sum([Qty]-[Qty Out]) AS [Remaining Stock] 
FROM Products 
GROUP BY 
Products.[Date Recvd], 
Products.[Invoice NO],  
Products.Suppliers,  
Products.[Product Name],  
Products.Type,  
Products.Size,  
Products.[Load Index],  
Products.[Cost Per Unit],  
Products.QTY,  
Products.[Freight Cost],  
Products.[Freight Company],  
Products.Comments,  
Products.[Date Outgoing],  
Products.[Type Out],  
Products.[Qty Out],  
Products.Freight,  
Products.[Freight Send Cost]; 

Then run this query ...

SELECT  
qryFreight1.[Date Recvd],  
qryFreight1.[Invoice NO],  
qryFreight1.Suppliers,  
qryFreight1.[Product Name],  
qryFreight1.Type, 
qryFreight1.Size,  
qryFreight1.[Load Index],  
qryFreight1.[Cost Per Unit],  
qryFreight1.QTY,  
qryFreight1.[TotalCost] AS [Total Cost of Order before GST and Freight],  
qryFreight1.GST, 
([GST]/[Qty]) AS [gst per item],
Sum([TotalCost]+[GST]+[Freight Cost]) AS [TOTAL GST incl],  
qryFreight1.[Freight Cost],  
qryFreight1.[Freight Cost Per Item],  
Sum([Freight Cost]+[TotalCost]+[GST]+[Freight Cost]) AS [GRAND TOTAL Freight plus GST incl], 
qryFreight1.[Freight Company],  
qryFreight1.Comments,  
qryFreight1.[Date Outgoing],  
qryFreight1.[Type Out],  
qryFreight1.[Qty Out], 
qryFreight1.Freight,  
qryFreight1.[Freight Send Cost],  
qryFreight1.[Remaining Stock] 
FROM Products 
GROUP BY 
qryFreight1.[Date Recvd], 
qryFreight1.[Invoice NO],  
qryFreight1.Suppliers,  
qryFreight1.[Product Name],  
qryFreight1.Type,  
qryFreight1.Size,  
qryFreight1.[Load Index],  
qryFreight1.[Cost Per Unit],  
qryFreight1.QTY,
qryFreight1.[TotalCost]
qryFreight1.GST,
qryFreight1.[Freight Cost], 
qryFreight1.[Freight Cost Per Item], 
qryFreight1.[Freight Company],  
qryFreight1.Comments,  
qryFreight1.[Date Outgoing],  
qryFreight1.[Type Out],  
qryFreight1.[Qty Out],  
qryFreight1.Freight,  
qryFreight1.[Freight Send Cost],  
qryFreight1.[Remaining Stock];

[jimatqsi]

Oh, wow Deb, what a difference a couple of days can make. Congratulations. :)

Jim

[MMcCarthy]

You put in all the hard work Deb and learned something in the process. That's what it's all about, at least here on Bytes :)