No the best way to do this in Record source

C

ChoonBoy

I have a report with its record source set to the following.

SELECT IIf([period]="group","All months",[mthnos]) AS MthNos1,
qPartnerExpGrp.ExpYear, qPartnerExpGrp.PartnerName1,
qPartnerExpGrp.PExpenseID AS ExpenseID, Sum(qPartnerExpGrp.ExpenseReceive) AS
ExpenseReceive, Sum(qPartnerExpGrp.ExpenseCost) AS ExpenseCost,
IIf([period]="group","All months",[expmth]) AS ExpMth1,
qPartnerCount2.CountPnr, qShipmentProfitforPartner.SumOfProfit,
IIf([partner]="company",[sumofprofit],[sumofprofit]/[countpnr]) AS ProfitShr
FROM Tbl_Choice, qPartnerCount2, qPartnerExpGrp INNER JOIN
qShipmentProfitforPartner ON (qPartnerExpGrp.ExpYear =
qShipmentProfitforPartner.ShipYear) AND (qPartnerExpGrp.ExpMth =
qShipmentProfitforPartner.ShipMth) GROUP BY IIf([period]="group","All
months",[mthnos]), qPartnerExpGrp.ExpYear, qPartnerExpGrp.PartnerName1,
qPartnerExpGrp.PExpenseID, IIf([period]="group","All months",[expmth]),
qPartnerCount2.CountPnr, qShipmentProfitforPartner.SumOfProfit,
IIf([partner]="company",[sumofprofit],[sumofprofit]/[countpnr]);


My qPartnerCount2 is actually derived from first grouping a field in a
transaction table and saved as qPartnerCount1 and qPartnerCount2 is used to
give the exact count.

I know this is not the correct way to do this as it give rise to too many
queries in my Query window.

Appreciate help on this with the complete codes.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there a better way? 4
Runtime Error 3000 - Reserved error -3087 0
Change criteria from option group 2
Min and Max for Date Range in Report 4
Create a report from two unrelated sources 1
Extra pages printing 0
Problems with Forecast Query 1
Record Source Error - Can't use a pass-through query or a non-fixed-column query 0

Top