Summarizing based on criteria

[gddrew]

All:

I'm new and inexperienced with Access, so please forgive the rather basic question. I have a table of data for which I want to produce a report. In the report, I want to show dollar amounts only for specific cost centers, while those not in the particular group of cost centers I want to show only the summarized amount. I have uploaded a file to show you an example of what the table data looks like and what the report I want to produce should look like.

Thanks in advance for your help.

 

[john471]

Here's one way you could do it. - no guarantee it is the easiest / best / smartest....

Create a table to house the Cost Center Numbers that you want itemised (and only those), with one field "CostCentre" as the Primary Key.

eg:
tblCostCenterShowDetail
------------------------
CostCenter - Same Data Type definition as "CostCenter" in the relevant source table for "qrySumByCostCenter"
----

Then :-
Add a record to this table for each Cost Center you want itemised (in your example, add two records, 123456, and 789012).

Modify your "qrySumByCostCenter" or build a new query based on it and the new table.

Create an OUTER JOIN between the CostCenter fields ("Include all records from 'YourMainTable' and only those records from 'tblCostCenterShowDetail' where the joined fields are equal.")

Then in your query delete the existing CostCenter field (which is sourced form the main table) and replace it with something like

CostCenter: IIf(IsNull([tblCostCentreShowDetail].[CostCenter]),"Other",[tblCostCentreShowDetail].[CostCenter])

Make this field a "Group By".

This way, any cost center entered into the new table will show up with its own cost centre number, any that is not present in the table will be displayed (and grouped and summed) as "Other".


Build you report based on the new query.

To adjust which Cost Centers are reported in detail, simply add/delete records in the table.

HTH

Regards

John.

 

[gddrew]

Thank you for answering my question. I have followed your instructions, but apparently I'm missing something on the IIf statement because I'm getting an error message that says "The specified field 'CostCenter' could refer to more than one table listed in the FROM clause of yoru SQL statement."

qrySumByCostCenter.BudgetYear, qrySumByCostCenter.CaseNumber, qrySumByCostCenter.CaseName, qrySumByCostCenter.CaseStateDesc, qrySumByCostCenter.DateStateLastChanged, qrySumByCostCenter.Biz_Group, qrySumByCostCenter.Tech_Group, qrySumByCostCenter.MU, qrySumByCostCenter.Tech_LastName, qrySumByCostCenter.SumOfTotal, IIf(IsNull([tblCostCenterShowDetail].[CostCenter]),"Other",[tblCostCenterShowDetail].[CostCenter])
FROM qrySumByCostCenter LEFT JOIN tblCostCenterShowDetail ON qrySumByCostCenter.CostCenter = tblCostCenterShowDetail.CostCenter
GROUP BY CostCenter;

 

[gddrew]

I figured out what I had done wrong, so below is my corrected SQL. Thanks very much for your kind assistance.

SELECT qrySumByCostCenter.BudgetYear, qrySumByCostCenter.CaseNumber, qrySumByCostCenter.CaseName, qrySumByCostCenter.CaseStateDesc, qrySumByCostCenter.DateStateLastChanged, qrySumByCostCenter.Biz_Group, qrySumByCostCenter.Tech_Group, qrySumByCostCenter.MU, qrySumByCostCenter.Tech_LastName, qrySumByCostCenter.SumOfTotal, IIf(IsNull([tblCostCenterShowDetail].[CostCenter]),"Other",[tblCostCenterShowDetail].[CostCenter]) AS CostCenter
FROM qrySumByCostCenter LEFT JOIN tblCostCenterShowDetail ON qrySumByCostCenter.CostCenter = tblCostCenterShowDetail.CostCenter
GROUP BY qrySumByCostCenter.BudgetYear, qrySumByCostCenter.CaseNumber, qrySumByCostCenter.CaseName, qrySumByCostCenter.CaseStateDesc, qrySumByCostCenter.DateStateLastChanged, qrySumByCostCenter.Biz_Group, qrySumByCostCenter.Tech_Group, qrySumByCostCenter.MU, qrySumByCostCenter.Tech_LastName, qrySumByCostCenter.SumOfTotal, IIf(IsNull([tblCostCenterShowDetail].[CostCenter]),"Other",[tblCostCenterShowDetail].[CostCenter]);

 

[john471]

Well done on figuring it out, and glad it worked. Thanks for replying back.