Query Not Summing All Records

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!!!
 
V

vanderghast

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
 
S

Stacey Crowhurst

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!
 

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

Null Values 5
Query not grouping all data 2

Top