How do you pull a Total from a Form into a Query? (1 Viewer)

AC/DC

New member
Local time
Today, 11:44
Joined
Jun 19, 2019
Messages
12
I have a quote form showing a quote total based off the quote details subform. I am trying to create a query where I get a full list of all the quotes created and the quote totals.

I can easily get the list of quotes as every new quote created in the form is added to the quotes table. However, the quote total is not listed in any table nor can I get it on a query referencing the quote total text box. How can I pull the quote totals for each quote from the form into a query? Do I need to redo the quote total calculated field in the query referencing the quote details subform?

Please note I am a beginner to Access.
 
Last edited:

June7

AWF VIP
Local time
Today, 07:44
Joined
Mar 9, 2014
Messages
5,423
Totals are usually result of aggregate query.

Perhaps you need to build a report using its Sorting & Grouping features with aggregate calcs.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. All data is stored in tables. You can then use a query to pull this data and do calculations on it. Things you can calculate on a form, you should be able to calculate in a query too.
 

AC/DC

New member
Local time
Today, 11:44
Joined
Jun 19, 2019
Messages
12
I was able to redo the quote total calculation in part as seen below (this formula works):
QuoteAmt: IIf(Sum((1-[Quotes]![QDiscP])*[QuotesDetail]![QQty]*[QuotesDetail]![QUPrice]) Is Null,0,Sum((1-[Quotes]![QDiscP])*[QuotesDetail]![QQty]*[QuotesDetail]![QUPrice]))

Now I need to exclude QAccy from the total amount by adding the exclusion to the above formula. Below is what I found in the database:
=Sum([QuotesDetail]![QQty]*IIf([QuotesDetail]![QAccy] Is Null,[QuotesDetail]![QUPrice],0))

But I'm can't figure out how to combine both formulas into one. Any ideas on how to add the QAccy exclusion to the first formula? Multiple IIf within the formula or do I need to use AND somehow?
 
Last edited:

June7

AWF VIP
Local time
Today, 07:44
Joined
Mar 9, 2014
Messages
5,423
What exclusion? QAccy is not in the QuoteAmt calc so exactly how could it be excluded?
 

AC/DC

New member
Local time
Today, 11:44
Joined
Jun 19, 2019
Messages
12
Sorry if I was not clear. QAccy is not in the QuoteAmt formula but needs to be added to it. I need to take QAccy out from the total amount by adding it as an exclusion in the QuoteAmt formula. I hope this is clear. If not, let me know and I'll try to clarify further.
 

June7

AWF VIP
Local time
Today, 07:44
Joined
Mar 9, 2014
Messages
5,423
Still confused. Where is QAccy included in the QuoteAmt? Do you mean you need to reduce the QuoteAmt by the value of the sum of QAccy?
 

AC/DC

New member
Local time
Today, 11:44
Joined
Jun 19, 2019
Messages
12
Item Name--QAccy----QQTY-----QUPrice------Total
---Item 1-----------------1----------$10--------$10
---Item 2-----------------1----------$20--------$20
---Item 3-------1---------1----------$30--------$30
---Item 4-------2---------1----------$40--------$40

I couldn't figure out how to format the above so I used --- for the spacing.

The QuoteAmt formula sums everything giving $100. I need to remove the QAccy items that are not blank from the total/calculation. I.e. removing Items 3&4 (keeping items 1&2) resulting in a total of $30. So I need my QuoteAmt formula to have QAccy added to exclude the non-null QAccy fields?

QuoteAmt formula giving $100:
QuoteAmt: IIf(Sum((1-[Quotes]![QDiscP])*[QuotesDetail]![QQty]*[QuotesDetail]![QUPrice]) Is Null,0,Sum((1-[Quotes]![QDiscP])*[QuotesDetail]![QQty]*[QuotesDetail]![QUPrice]))

Quote total formula I found that excludes the QAccy filled fields (keeps the blank QAccy fields) but doesn't account for the discount "QDiscP" that I need to have as shown in the QuoteAmt formula:
=Sum([QuotesDetail]![QQty]*IIf([QuotesDetail]![QAccy] Is Null,[QuotesDetail]![QUPrice],0))

I need to combine the two formulas into one or simply add the QAccy exclusion parameter to the QuoteAmt formula preferably.

Please let me know if this helps. My apologies for the bad clarification, I'm unsure if this is due to my lack of understanding of Access or formulas or worse both.
 
Last edited:

June7

AWF VIP
Local time
Today, 07:44
Joined
Mar 9, 2014
Messages
5,423
You want to exclude records from the Sum where QAccy field is not Null? If you are doing an aggregate query, apply filter criteria to the query so the records just won't be there and therefore not in the Sum.

But if you want to retrieve and display all the records (as in a report), do the simple arithmetic calc in a regular query:
ExtPrice: (1-[Quotes]![QDiscP])*[QuotesDetail]![QQty]*[QuotesDetail]![QUPrice])

Then have calc in textbox in header or footer:

=Nz(Sum(IIf(Not [QAccy] Is Null, Null, [ExtPrice])), 0)
 
Last edited:

Users who are viewing this thread

Top Bottom