Query Help Needed

G

Guest

Okay, I have a query that joins three tables. This query looks at several
fields and adds them, simple enough but whenever I view the query it asks for
the parameters for each of the fields where I've imput an expression. The end
result is always right but how do I stop it from popping up with the
parameter request for each field?

Below is the SQL view of this query. I hope someone can give me a simple
answer to stop this.

Thanks:

SELECT [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?], IIf(([Subcontract
Status Details]![Change Value]<>0),(0),([Original Budget])) AS Original,
IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change Value]),(0)) AS
[Approved Revisions], ([Original]+[Approved Revisions]) AS [Revised Budget],
[Subcontract Status Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)) AS Change,
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])) 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]-[Projected Final
Cost]) AS [Savings/(Overrun)]
FROM [PFC Table] INNER JOIN ([Subcontract Status1] INNER JOIN [Subcontract
Status Details] ON [Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID) ON [PFC Table].[Cost Code] = [Subcontract Status
Details].[Cost Code]
GROUP BY [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost
Code], [PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
IIf(([Subcontract Status Details]![Change Value]<>0),(0),([Original
Budget])), IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change
Value]),(0)), ([Original]+[Approved Revisions]), [Subcontract Status
Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)),
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])), IIf(([Committed
Original]>[Original]),(0),([Original]-[Committed Original])), ([Approved
Revisions]), (0), ([Committed Original]+[TBC Original]+[Committed
Revisions]+[TBC Revisions]), ([Revised Budget]-[Projected Final Cost])
ORDER BY [PFC Table].[Cost Code];
 
G

Guest

whenever I view the query it asks for the parameters for each of the fields
where I've imput an expression.
You did not say which parameters it ask for.
--
KARL DEWEY
Build a little - Test a little


Lori said:
Okay, I have a query that joins three tables. This query looks at several
fields and adds them, simple enough but whenever I view the query it asks for
the parameters for each of the fields where I've imput an expression. The end
result is always right but how do I stop it from popping up with the
parameter request for each field?

Below is the SQL view of this query. I hope someone can give me a simple
answer to stop this.

Thanks:

SELECT [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?], IIf(([Subcontract
Status Details]![Change Value]<>0),(0),([Original Budget])) AS Original,
IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change Value]),(0)) AS
[Approved Revisions], ([Original]+[Approved Revisions]) AS [Revised Budget],
[Subcontract Status Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)) AS Change,
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])) 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]-[Projected Final
Cost]) AS [Savings/(Overrun)]
FROM [PFC Table] INNER JOIN ([Subcontract Status1] INNER JOIN [Subcontract
Status Details] ON [Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID) ON [PFC Table].[Cost Code] = [Subcontract Status
Details].[Cost Code]
GROUP BY [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost
Code], [PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
IIf(([Subcontract Status Details]![Change Value]<>0),(0),([Original
Budget])), IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change
Value]),(0)), ([Original]+[Approved Revisions]), [Subcontract Status
Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)),
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])), IIf(([Committed
Original]>[Original]),(0),([Original]-[Committed Original])), ([Approved
Revisions]), (0), ([Committed Original]+[TBC Original]+[Committed
Revisions]+[TBC Revisions]), ([Revised Budget]-[Projected Final Cost])
ORDER BY [PFC Table].[Cost Code];
Click to expand...
 
G

Guest

