Query Not Summing All Records

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

Stacey Crowhurst

Hi. I have a query that shows me all transacations not paid on a contract by
cost code and budget code. For a few records, the transactions have the same
cost and budget codes, but they are showing up individually instead of summed.

Here is the example:
tdCCPID tdCostCodeID tdPhaseCodeID tdVendorID vdVendorName NonContractCosts
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $604.44
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $5,855.80

It should show one line as follows:
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $6,460.24

Anyway, here is the SQL:
SELECT tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName, Sum(tblTransactionDetail.tdLineAmount) AS
NonContractCosts
FROM qryProjectMaxSequence INNER JOIN (tblProjects INNER JOIN
((tblTransactions INNER JOIN tblTransactionDetail ON
tblTransactions.trAutoNumberID = tblTransactionDetail.tdtrAutoNumberID) LEFT
JOIN tblVendors ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID)
ON tblProjects.prjCCPID = tblTransactionDetail.tdCCPID) ON
(qryProjectMaxSequence.prjCCPID = tblProjects.prjCCPID) AND
(qryProjectMaxSequence.MaxOfprjSequence = tblProjects.prjSequence)
WHERE (((tblTransactionDetail.tdContractID) Is Null Or
(tblTransactionDetail.tdContractID)=""))
GROUP BY tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName;

Let me know if you need more information. THANKS!!!
 
If ANY of the five fields:

tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName


is different, then you have different line, in the result. Mainly with
names, it is possible than one has an extra space here, or there (even at
the end, maybe), so they are different even if, once printed, they look
exactly the same.

Does


SELECT DISTINCT tblTransactionDetail.tdCCPID,
tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID,
tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName
FROM tblTransactionDetail LEFT JOIN tblVendors
ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID


produces two different lines too? if so, something in one of the five
fields 'is' different.



Vanderghast, Access MVP
 
Thank you for your help. tdPhaseCodeID had both "" and NULL values so that
was the culprit. I did an update query to change all "" to NULL and now it
works great!
 
Back
Top