How to avoid prompts

[Guest]

Hello to everyone

I have a query based on this SQL:
SELECT customerstats.all_customers AS ASB1, customerstats_1.all_customers AS
ASB2, 25000 AS GSB, Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2])))) AS ASB,
[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts], Count(vodstats.ID) AS Tot
FROM....
WHERE....
GROUP BY...

When I run the query, I get prompted to enter values for Tot, ASB1, ASB2 .
How can I avoid those prompts?

Thanks a lot.

 

[Guest]

Don't use the new fields as fields in the query, use there formula

SELECT customerstats.all_customers AS ASB1, customerstats_1.all_customers AS
ASB2, 25000 AS GSB,
Int((0.5*(customerstats.all_customers+IIf(IsNull(customerstats_1.all_customers),0,customerstats_1.all_customers)))) AS ASB,
Count(vodstats.ID)*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross Receipts], Count(vodstats.ID) AS Tot
FROM....
WHERE....
GROUP BY...

 

[Baz]

You can't refer to output columns by their aliases from within the query,
you will have to repeat the full expression. For example, instead of this:

[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts]

you must do this:

Count(vodstats.ID)*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]
) AS [Gross Receipts]

 

[Guest]

Which means that If I want to use [Gross Receipts] in another I have to use
the whole expression again, huh? That will be a big query

Thanks anyway guys

Ο χÏήστης "Baz" έγγÏαψε:

You can't refer to output columns by their aliases from within the query,
you will have to repeat the full expression. For example, instead of this:

[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts]

you must do this:

Count(vodstats.ID)*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]
) AS [Gross Receipts]

Hello to everyone

I have a query based on this SQL:
SELECT customerstats.all_customers AS ASB1, customerstats_1.all_customers AS
ASB2, 25000 AS GSB, Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2])))) AS ASB,
[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts], Count(vodstats.ID) AS Tot
FROM....
WHERE....
GROUP BY...

When I run the query, I get prompted to enter values for Tot, ASB1, ASB2 .
How can I avoid those prompts?

Thanks a lot.

 

[Baz]

Yes and Yes!

Which means that If I want to use [Gross Receipts] in another I have to use
the whole expression again, huh? That will be a big query

Thanks anyway guys

? ??????? "Baz" ???????:

You can't refer to output columns by their aliases from within the query,
you will have to repeat the full expression. For example, instead of this:

[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts]

you must do this:

Count(vodstats.ID)*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]
) AS [Gross Receipts]

Hello to everyone

I have a query based on this SQL:
SELECT customerstats.all_customers AS ASB1,

customerstats_1.all_customers
AS

ASB2, 25000 AS GSB, Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2]))))

AS
ASB,

[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts], Count(vodstats.ID) AS Tot
FROM....
WHERE....
GROUP BY...

When I run the query, I get prompted to enter values for Tot, ASB1, ASB2 .
How can I avoid those prompts?

Thanks a lot.

 

[Keith Wilby]

Which means that If I want to use [Gross Receipts] in another I have to
use
the whole expression again, huh? That will be a big query

You could always create the aliases in one stored query and then do the
calcs in another query based on the first one.

Keith.
www.keithwilby.com

 

[Gary Walter]

Hi Mario,

Please show the SQL of your query
in its entirety...and is this a Jet query?

In a Jet query (not SQL Server) you
should be able to refer to an alias in
an expression in the SELECT clause
if that alias "came into being" prior to the
expression.

that's why [Tot] "failed" because you
had it listed in SELECT clause *after*
the expression that tried to use it.

the expression did not yet know what
it was.

my guess is that the ASB1/ASB2 problem
came about because you tried to use the
aliases in WHERE or GROUP BY clause.

As a general rule (in Jet), the SELECT clause
get "processed" last, so those aliases have not
"come into being" when the WHERE and
GROUP BY clauses get processed.

SELECT
customerstats.all_customers AS ASB1,
customerstats_1.all_customers AS ASB2,
25000 AS GSB,
Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2])))) AS ASB,
Count(vodstats.ID) AS Tot,
[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts]
FROM....
WHERE....
GROUP BY

customerstats.all_customers,
customerstats_1.all_customers

 

[Gary Walter]

One important point...

implicit in the query rewrite I gave you
was that:

the Totals row under the expression
was changed from "Group By" to
"Expression"

that was why it did not appear in
GROUP BY clause.


in the QBE

Field: ASB: Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2]))))
Table:
Total: Expression
Sort:
Show:
Criteria:
or:

good luck,

gary

Hi Mario,

Please show the SQL of your query
in its entirety...and is this a Jet query?

In a Jet query (not SQL Server) you
should be able to refer to an alias in
an expression in the SELECT clause
if that alias "came into being" prior to the
expression.

that's why [Tot] "failed" because you
had it listed in SELECT clause *after*
the expression that tried to use it.

the expression did not yet know what
it was.

my guess is that the ASB1/ASB2 problem
came about because you tried to use the
aliases in WHERE or GROUP BY clause.

As a general rule (in Jet), the SELECT clause
get "processed" last, so those aliases have not
"come into being" when the WHERE and
GROUP BY clauses get processed.

SELECT
customerstats.all_customers AS ASB1,
customerstats_1.all_customers AS ASB2,
25000 AS GSB,
Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2])))) AS ASB,
Count(vodstats.ID) AS Tot,
[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS [Gross
Receipts]
FROM....
WHERE....
GROUP BY

customerstats.all_customers,
customerstats_1.all_customers

I have a query based on this SQL:
SELECT customerstats.all_customers AS ASB1, customerstats_1.all_customers
AS
ASB2, 25000 AS GSB, Int((0.5*([ASB1]+IIf(IsNull([ASB2]),0,[ASB2])))) AS
ASB,
[Tot]*IIf(IsNull([GuaranteedFeePerBuy]),0,[GuaranteedFeePerBuy]) AS
[Gross
Receipts], Count(vodstats.ID) AS Tot
FROM....
WHERE....
GROUP BY...

When I run the query, I get prompted to enter values for Tot, ASB1, ASB2
.
How can I avoid those prompts?

Thanks a lot.