I currently have a query that takes

[boblovesyousomuch]

I currently have a query that takes some information from a couple of tables and displays the various information. I then use another query to perform mathematical functions on that data. I really want the query to happen in one go put can't figure it out.

I have put the too queries below (they are big and confusing):

QryTermCodeByAgent

SELECT Stats.DO_FinalTerm AS TermCodeShort, tblTermCode.TC_Actual AS TermCodeDescript, Count(Stats.DO_FinalTerm) AS TermCodeCount, tblAgentList.A_ID, Sum(Stats.DO_Balance) AS TermCodeBalance
FROM (tblAgentList INNER JOIN Stats ON tblAgentList.A_DavoxID = Stats.DO_OperID) INNER JOIN tblTermCode ON Stats.DO_FinalTerm = tblTermCode.TC_TermCode
GROUP BY Stats.DO_FinalTerm, tblTermCode.TC_Actual, tblAgentList.A_ID, Stats.DO_DateCalled, tblAgentList.A_ID
HAVING (((tblAgentList.A_ID)=[Forms]![FrmCallSummary]![ListAgents]) AND ((Stats.DO_DateCalled) Between [Forms]![FrmCallSummary]![txtStartDate] And [Forms]![FrmCallSummary]![txtEndDate]));

QryTermCodeFullDebt

SELECT QryTermCodeByAgent.TermCodeShort, QryTermCodeByAgent.TermCodeDescript, QryTermCodeByAgent.TermCodeCount, Format([TermCodeCount]/[Connects],'Percent') AS VolumePer, QryTermCodeByAgent.TermCodeBalance, Format([TermCodeBalance]/[TotalBalance],'Percent') AS ValuePer
FROM QryTermCodeByAgent INNER JOIN QryConnects ON QryTermCodeByAgent.A_ID = QryConnects.A_ID
ORDER BY QryTermCodeByAgent.TermCodeDescript;

I want to do this so I can pull it in to VB and play around with the ORDERING etc.

Cheers for any help in advance

 

[JonFer]

Make this change to your first query:

SELECT Stats.DO_FinalTerm AS TermCodeShort,
tblTermCode.TC_Actual AS TermCodeDescript,
Count(Stats.DO_FinalTerm) AS TermCodeCount,
tblAgentList.A_ID,
Sum(Stats.DO_Balance) AS TermCodeBalance
FROM (tblAgentList INNER JOIN Stats ON
tblAgentList.A_DavoxID = Stats.DO_OperID)
INNER JOIN tblTermCode
ON Stats.DO_FinalTerm = tblTermCode.TC_TermCode

WHERE (((tblAgentList.A_ID)=[Forms]![FrmCallSummary]![ListAgents]) AND ((Stats.DO_DateCalled) Between [Forms]![FrmCallSummary]![txtStartDate] And [Forms]![FrmCallSummary]![txtEndDate]))

GROUP BY Stats.DO_FinalTerm,
tblTermCode.TC_Actual,
tblAgentList.A_ID;

I put your conditions in the WHERE clause because the criteria are not on aggregate fields. I removed the date field because you don't include that in your final totals.

Now add in the QryConnects table linked on A_ID and you can do calculations like this:

Count(Stats.DO_FinalTerm)/Max([Connects]) AS VolumePer
Sum(Stats.DO_Balance)/Max([TotalBalance]) AS ValuePer