How to calculate

[seb]

Hello



In an MS Acces 2016, I have a Form to search queries between dates.
My union query works well.
I need the total of the column Freq1 to use in a calculated field.
However, I don't know how to calculate with sql the total of the column Feq.
I attach my database and an excel file (zip)with the column /Prc) that I need in my query

Your help is appreciated

SELECT tbltaskmod.day, tbltaskmod.plot_id AS plot, tbltaskmodname.taskmodname AS taskmod, Sum(tbltaskmod.qty) AS Freq1FROM tbltaskmodname INNER JOIN (tblplot INNER JOIN tbltaskmod ON tblplot.Loteid = tbltaskmod.plot_id) ON tbltaskmodname.taskmod_id = tbltaskmod.taskmodname_idGROUP BY tbltaskmod.day, tbltaskmod.plot_id, tbltaskmodname.taskmodnameHAVING (((tbltaskmod.day) Between [Formularios]![frm_qrys_charts]![txtday1] And [Formularios]![frm_qrys_charts]![txtday2]))UNION SELECT tblbtask.day, tblbtask.plot_id AS plot, tblbtask_name.btask_name AS btast, Sum(tblbtask.qty) AS Freq2FROM tblbtask_name INNER JOIN (tblplot INNER JOIN tblbtask ON tblplot.Loteid = tblbtask.plot_id) ON tblbtask_name.btask_id = tblbtask.btask_idGROUP BY tblbtask.day, tblbtask.plot_id, tblbtask_name.btask_nameHAVING (((tblbtask.day) Between [Formularios]![frm_qrys_charts]![txtday1] And [Formularios]![frm_qrys_charts]![txtday2]));

[pbaldy]

One way would be to put a textbox with a DSum() on the form that pulled the total, then add it to your query:

Round(Sum(tbltaskmod.qty)/[Forms]![frm_qrys_charts]![text9],4) AS Pct

The DSum() could be in the query, but would be less efficient since it would be run for every record returned.

[seb]

If I write In the txtTotal =Sum([tbltaskmod].[qty]) and Error is displayed
If I write in the TxtTotal = DSum=Suma([tbltaskmod].[qty]) a message "The expression you specified contains a function with an incorrect number of arguments" is displayed

I do not know what I should write in the txtTotal

Please help

[davegri]

TxtTotal = DSum("[qty]","[tbltaskmod]")

[pbaldy]

I suspect you'd want the same date criteria as well.

http://www.theaccessweb.com/general/gen0018.htm

[seb]

davegri.Than you very much for your help.
I wrote in the txttotal the code you recommended. However, an error (#¿Name?) is displayed.
The Query I am calling is a Union Query from 2 tables.
Any idea is welcome

[seb]

pbald.I am going to study the link. I hope to fins a solution.

[davegri]

The Query I am calling is a Union Query from 2 tables.

Well, your original post called for a sum in a table, not a query.
It has to be like this:

txtTotal = Dsum("[field name in your query]","[query name]")

[pbaldy]

Okay, post back if you get stuck. I'm not sure you can use the query in the DSum(), as it may cause a circular reference, but you can try it.

[seb]

Many Thanks.
I really apologize if I did not say that I need the total from a union query.
I have been unable to insert the correct code in txttotal.
I attach my database.

[davegri]

davegri_test1.zip
Try the attached.
I fixed errors in uqry_jar_modbtar which wouldn't run at all. Then added qty field to that query.
Removed the expression in txttotal data property and set the value of that textbox in the cmb_search click_event.

[seb]

davegri. Smart code you sent me.
I was kilometres away to find the solution.
Thank you very much indeed.

[seb]

paldy.
In this post, davegri showed me how to calculate total in txttotal.
Nevertheless, I still don't know where in my union query I have to include your code Round(Sum(tbltaskmod.qty)/[Forms]![frm_qrys_charts]![txttotal],4) AS Pct.
I wrote txttotal instead of txt9

Can you help me with this?
Many thanks in advance,

[pbaldy]

In your original sample this worked:

SELECT tbltaskmod.day, tbltaskmod.plot_id AS plot, tbltaskmodname.taskmodname AS taskmod, Sum(tbltaskmod.qty) AS Freq1, round(Sum(tbltaskmod.qty)/[Forms]![frm_qrys_charts]![text9],4) AS Pct

You'd have to add to the second SELECT as well.