How do you pull a Total from a Form into a Query?

[AC/DC]

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.

 

[June7]

Totals are usually result of aggregate query.

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

 

[theDBguy]

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]

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?

 

[June7]

What exclusion? QAccy is not in the QuoteAmt calc so exactly how could it be excluded?

 

[AC/DC]

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]

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]

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.

 

[June7]

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)