Query not grouping all data

[Stacey Crowhurst]

Hi. I have a query that shows me costs by vendor for each budget code.
Generally, it works just fine. But recently, I have come across some
instances where it is not summing the records for a vendor.

This is what it will show me:
prjCCPID cocBudgetCodeID nonVendorName NonContract
TCC09-02 125-500 ALLIED MOLDED PRODUCTS $2,421.00
TCC09-02 125-500 ALLIED MOLDED PRODUCTS $2,421.00
TCC09-02 125-500 AMERICAN COUNTRY $604.44
TCC09-02 125-500 AMERICAN COUNTRY $5,855.80


This is what I want it to show me instead:
prjCCPID cocBudgetCodeID nonVendorName NonContract
TCC09-02 125-500 ALLIED MOLDED PRODUCTS $4,842.00
TCC09-02 125-500 AMERICAN COUNTRY $6,460.24

Here is the SQL:
SELECT qryCommittedCostsbyCostCode.prjCCPID, tblProjects.prjProjectManager,
qryCommittedCostsbyCostCode.prjParentProjectID,
qryCommittedCostsbyCostCode.prjProjectName,
qryCommittedCostsbyCostCode.cocBudgetCodeID,
qryCommittedCostsbyCostCode.bcBudgetCodeDesc,
qryCommittedCostsbyCostCode.vdVendorID,
qryCommittedCostsbyCostCode.conVendorName,
qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
Sum(qryCommittedCostsbyCostCode.Contract) AS SumOfContract,
Sum(qryCommittedCostsbyCostCode.NonContractCosts) AS SumOfNonContractCosts,
qryCommittedCostsbyCostCode.PhaseCodeID AS cdPhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID, tblProjects.prjSequence,
tblProjects.prjStatusID
FROM qryCommittedCostsbyCostCode INNER JOIN tblProjects ON
qryCommittedCostsbyCostCode.prjCCPID = tblProjects.prjCCPID
GROUP BY qryCommittedCostsbyCostCode.prjCCPID,
tblProjects.prjProjectManager,
qryCommittedCostsbyCostCode.prjParentProjectID,
qryCommittedCostsbyCostCode.prjProjectName,
qryCommittedCostsbyCostCode.cocBudgetCodeID,
qryCommittedCostsbyCostCode.bcBudgetCodeDesc,
qryCommittedCostsbyCostCode.vdVendorID,
qryCommittedCostsbyCostCode.conVendorName,
qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
qryCommittedCostsbyCostCode.PhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID, tblProjects.prjSequence,
tblProjects.prjStatusID;

I am not sure why theses lines are not grouping. Let me know if you need
more information and thank you in advance for your help!

Stacey

 

[Duane Hookom]

I expect there are more than one value in one or more of these fields:
qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
qryCommittedCostsbyCostCode.PhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID,

 

[KenSheridan via AccessMonster.com]

Stacey:

If you want to show all the columns, including those without distinct values
per prjCCPID/cocBudgetCodeID/nonVendorName then you have two options:

1. Return all rows in an ungrouped query and sum the costs in subqueries.

2. Sum the costs in a separate query grouped only by those columns with
distinct values. This may be enough in itself for your purposes, but if you
do need to show the values from the other columns also then this query can be
joined to an ungrouped query, or to the individual base tables in another
query.

For the second option, which I think you'll find easier, the query to return
the total costs per prjCCPID/cocBudgetCodeID/nonVendorName would be:

SELECT prjCCPID, cocBudgetCodeID, nonVendorName,
SUM(Contract) AS SumOfContract,
SUM(NonContractCosts) AS SumOfNonContractCosts
FROM qryCommittedCostsbyCostCode
GROUP BY prjCCPID, cocBudgetCodeID, nonVendorName;

That in itself should give you a result set like that you gave in your post
as the desired result, but if you also want to include the other columns
which you included in your original query then join the above query either to
a an ungrouped query which returns those columns or to the original base
tables. You'll still get duplicate rows per vendor where values in the other
columns are not distinct per vendor, but the totals will be per vendor.

Ken Sheridan
Stafford, England

 

[Stacey Crowhurst]

Thank you Ken & Duane. The culprit was "" and NULL values in tdPhaseCodeID.
So I ran an update query to change "" to NULL and now it works great!