How do I prevent compounded totals in a query combinging other queries 1

[R00K]

I have a query that sums 3 other similar queries that sum slightly different data.

qry1 sums 40% of totalbeans of group 1
qry2 sums 60% of totalbeans of group 2
qry3 sums 100% of totalbeans of group3, group4 and group5

All are ordered by date and each query uses the same data but uses a "where" clause seperating the groups so that the right percentage is applied.

The final query (which causes the problems) sums the totalbeans or qry1, qry2 and qry3 ordered by date. Each preceding query shows one entry per day. The final query shows multiple entries per day multiplies the values as each date occurs several times, which is incorrect.

Here is the code:

Sum(((Nz(qry1!totalbeans,0)+(Nz(qry2!totalbeans,0)+(NZ(qry3!totalbeans,0)

The process was developed to show totalbeans under a group made up of a share of totalbean of other groups according to the percaentages.

I thank you all in advance. Let me know if you need more clarification.

Cheers,

David

 

[lespaul]

can you provide some sample data from your first queries and what you want the summary data to look like in your final query?

Leslie

 

[PHV]

Are the 3 queries joined in your final query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[R00K]

I used left joins on date. By the way, this is for a financial database and has been simplified and modified to preserve privacy.

Data look like this:

Date | blackbeans | favabeans | navybeans | groupid
010104 25 10 34 1
010104 10 11 5 2


Each day may or may not have an entry for each group under each type of bean.

thank you for your interest.

 

[lespaul]

and is that the data from the 1st three queries or is that what you want the summary query to look like?


Leslie

 

[R00K]

I'm sorry.

That is the initial data.

The first query would show the data in the same format but only 40% of the totals for each column, plus a grand total for that day for the groupid1 only.

So the second line would dissappear and the toals would be at 40%

Date | blackbeans | favabeans | navybeans | totalbeans | groupid
010104 10 4 13.6 1 27.6

The seond query would be similar, differing in the percentage and the third would show the full amount. The problem is when I try to combine the total beans of all the groups as stated above.

Cheers,

David

 

[R00K]

qry1

SELECT Runningtotal.CDate, Sum(((Runningtotal!RunningSum*qryChangepercent2!GainPercent)-[BrokerbyAccount])) AS Gainbyaccount, Runningtotal!RunningSum*qryChangepercent2!IntPercent AS IntbyAccount, Runningtotal!RunningSum*qryChangepercent2!DivPercent AS DivbyAccount, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent AS IntPaidbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent AS FTWIntbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent AS FTWDivbyAccount, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent AS BrokerbyAccount, Runningtotal.AccountID, Sum((Runningtotal!RunningSum*qryChangepercent2!GainPercent)) AS GainbyAccount1
FROM qryChangepercent2 LEFT JOIN Runningtotal ON qryChangepercent2.CDate=Runningtotal.CDate
WHERE (((Runningtotal.AccountTypeID)=10 Or (Runningtotal.AccountTypeID)=7 Or (Runningtotal.AccountTypeID)=9))
GROUP BY Runningtotal.CDate, Runningtotal!RunningSum*qryChangepercent2!IntPercent, Runningtotal!RunningSum*qryChangepercent2!DivPercent, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent, Runningtotal.AccountID;

 

[R00K]

Sorry that was qry3. This is qry1.

SELECT Runningtotal.CDate, Sum(((Runningtotal!RunningSum*qryChangepercent2!GainPercent)-[BrokerbyAccount])) AS Gainbyaccount, Runningtotal!RunningSum*qryChangepercent2!IntPercent AS IntbyAccount, Runningtotal!RunningSum*qryChangepercent2!DivPercent AS DivbyAccount, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent AS IntPaidbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent AS FTWIntbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent AS FTWDivbyAccount, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent AS BrokerbyAccount, Runningtotal.AccountID, Sum((Runningtotal!RunningSum*qryChangepercent2!GainPercent)) AS GainbyAccount1
FROM qryChangepercent2 LEFT JOIN Runningtotal ON qryChangepercent2.CDate=Runningtotal.CDate
WHERE (((Runningtotal.AccountTypeID)=10 Or (Runningtotal.AccountTypeID)=7 Or (Runningtotal.AccountTypeID)=9))
GROUP BY Runningtotal.CDate, Runningtotal!RunningSum*qryChangepercent2!IntPercent, Runningtotal!RunningSum*qryChangepercent2!DivPercent, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent, Runningtotal.AccountID;

 

[PHV]

And what about an Union query of your 3 queries ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[R00K]

qry2

SELECT Runningtotal.CDate, Sum(((Runningtotal!RunningSum*qryChangepercent2!GainPercent)-[BrokerbyAccount])) AS Gainbyaccount, Runningtotal!RunningSum*qryChangepercent2!IntPercent AS IntbyAccount, Runningtotal!RunningSum*qryChangepercent2!DivPercent AS DivbyAccount, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent AS IntPaidbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent AS FTWIntbyAccount, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent AS FTWDivbyAccount, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent AS BrokerbyAccount, Runningtotal.AccountID, Sum((Runningtotal!RunningSum*qryChangepercent2!GainPercent)) AS GainbyAccount1
FROM qryChangepercent2 LEFT JOIN Runningtotal ON qryChangepercent2.CDate=Runningtotal.CDate
WHERE (((Runningtotal.AccountTypeID)=10 Or (Runningtotal.AccountTypeID)=7 Or (Runningtotal.AccountTypeID)=9))
GROUP BY Runningtotal.CDate, Runningtotal!RunningSum*qryChangepercent2!IntPercent, Runningtotal!RunningSum*qryChangepercent2!DivPercent, Runningtotal!RunningSum*qryChangepercent2!IntPaidPercent, Runningtotal!RunningSum*qryChangepercent2!FTWIntPercent, Runningtotal!RunningSum*qryChangepercent2!FTWDivPercent, Runningtotal!RunningSum*qryChangepercent2!BrokerPercent, Runningtotal.AccountID;

The last query joins them by cdate and totals the columns.

It is a lot of code and probably a little messy.

Thanks again for your attention to this.

David

 

[R00K]

And what about an Union query of your 3 queries ?

Can you give me an example? Should I give up the query I have posted at the top in favor of a union query? I will try it.

Cheers,

David

 

[lespaul]

a union query is set up like :

SELECT FIRSTNAME, LASTNAME FROM TblPriorCustomer
UNION
SELECT FIRSTNAME, LASTNAME FROM tblCurrentCustomer

which will give you all the first and last names from each table in a single result set.

So you could take query 1 add UNION to the end of it, paste in query2, add UNION to the end of it and then paste in query3. Then you will have single result set with all your records in it. Does that make sense?

Leslie

 

[R00K]

I am game for anything...will a union qery prevent the multiplication or compounding issue I am having?

The query I have worked well until account types began to change and became more complicated. I then needed more queries to get the right data to show up in the final query which really just gives a grand total. It was today that I noticed an entry that was 4 times greater than it should have been. Upon more investigation, I discovered that the query degrades as accounts are added.

I will report my success/failure on Monday.

Thank you all for your help. Have a good weekend.

David

 

[lespaul]

I don't know if a union query will help or not, I'm still waiting for the sample results from the first three queries and what you want the summary results to look like from those results.

I usually use union queries when the information I need is in two or more tables. I have an old table tblOldJurors that is structured completely different than the tblNewJurors, but I need let my users still see the information from the old table.

I use:

SELECT LASTNAME, FIRSTNAME, JURNUM From TblNewJurors
UNION
SELECT LASTNAME, FIRSTNAME, "0" From tblOldJurors

(I have to add the 0 because the old table doesnt have the field JURNUM, but the column count has to be the same for each select)

Now I end up with a single list of jurors, both old and new:

Blow Joe 11520 (record from tblNewJurors)
Smith John 15557
Doe Jane 0 (record from tblOldJurors)

Leslie

 

[JonFer]

If you are getting multiples of your numbers, you are probably missing a join condition in the final query. You said you are joining on CDate but what about AccountID? Can you post the final query? Any field you are grouping by should be joined in the final query unless they are derived from another field. For example, you are grouping by several percentage fields - are those based on the date?

Also, will some dates be missing in one of the 3 initial queries? A left join makes sure you have all of the records from the "left" table but you may also have dates in the "right" table that are not in the "left" table. If so, you'll need another table that lists all dates and account combinations to use as your "left" table and then make the 3 queries "right" tables joined to it.

 

[R00K]

Here is the last query which should give me a total. Sorry it took so long.

SELECT tblNetNetBalance.CDate, Sum(((Nz(qryNHLI!Gainbyaccount,0)+(Nz(qryNHLI!IntbyAccount,0)+(NZ(qryNHLI!DivbyAccount,0)+(Nz(qryNHLI60!Gainbyaccount,0)+(Nz(qryNHLI60!IntbyAccount,0)+(NZ(qryNHLI60!DivbyAccount,0)+(Nz(qryNHLI40!Gainbyaccount,0)+(Nz(qryNHLI40!IntbyAccount,0)+(NZ(qryNHLI40!DivbyAccount,0)+(NZ(qryNHLITrans!Depwith,0))))))))))))) AS TBandNHLI
FROM (((tblNetNetBalance LEFT JOIN qryNHLI ON tblNetNetBalance.CDate = qryNHLI.CDate) LEFT JOIN qryNHLITrans ON tblNetNetBalance.CDate = qryNHLITrans.TransactionDate) LEFT JOIN qryNHLI40 ON tblNetNetBalance.CDate = qryNHLI40.CDate) LEFT JOIN qryNHLI60 ON tblNetNetBalance.CDate = qryNHLI60.CDate
GROUP BY tblNetNetBalance.CDate
HAVING (((tblNetNetBalance.CDate)>#3/6/2003#));

I tried the UNION query and it seems to work. I'm still not sure why I was having a problem and hope to find a solution. I think I will use a form to sum the columns.

Cheers,

David

 

[JonFer]

Your HAVING clause should be a WHERE clause like this:

SELECT ...
FROM ...
WHERE tblNetNetBalance.CDate > #3/6/2003#
GROUP BY tblNetNetBalance.CDate ;

Having is applied to the groups after all of the records have been filtered by the WHERE clause.

You are also not joining on the other fields that you are grouping by in the first queries. This causes a multiplication of your amounts. Here is an example of the problem:

Query1:

  CDate  Admin   Amount
  1/1/04  001     $311
  1/1/04  002     $241

Query2:

  CDate  Admin   Amount
  1/1/04  001     $312
  1/1/04  002     $242

Joining on CDate alone and then Grouping by CDate generates 4 rows to summarize (combinations of records from each query with the same CDate):

  CDate    Amount (Query1+Query2)
  1/1/04  $311+$312
  1/1/04  $311+$242
  1/1/04  $241+$312
  1/1/04  $241+$242

Joining on CDate AND AdminID (uniquely identifies a record in the example query) will generate 2 rows to summarize. You can then group by CDate alone and get the correct total:

  CDate    Amount (Query1+Query2)
  1/1/04  $311+$312
  1/1/04  $241+$242