Solved some, but still need help

[Ginger]

I solved the first problem by using the "nz" function.
I adjusted the 2nd query to use update to field3 of the 1st
query. But it is not giving me a true total. Instead of
$3,400, it is giving me $17,775.00.
I think I am still doing something incorrectly.

Ginger

 

[John Vinson]

I solved the first problem by using the "nz" function.
I adjusted the 2nd query to use update to field3 of the 1st
query. But it is not giving me a true total. Instead of
$3,400, it is giving me $17,775.00.
I think I am still doing something incorrectly.

Please post the SQL of your query. It's awfully difficult to tell what
you might be doing wrong without seeing what you're doing!

 

[Ginger]

John,
good idea. thank you. I am doing this with the query
builder, so I wasn't thinking of sending the sql. sorry.


The 1st query is as follows:

SELECT DISTINCTROW tblPlayoffs.Date,
Sum([tblPlayoffs]![GrossSales]-(([tblPlayoffs]![GrossSales]*[tblPlayoffs]![CalSalesTax])+[tblPlayoffs]![ElectricBill]+[tblPlayoffs]![VariousUtility]+[tblPlayoffs]![Payroll]+[tblPlayoffs]![Lease]+[tblPlayoffs]![SumOfLiability]+[tblPlayoffs]![DirectTV]+[tblPlayoffs]![WorkersComp]+[tblPlayoffs]![EricOwnerDraw]+[tblPlayoffs]![AaronOwnerDraw]))
AS RE1, Sum(qryCOGSforRetainedEarningsUpdate.[Sum Of COGS])
AS REonlyCOGS,
Sum(([tblPlayoffs]![GrossSales]-(([tblPlayoffs]![GrossSales
]*[tblPlayoffs]![CalSalesTax])+nz([qryCOGSforRetainedEarningsUpdate]![Sum
Of
COGS],0)+[tblPlayoffs]![ElectricBill]+[tblPlayoffs]![VariousUtility]+[tblPlayoffs]![Payroll]+[tblPlayoffs]![Lease]+[tblPlayoffs]![SumOfLiability]+[tblPlayoffs]![DirectTV]+[tblPlayoffs]![WorkersComp]+[tblPlayoffs]![EricOwnerDraw]+[tblPlayoffs]![AaronOwnerDraw])))
AS REwALL
FROM tblPlayoffs LEFT JOIN qryCOGSforRetainedEarningsUpdate
ON tblPlayoffs.Date =
qryCOGSforRetainedEarningsUpdate.EventDate
GROUP BY tblPlayoffs.Date;


The 2nd query which is based on the above is as follows:

UPDATE DISTINCTROW tblPlayoffs LEFT JOIN
qryUpdatePlayoffsREsum ON tblPlayoffs.Date =
qryUpdatePlayoffsREsum.Date SET
tblPlayoffs.RetainedEarnings =
[qryUpdatePlayoffsREsum]![REwALL];

Thank you John.

Ginger

 

[John Vinson]

FROM tblPlayoffs LEFT JOIN qryCOGSforRetainedEarningsUpdate

Since you're joining the table to the query, and then summing over the
joined records, you're almost surely getting as many copies of each
tblPlayoffs record as there are matching values in the query - so your
sums will be multiplied by the number of matching records in that
query.

Without knowing more about the table contents, and without spending a
lot more time than I can justify as a volunteer, I can't analyze just
what sum you're getting - but do look at the table datasheet of the
plain Select query and see what you are actually summing!

Note also that no Totals query, nor any query based on a Totals query,
nor any query containing a Totals query will ever be updateable.

 

[Ginger]

Thank you John.
I think your last paragraph might solve the problem. I'll
work on it this morning.
Once again, thank you.

Ginger