Unwanted Parameter Prompt

G

Guest

Hi. I am getting two unwanted parameter prompts on a query.

It's a simple Select query that has two fields on which I have placed a
Count. I then have a third field calculating the difference between these two
counts/totals and it's this that seems to be casuing the paramater prompt.
SQL is as follows:

SELECT TBL_CALENDER.GRAND_PRIX, Count(TBL_RACE_RESULTS.QUAL_POS) AS
CountOfQUAL_POS, Count(TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION_NUM) AS
CountOfCLASSIFICATION_NUM, ([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS
Retirements
FROM TBL_CLASSIFICATION_TEXT2NUM RIGHT JOIN (TBL_CALENDER RIGHT JOIN
TBL_RACE_RESULTS ON TBL_CALENDER.RACE_DATE = TBL_RACE_RESULTS.RACE_DATE) ON
TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION = TBL_RACE_RESULTS.CLASSIFICATION
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]);

When I run it prompts me for the two total fields. I leave them blank and it
runs fine, but I don't understand why the prompts are coming up.

Any suggestions?

Cheers

Matt
 
G

Guest

I think this will fix your problem:
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS Retirements;
 
G

Guest

Great, thank-you. So I just needed to add the "As" - the alias for the
expression?

Klatuu said:
I think this will fix your problem:
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS Retirements;


Matt D Francis said:
Hi. I am getting two unwanted parameter prompts on a query.

It's a simple Select query that has two fields on which I have placed a
Count. I then have a third field calculating the difference between these two
counts/totals and it's this that seems to be casuing the paramater prompt.
SQL is as follows:

SELECT TBL_CALENDER.GRAND_PRIX, Count(TBL_RACE_RESULTS.QUAL_POS) AS
CountOfQUAL_POS, Count(TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION_NUM) AS
CountOfCLASSIFICATION_NUM, ([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS
Retirements
FROM TBL_CLASSIFICATION_TEXT2NUM RIGHT JOIN (TBL_CALENDER RIGHT JOIN
TBL_RACE_RESULTS ON TBL_CALENDER.RACE_DATE = TBL_RACE_RESULTS.RACE_DATE) ON
TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION = TBL_RACE_RESULTS.CLASSIFICATION
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]);

When I run it prompts me for the two total fields. I leave them blank and it
runs fine, but I don't understand why the prompts are coming up.

Any suggestions?

Cheers

Matt
Click to expand...
Click to expand...
 
G

Guest

correct

Matt D Francis said:
Great, thank-you. So I just needed to add the "As" - the alias for the
expression?

Klatuu said:
I think this will fix your problem:
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS Retirements;


Matt D Francis said:
Hi. I am getting two unwanted parameter prompts on a query.

It's a simple Select query that has two fields on which I have placed a
Count. I then have a third field calculating the difference between these two
counts/totals and it's this that seems to be casuing the paramater prompt.
SQL is as follows:

SELECT TBL_CALENDER.GRAND_PRIX, Count(TBL_RACE_RESULTS.QUAL_POS) AS
CountOfQUAL_POS, Count(TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION_NUM) AS
CountOfCLASSIFICATION_NUM, ([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS
Retirements
FROM TBL_CLASSIFICATION_TEXT2NUM RIGHT JOIN (TBL_CALENDER RIGHT JOIN
TBL_RACE_RESULTS ON TBL_CALENDER.RACE_DATE = TBL_RACE_RESULTS.RACE_DATE) ON
TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION = TBL_RACE_RESULTS.CLASSIFICATION
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]);

When I run it prompts me for the two total fields. I leave them blank and it
runs fine, but I don't understand why the prompts are coming up.

Any suggestions?

Cheers

Matt
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Great, thank-you.

Klatuu said:
correct

Matt D Francis said:
Great, thank-you. So I just needed to add the "As" - the alias for the
expression?

Klatuu said:
I think this will fix your problem:
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS Retirements;


:

Hi. I am getting two unwanted parameter prompts on a query.

It's a simple Select query that has two fields on which I have placed a
Count. I then have a third field calculating the difference between these two
counts/totals and it's this that seems to be casuing the paramater prompt.
SQL is as follows:

SELECT TBL_CALENDER.GRAND_PRIX, Count(TBL_RACE_RESULTS.QUAL_POS) AS
CountOfQUAL_POS, Count(TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION_NUM) AS
CountOfCLASSIFICATION_NUM, ([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]) AS
Retirements
FROM TBL_CLASSIFICATION_TEXT2NUM RIGHT JOIN (TBL_CALENDER RIGHT JOIN
TBL_RACE_RESULTS ON TBL_CALENDER.RACE_DATE = TBL_RACE_RESULTS.RACE_DATE) ON
TBL_CLASSIFICATION_TEXT2NUM.CLASSIFICATION = TBL_RACE_RESULTS.CLASSIFICATION
GROUP BY TBL_CALENDER.GRAND_PRIX,
([CountOfQUAL_POS]-[CountOfCLASSIFICATION_NUM]);

When I run it prompts me for the two total fields. I leave them blank and it
runs fine, but I don't understand why the prompts are coming up.

Any suggestions?

Cheers

Matt
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

ACCESS - how do I apply a IIF = statement without parameter value prompt. The two column comparison is count 1
double prompt in union query 2
Too Many Parameter Prompts 1
query parameter order 4
Count operator leads to parameter value error 2
Parameter/Criteria settings for percentage expression field 2
Unwanted parameter prompt 4
Prompting for a parameter in a query when one isnt present 2

Top