Is there a way to do this without subqueries? (1 Viewer)

Bloodlvst · Nov 27, 2018

So my predicament is the following:

I am trying to get the total of all of my orders (After discounts). But I can't even seem to get the sum of the order totals to add properly.

As per the screenshot, each order can have multiple line items (each row from orderdetails is a line item). These line items make a sub-total, on which a discount and taxes can be applied.

My forms all work fine for this, but I want a report of all my orders so I can track total sales.

Problem is my query just won't add them up. As per the attached, my query is the following:

Code:

SELECT OrderDetail.OrderID, OrderDetail.SKU, OrderDetail.Quantity, Products.UnitPrice, [Quantity]*[UnitPrice] AS ItemSubTotal, Sum([ItemSubTotal]) AS OrderSubTotal
FROM Products INNER JOIN OrderDetail ON Products.SKU = OrderDetail.SKU
GROUP BY OrderDetail.OrderID, OrderDetail.SKU, OrderDetail.Quantity, Products.UnitPrice, [Quantity]*[UnitPrice];

But I get the result in the 2nd screenshot. I can get around this by making this query a subquery of another query, but I'd like to try and not have a million queries if I can help it. Can anyone offer me any advice, or is a suqquery my best option here?

Cheers!

June7 · Nov 28, 2018

Alternatively, don't do summary calcs in query. Use report Sorting & Grouping feature with aggregate functions in textboxes. That will allow displaying detail data as well as summary calcs.

Bloodlvst · Nov 28, 2018

Thanks June!

The issue unfortunately with this method is that I'd need to include each line item on the report itself would I not? The goal for this report is to just have lines with each Order number, the subtotal, the discount amount, and the subtotal after discount, which would each be totalled at the bottom of the report.

Or does a report work the similarly to the way a continuous form does?

June7 · Nov 28, 2018

Can set the Detail section to not be visible.

You want subtotal by Order but your query is aggregating by SKU.

plog · Nov 28, 2018

The goal for this report is to just have lines with each Order number, the subtotal, the discount amount, and the subtotal after discount,

So why aren't you building a query to do that? An aggregate query (one with GROUP BY) makes a query that returns unique rows of data for every permutation of data in the GROUP BY. So, if you had this:

Code:

SELECT A, B, C, SUM(D)
FROM Table
GROUP BY A, B, C

And you complain multiple B and C values prevent you from adding up D correctly--then what are B & C doing in your query? If you only want totals for unique values of A, you would simply do this:

Code:

SELECT A, SUM(D)
FROM Table
GROUP BY A

Bloodlvst · Nov 28, 2018

plog said:
So why aren't you building a query to do that? An aggregate query (one with GROUP BY) makes a query that returns unique rows of data for every permutation of data in the GROUP BY. So, if you had this:

Code:

SELECT A, B, C, SUM(D) FROM Table GROUP BY A, B, C

And you complain multiple B and C values prevent you from adding up D correctly--then what are B & C doing in your query? If you only want totals for unique values of A, you would simply do this:

Code:

SELECT A, SUM(D) FROM Table GROUP BY A

Okay, I think I get what you're saying here.

To clarify it, because the value of D is not stored in a table, but is a calculation, I have no choice but to first create a query to obtain the value of D, then perform another query to get the totals of D grouped by A, correct?

plog · Nov 28, 2018

No, you can sum the calculation it is based on:

SUM([Quantity]*[Price]) AS Total

Bloodlvst · Nov 28, 2018

plog said:
No, you can sum the calculation it is based on:

SUM([Quantity]*[Price]) AS Total

Thanks! Dunno how I didn't think to try that.

Is there a way to do this without subqueries? (1 Viewer)

Bloodlvst

Registered User.

Attachments

June7

AWF VIP

Bloodlvst

Registered User.

June7

AWF VIP

plog

Banishment Pending

Bloodlvst

Registered User.

plog

Banishment Pending

Bloodlvst

Registered User.

Similar threads

Users who are viewing this thread