every field where I have written an expression asks for the parameters
everytime I try to view the query. Unless it's a field directly from the
table it will prompt me for the parameters, i.e. original, revised budget,
etc. These are the only ones that don't:
[PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
Click to expand...
Click to expand...
--
Lori A. Pong


KARL DEWEY said:
where I've imput an expression.
You did not say which parameters it ask for.
--
KARL DEWEY
Build a little - Test a little


Lori said:
Okay, I have a query that joins three tables. This query looks at several
fields and adds them, simple enough but whenever I view the query it asks for
the parameters for each of the fields where I've imput an expression. The end
result is always right but how do I stop it from popping up with the
parameter request for each field?

Below is the SQL view of this query. I hope someone can give me a simple
answer to stop this.

Thanks:

SELECT [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?], IIf(([Subcontract
Status Details]![Change Value]<>0),(0),([Original Budget])) AS Original,
IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change Value]),(0)) AS
[Approved Revisions], ([Original]+[Approved Revisions]) AS [Revised Budget],
[Subcontract Status Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)) AS Change,
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])) 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]-[Projected Final
Cost]) AS [Savings/(Overrun)]
FROM [PFC Table] INNER JOIN ([Subcontract Status1] INNER JOIN [Subcontract
Status Details] ON [Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID) ON [PFC Table].[Cost Code] = [Subcontract Status
Details].[Cost Code]
GROUP BY [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost
Code], [PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
IIf(([Subcontract Status Details]![Change Value]<>0),(0),([Original
Budget])), IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change
Value]),(0)), ([Original]+[Approved Revisions]), [Subcontract Status
Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)),
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])), IIf(([Committed
Original]>[Original]),(0),([Original]-[Committed Original])), ([Approved
Revisions]), (0), ([Committed Original]+[TBC Original]+[Committed
Revisions]+[TBC Revisions]), ([Revised Budget]-[Projected Final Cost])
ORDER BY [PFC Table].[Cost Code];
Click to expand...
Click to expand...
 
G

Guest

Do not use a calculated field name in the same query that does the
calculation. You have to use the calculation instead.

Or use the output in a second query.
--
KARL DEWEY
Build a little - Test a little


Lori said:
every field where I have written an expression asks for the parameters
everytime I try to view the query. Unless it's a field directly from the
table it will prompt me for the parameters, i.e. original, revised budget,
etc. These are the only ones that don't:
[PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
Click to expand...
Click to expand...
--
Lori A. Pong


KARL DEWEY said:
whenever I view the query it asks for the parameters for each of the fields
Click to expand...
where I've imput an expression.
You did not say which parameters it ask for.
--
KARL DEWEY
Build a little - Test a little


Lori said:
Okay, I have a query that joins three tables. This query looks at several
fields and adds them, simple enough but whenever I view the query it asks for
the parameters for each of the fields where I've imput an expression. The end
result is always right but how do I stop it from popping up with the
parameter request for each field?

Below is the SQL view of this query. I hope someone can give me a simple
answer to stop this.

Thanks:

SELECT [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost Code],
[PFC Table].Trade, [Subcontract Status1].[ScopeChange?], IIf(([Subcontract
Status Details]![Change Value]<>0),(0),([Original Budget])) AS Original,
IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change Value]),(0)) AS
[Approved Revisions], ([Original]+[Approved Revisions]) AS [Revised Budget],
[Subcontract Status Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)) AS Change,
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])) 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]-[Projected Final
Cost]) AS [Savings/(Overrun)]
FROM [PFC Table] INNER JOIN ([Subcontract Status1] INNER JOIN [Subcontract
Status Details] ON [Subcontract Status1].SUBID = [Subcontract Status
Details].SUBID) ON [PFC Table].[Cost Code] = [Subcontract Status
Details].[Cost Code]
GROUP BY [PFC Table].PFCID, [PFC Table].[Project #], [PFC Table].[Cost
Code], [PFC Table].Trade, [Subcontract Status1].[ScopeChange?],
IIf(([Subcontract Status Details]![Change Value]<>0),(0),([Original
Budget])), IIf(([ScopeChange?]=-1),([Subcontract Status Details]![Change
Value]),(0)), ([Original]+[Approved Revisions]), [Subcontract Status
Details].Value, IIf(([Subcontract Status Details]![Change
Value]<>0),([Subcontract Status Details]![Change Value]),(0)),
IIf(([Subcontract Status Details]![ScopeChange]=-1),([Subcontract Status
Details]![Value]),([Subcontract Status Details]![Value]+[Subcontract Status
Details]![ScopeChange])), IIf(([Committed
Original]>[Original]),(0),([Original]-[Committed Original])), ([Approved
Revisions]), (0), ([Committed Original]+[TBC Original]+[Committed
Revisions]+[TBC Revisions]), ([Revised Budget]-[Projected Final Cost])
ORDER BY [PFC Table].[Cost Code];
Click to expand...
Click to expand...
Click to expand...
 

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

Proper use of update query 2
Create Index in Make Table Query 4
is this query possible? 3
PLEASE HELP!!! NULL field ? 2
query for latest course 1
Aggregate query help 1
Complex Append Query 1
Code works well in A97 and Xp, but bombs in A2000. 8

Top