Parameter pop out why?

[Reshmi mohankumar]

i am getting parameter pop outs for below query!, will any body find out where it occurs?

SELECT DISTINCTROW Sum(Nz([SumOfPURE])) AS RCPT_PURE, Sum(Nz([SumOfMC])) AS RCPTMC, Sum(Nz([SumOfST_AMT])) AS RCPTST_AMT, Sum(Nz([SumOfPAYPURE])) AS PAY_PURE, Sum(Nz([SumOfMC_AMT])) AS PAYMC_AMT, Sum(Nz([SumOfPAYST_AMT])) AS PAYST_AMT, SMITH.SMITH, [RCPT_PURE]-[PAY_PURE] AS [PURE BAL], [RCPTMC]-[PAYMC_AMT] AS [MC BAL], [RCPTST_AMT]-[PAYST_AMT] AS [ST AMT BAL]
FROM (SMITH LEFT JOIN QRY_PAY_PURE_MC_sum_ledger ON SMITH.[SMITH] = QRY_PAY_PURE_MC_sum_ledger.[SMITH]) LEFT JOIN QRY_RCPT_PURE_MC_sum_ledger ON SMITH.[SMITH] = QRY_RCPT_PURE_MC_sum_ledger.[SMITH]
GROUP BY SMITH.SMITH, [RCPT_PURE]-[PAY_PURE], [RCPTMC]-[PAYMC_AMT], [RCPTST_AMT]-[PAYST_AMT], QRY_RCPT_PURE_MC_sum_ledger.SMITH, QRY_PAY_PURE_MC_sum_ledger.SMITH;

 

[arnelgp]

What does the pop up says?

 

[The_Doc_Man]

Parameter requests invariably mean that you have spelled a field name wrong in your query or, stated another way, one of those fields in your query does not exist in the named tables. OR the name exists in more than one table and is therefore ambiguous.

Check the spelling and host table for every query field. One of them is wrong.

 

[Reshmi mohankumar]

Parameter requests invariably mean that you have spelled a field name wrong in your query or, stated another way, one of those fields in your query does not exist in the named tables. OR the name exists in more than one table and is therefore ambiguous.

Check the spelling and host table for every query field. One of them is wrong.

I observed what u said. But it pops out at all calculate Fields .
And if I hit ok for all pop outs , calculations done nicely.
How can I prevent those pop outs?

 

[arnelgp]

Say you have calc field:

Expr1: field1-field2
Expr2: sum(field3)

Dont use expr2-expr1, instead use the base calculation:

Sum(field3)-(field1-field2)

 

[isladogs]

There is a lot wrong with this query

Firstly, as arnel suggested, you are trying to use several calculated fields to then calculate other fields. That's highly unlikely to work.

The next issue is that two fields are sums of sums to which you've applied Nz without stating what the value should be replaced by if it's null

Furthermore, you are trying to group by three items each of which are differences between 2 calculated values. It's hard enough to describe.
I would be incredulous if Access understood what to do

I think you need to start again. Remake this whole query one step at a time, checking it does what you need at each step

 

[Mark_]

You may find it MUCH EASIER to make one query that gives you your basic data (drawn off of the tables) then a second query that references the first to give you your totaling.

The reason is your first query will return ONLY the data set you want to work with so you can verify you really do have what you need (as well as how you want to handle null values) and the second is then working off of a dataset that you can easily verify against.

As the others have posted, you are giving ACCESS a bit much to work with so it gets really difficult for you to work through problems.

 

[The_Doc_Man]

I'm with Mark and Colin.

Take a "layered" query approach. The execution efficiency might be less, but if you have fewer than a thousand records, your human perception won't recognize the delay as observed on a GHz computer.

Form the sums first. Go ahead and name the summation columns. Also, that "DISTINCTROW" needs to not be there.

DISTINCTROW needs to be in the next layer, perhaps where you compute your difference fields and name them.

Then in a third layer you can implement your ORDER BY.

Since you don't have a visible WHERE clause, that makes it easy. I'm going to presume that the JOIN statements are being used to do the WHERE clause by matching contents of two tables or a table and a query, which is perfectly fine.

This is a case where "Divide and conquer" is good advice even if it DID come from Julius Caesar, who has been dead for a while. Some advice just doesn't get old.