Sum of a field defined by query expression

[age0004]

Okay, so actually.... It works, but not quite.... So its giving me the total for every single order in the query... but I need the total for the order in question. So if my order form has orderID=4 I need the total for that order, not for ALL of the orders....

Is there a way to make that specification in the equation? Or do you have any ideas of how to go about this?

Thanks again!!

 

[The_Doc_Man]

Is there a way to make that specification in the equation?

There is a third argument to Domain Aggregates - the criterion clause - that looks like a WHERE clause except it doesn't have the word "WHERE" in it.

Whatever you are using to select a single order (the "order in question") can be applied to the criterion clause and make it selective, too.

https://www.techonthenet.com/access/functions/domain/dsum.php

There is an example in the article that specifically shows using an OrderID and a number as the criterion for the Domain selection.

Note that the article always uses "criteria" which is plural. And you CAN have multiple criteria, separated by AND and OR conjunctions. If you have complex multiple criteria, you MIGHT wish to build a string and then just plop that string variable as the third argument.

Criteria = "( [Part] = 'Resistor' ) AND ( [PValue] = '10K' ) AND ( ( [Tolerance] = '5%' ) OR ( [Tolerance] = '10%' ))"
TheSum = DSum( "[Price]", "Catalog", Criteria )

Note that this is a contrived example just to show syntax.

 

[age0004]

Okay, So I have it working correctly if I type the actual order number in. For instance for orderID=4, the correct total comes up using this

=DSum("[extendedprice]","Qry_orderdetail","[OrderID]=4")

Now, since I have one order form for all orders... being filtered by the order number.. how would I make this say something like .." Where the OrderID = the OrderID thats in question".. The user should be able to go to any order and see the total without having to say which orderID specifically they need the total for

I tried
=DSum("[extendedprice]","Qry_orderdetail","[OrderID]=OrderID") and thats not working...
=DSum("[extendedprice]","Qry_orderdetail","[OrderID]=[forms]![orderform]![orderID] is too long


"Here's a hint for future consideration. If you ever decide to apply a filter to the form, just remember that the filter clause can ALSO be copied and used as the criteria clause (3rd argument) in the DSum(), as long as the form uses the same table as is used in the domain (2nd argument) in the DSum()."

I think I just didnt quite understand this when you mentioned it earlier^^

 

[gemma-the-husky]

=DSum("[extendedprice]","Qry_orderdetail","[OrderID] = " & OrderID)

=DSum("[extendedprice]","Qry_orderdetail","[OrderID] = " & [forms]![orderform]![orderID])

you need the orderID to reference the numeric value in the "where" clause
there was no closing bracket on the second example, but I am sure that is a typo.


[edit - I added a similar construct to the second example]

 

[Mark_]

To add to Dave's response, you normally would have something on your order that shows the order number. You would generally reference that controls, so it could look like
"OrderID = " & me.OrderID
as you would be referencing the control on screen.

 

[age0004]

Thank you!

Okay perfect. I got that first formula to work beautifully!

Onto another issue I've been having, which will hopefully complete this form. Now that I have the subtotal and shipping total working, I need a grand total. I can get this to work just by doing a control source simple addition formula, however, I would like to be able to reference this on another form which lists the orders and their totals so its easier to keep up with them.. Can this formula be done in a qry and then referenced or should it be done in a control source and then referenced somehow on the other form?

Here are some images.
Cature1 is the form thats based on a Qry_orderlist and its only being used to display orders. Its ordered from newest to oldest orders...

Capture2 is the order form at the bottom where I have the "subtotal" "shippingtotal" and "taxes" being added together for a grand total... however, Id like this grand total to also show up in the orderlist page from capture1. Im just not sure which way will work best... through the query or through control sources?

 

[Mark_]

In your order, I am guessing you use line items for what is being ordered. Do these line items have something that indicates they are taxable or have a shipping charge?

If yes, you can either add a line item for each once the order is placed OR you can calculate them as needed. Either way, you may want to make a total query that gives you the total amount for an order. This is what you would reference by joining it to your query to give you the total for each order.

 

[age0004]

In your order, I am guessing you use line items for what is being ordered. Do these line items have something that indicates they are taxable or have a shipping charge?

If yes, you can either add a line item for each once the order is placed OR you can calculate them as needed. Either way, you may want to make a total query that gives you the total amount for an order. This is what you would reference by joining it to your query to give you the total for each order.

Yes it is a line item. Its a subform in a datasheet view on a main form. Each item could be ordered from somewhere different. Each vendor has different shipping costs and tax costs. Some places have free shipping and some places we don't pay tax so it has to be assigned to the item. So my main form is based on a query as well...

I guess im not just not quite sure how to go about making a "total" query for this. Would I just make a query, build an expression for "total" summing everything, then.... referencing it on my orderslist? (Excuse my lack of knowledge/experience I am still learning)

 

[Mark_]

Your query should look something like

SELECT [OrderID],[DetailNo], [Amount] + [Shipping] + [Tax] AS TotalAmt FROM TblOrder WHERE [OrderID] = OrderID

Then when you have the query open in design view you press the TOTALS button up to.

 

[age0004]

Okay, so one thing that Im not sure if I will need to change or not is this....


"subtotal" and "extended price" comes from the table OrderDetails
whereas the "taxes" and "total" comes from the table Orders

Can I keep this the same and still do a query with the total? Is it a problem that the calculation for extended price is done as an expression in a query and the subtotal calculation is done as a control source (so the $$ value is not actually being stored in the query)??

I dont know if that makes any sense or not, but I feel like because my subtotal is not done as an expression in the query then I cannot reference it?