Query Help

  • Thread starter Stacey Crowhurst
  • Start date
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
 
J

Jeff Boyce

Stacey

No... and it won't matter. Instead of relying on the raw output of the
query, use your report.

You can specify in the report to Group By, or to not show duplicate values,
so the 'extra' info in the query doesn't have to show up in the report...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP
 
S

Stacey Crowhurst

Got it! Thank you very much for your help!!

Jeff Boyce said:
Stacey

No... and it won't matter. Instead of relying on the raw output of the
query, use your report.

You can specify in the report to Group By, or to not show duplicate values,
so the 'extra' info in the query doesn't have to show up in the report...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP
 

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

Top