Query not grouping all data

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

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
 
I expect there are more than one value in one or more of these fields:
qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
qryCommittedCostsbyCostCode.PhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID,
 
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!
 
Back
Top