S
Stacey Crowhurst
Hi. I have a query that shows me basically the following: Vendor Name,
Contract Costs, and NonContract Costs by both Budget Code and Cost Code. In
my database cost codes roll up to budget codes in a heirarchy.
The query works great, which in turn creates a report I use frequently. I
would like to add two pieces of information to my report: Potential Change
Orders and Project Manager Adjustments. However, the issue is that PCOs and
PM Adjustments are only recorded at the budget code level, not the cost code
level. So right now my query will repeat the PCO and PM Adjustment values
each time the budget code shows up. Is there a way to tell my query to only
return those values once per budget code?
Here is the SQL:
SELECT qryCommittedCostsbyCostCode.prjCCPID,
qryCommittedCostsbyCostCode.prjParentProjectID,
qryCommittedCostsbyCostCode.prjProjectName,
qryCommittedCostsbyCostCode.cocBudgetCodeID,
qryCommittedCostsbyCostCode.bcBudgetCodeDesc,
qryCommittedCostsbyCostCode.vdVendorID,
qryCommittedCostsbyCostCode.conVendorName,
qryCommittedCostsbyCostCode.cocCostCodeID,
qryCommittedCostsbyCostCode.cocCostCodeDesc,
qryCommittedCostsbyCostCode.PhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID,
qryCommittedCostsbyCostCode.Contract, qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
qryCommittedCostsbyCostCode.NonContractCosts,
qryPCOs.AllPCOs,
qryPMAdjustments.PMAdjustments
FROM (qryCommittedCostsbyCostCode LEFT JOIN qryPCOs ON
(qryCommittedCostsbyCostCode.prjCCPID = qryPCOs.pcoCCPID) AND
(qryCommittedCostsbyCostCode.cocBudgetCodeID = qryPCOs.pcoBudgetCodeID)) LEFT
JOIN qryPMAdjustments ON (qryCommittedCostsbyCostCode.prjCCPID =
qryPMAdjustments.adjCCPID) AND (qryCommittedCostsbyCostCode.cocBudgetCodeID =
qryPMAdjustments.bcBudgetCodeID);
Let me know if you need more information or explanation. And, thanks as
always for continued support!
Stacey
Contract Costs, and NonContract Costs by both Budget Code and Cost Code. In
my database cost codes roll up to budget codes in a heirarchy.
The query works great, which in turn creates a report I use frequently. I
would like to add two pieces of information to my report: Potential Change
Orders and Project Manager Adjustments. However, the issue is that PCOs and
PM Adjustments are only recorded at the budget code level, not the cost code
level. So right now my query will repeat the PCO and PM Adjustment values
each time the budget code shows up. Is there a way to tell my query to only
return those values once per budget code?
Here is the SQL:
SELECT qryCommittedCostsbyCostCode.prjCCPID,
qryCommittedCostsbyCostCode.prjParentProjectID,
qryCommittedCostsbyCostCode.prjProjectName,
qryCommittedCostsbyCostCode.cocBudgetCodeID,
qryCommittedCostsbyCostCode.bcBudgetCodeDesc,
qryCommittedCostsbyCostCode.vdVendorID,
qryCommittedCostsbyCostCode.conVendorName,
qryCommittedCostsbyCostCode.cocCostCodeID,
qryCommittedCostsbyCostCode.cocCostCodeDesc,
qryCommittedCostsbyCostCode.PhaseCodeID,
qryCommittedCostsbyCostCode.tdPhaseCodeID,
qryCommittedCostsbyCostCode.Contract, qryCommittedCostsbyCostCode.tdVendorID,
qryCommittedCostsbyCostCode.nonVendorName,
qryCommittedCostsbyCostCode.NonContractCosts,
qryPCOs.AllPCOs,
qryPMAdjustments.PMAdjustments
FROM (qryCommittedCostsbyCostCode LEFT JOIN qryPCOs ON
(qryCommittedCostsbyCostCode.prjCCPID = qryPCOs.pcoCCPID) AND
(qryCommittedCostsbyCostCode.cocBudgetCodeID = qryPCOs.pcoBudgetCodeID)) LEFT
JOIN qryPMAdjustments ON (qryCommittedCostsbyCostCode.prjCCPID =
qryPMAdjustments.adjCCPID) AND (qryCommittedCostsbyCostCode.cocBudgetCodeID =
qryPMAdjustments.bcBudgetCodeID);
Let me know if you need more information or explanation. And, thanks as
always for continued support!
Stacey