Eliminating Duplicates from query

[Lori]

Okay, I've tried several times to find this answer may someone can help here.
I have a query that looks at three separate tables and pulls out financial
information based on the assigned "Cost Code". Some of these items will have
changes. The Original is locked and revisions can only be made by way of
"Approved Revisions". When a revision is made a new line entry for that Cost
Code is created (several times depending on the number of changes approved).
I need a way to total all items in a given cost code so that each of the cost
codes is only listed once and the resulting totals are summed.

Below is the current SQL view of what I have so far. This provides all of
the information I need with the exception of the totals above. HELP!!!

SELECT [PFC Table].[Project #], [PFC Table].[Cost Code], [PFC Table].Trade,
IIf(([CO#] Is Null),([Original Budget]),(0)) AS Original, IIf(([Subcontract
Status1]![ScopeChange?]=0) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Budget Changes], IIf(([Subcontract
Status1]![ScopeChange?]=Yes) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Approved Revisions], ([Original]+[Approved
Revisions]) AS [Revised Budget], [Subcontract Status1].[CO#],
IIf(([Subcontract Status Details]![ScopeChange]=Yes),(0),([Value])) AS
[Committed Original], IIf((([Committed
Original])>[Original]),(0),([Original]-[Committed Original])) AS [TBC
Original], ([Approved Revisions]) AS [Committed Revisions], (0) AS [TBC
Revisions], ([Committed Original]+[TBC Original]+[Committed Revisions]+[TBC
Revisions]) AS [Projected Final Cost], ([Revised Budget]-[Committed
Original]-[TBC Original]-[Committed Revisions]-[TBC Revisions]) AS
[Savings/(Overrun)], [Subcontract Status Details].Value, [Subcontract Status
Details].ScopeChange
FROM [Subcontract Status1] INNER JOIN ([Subcontract Status Details] INNER
JOIN [PFC Table] ON [Subcontract Status Details].[Cost Code] = [PFC
Table].[Cost Code]) ON ([Subcontract Status1].[Project#] = [PFC
Table].[Project #]) AND ([Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID)
ORDER BY [PFC Table].[Cost Code];

 

[Klatuu]

I think you can accomplish this with a Totals query.
Open your query in design view and click the Totals icon. It looks like the
Greek Letter Sigma. For all other fields, use Group By in the Total row of
the query builder, and use Sum for the cost.

 

[Lori]

tried that and it didn't work, I guess the question is how do I tell it to
sum by cost code?
--
Lori A. Pong

I think you can accomplish this with a Totals query.
Open your query in design view and click the Totals icon. It looks like the
Greek Letter Sigma. For all other fields, use Group By in the Total row of
the query builder, and use Sum for the cost.
--
Dave Hargis, Microsoft Access MVP

Okay, I've tried several times to find this answer may someone can help here.
I have a query that looks at three separate tables and pulls out financial
information based on the assigned "Cost Code". Some of these items will have
changes. The Original is locked and revisions can only be made by way of
"Approved Revisions". When a revision is made a new line entry for that Cost
Code is created (several times depending on the number of changes approved).
I need a way to total all items in a given cost code so that each of the cost
codes is only listed once and the resulting totals are summed.

Below is the current SQL view of what I have so far. This provides all of
the information I need with the exception of the totals above. HELP!!!

SELECT [PFC Table].[Project #], [PFC Table].[Cost Code], [PFC Table].Trade,
IIf(([CO#] Is Null),([Original Budget]),(0)) AS Original, IIf(([Subcontract
Status1]![ScopeChange?]=0) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Budget Changes], IIf(([Subcontract
Status1]![ScopeChange?]=Yes) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Approved Revisions], ([Original]+[Approved
Revisions]) AS [Revised Budget], [Subcontract Status1].[CO#],
IIf(([Subcontract Status Details]![ScopeChange]=Yes),(0),([Value])) AS
[Committed Original], IIf((([Committed
Original])>[Original]),(0),([Original]-[Committed Original])) AS [TBC
Original], ([Approved Revisions]) AS [Committed Revisions], (0) AS [TBC
Revisions], ([Committed Original]+[TBC Original]+[Committed Revisions]+[TBC
Revisions]) AS [Projected Final Cost], ([Revised Budget]-[Committed
Original]-[TBC Original]-[Committed Revisions]-[TBC Revisions]) AS
[Savings/(Overrun)], [Subcontract Status Details].Value, [Subcontract Status
Details].ScopeChange
FROM [Subcontract Status1] INNER JOIN ([Subcontract Status Details] INNER
JOIN [PFC Table] ON [Subcontract Status Details].[Cost Code] = [PFC
Table].[Cost Code]) ON ([Subcontract Status1].[Project#] = [PFC
Table].[Project #]) AND ([Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID)
ORDER BY [PFC Table].[Cost Code];

 

[Klatuu]

Yes, that was your question. You may need to break it into two queries. One
to sum the cost code. Then join that query to one that will present the
other data you want.

One question - Is the cost code found in more that one of the 3 tables?
If so, you may need a query for each table to pull all data and another
query to sum the results of the other 3 by cost code.
--
Dave Hargis, Microsoft Access MVP

tried that and it didn't work, I guess the question is how do I tell it to
sum by cost code?
--
Lori A. Pong

I think you can accomplish this with a Totals query.
Open your query in design view and click the Totals icon. It looks like the
Greek Letter Sigma. For all other fields, use Group By in the Total row of
the query builder, and use Sum for the cost.
--
Dave Hargis, Microsoft Access MVP

Okay, I've tried several times to find this answer may someone can help here.
I have a query that looks at three separate tables and pulls out financial
information based on the assigned "Cost Code". Some of these items will have
changes. The Original is locked and revisions can only be made by way of
"Approved Revisions". When a revision is made a new line entry for that Cost
Code is created (several times depending on the number of changes approved).
I need a way to total all items in a given cost code so that each of the cost
codes is only listed once and the resulting totals are summed.

Below is the current SQL view of what I have so far. This provides all of
the information I need with the exception of the totals above. HELP!!!

SELECT [PFC Table].[Project #], [PFC Table].[Cost Code], [PFC Table].Trade,
IIf(([CO#] Is Null),([Original Budget]),(0)) AS Original, IIf(([Subcontract
Status1]![ScopeChange?]=0) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Budget Changes], IIf(([Subcontract
Status1]![ScopeChange?]=Yes) And ([CO#] Is Not Null),([Subcontract Status
Details]![Value]),(0)) AS [Approved Revisions], ([Original]+[Approved
Revisions]) AS [Revised Budget], [Subcontract Status1].[CO#],
IIf(([Subcontract Status Details]![ScopeChange]=Yes),(0),([Value])) AS
[Committed Original], IIf((([Committed
Original])>[Original]),(0),([Original]-[Committed Original])) AS [TBC
Original], ([Approved Revisions]) AS [Committed Revisions], (0) AS [TBC
Revisions], ([Committed Original]+[TBC Original]+[Committed Revisions]+[TBC
Revisions]) AS [Projected Final Cost], ([Revised Budget]-[Committed
Original]-[TBC Original]-[Committed Revisions]-[TBC Revisions]) AS
[Savings/(Overrun)], [Subcontract Status Details].Value, [Subcontract Status
Details].ScopeChange
FROM [Subcontract Status1] INNER JOIN ([Subcontract Status Details] INNER
JOIN [PFC Table] ON [Subcontract Status Details].[Cost Code] = [PFC
Table].[Cost Code]) ON ([Subcontract Status1].[Project#] = [PFC
Table].[Project #]) AND ([Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID)
ORDER BY [PFC Table].[Cost Code];