Parameter Value Entry

[Thorson]

I created a query which has 2 simple equations

The first is to add "StartingTotal" and the count of "ProtocolNumber"
together, the equation used is: TotalEnrolled:
[StartingTotal]+[CountOfProtocolNumber]

The second equation is to subtract the first equation "TotalEnrolled" from
the "MaxAnimals"

When I run the query everything works like it should, except that two
seperate boxes to Enter the "Parameter Value" pop-up. One box is to enter
the Parameter Value for "CountOfProtocolNumber" and the other is to enter the
parameter value for "TotalEnrolled". It doesn't matter what number is put in
the parameter value entry boxes the query always calculates the same value.

It appears that the problem is that the field name for the column doesn't
exactly match the field name for the equation, but the equations still work.
Is there any way to prevent the Parameter Value Entry boxes from popping up?

 

[Michel Walsh]

You probably have an ORDER BY clause (sort) using the alias. With Jet, the
alias defined in the SELECT clause cannot be used in the ORDER BY clause
(except in an UNION query).


Vanderghast, Access MVP

 

[Thorson]

I am just a novice and I'm not sure that I exactly understand what you are
refering to. I looked up union queries and it appears it is a query using 2
or more other queries. All of the data from my query is only from 3 seperate
tables, 2 of which have a joint relationship.

All of my fields in my query have the "total" set to "Group By" except for
the "ProtocolNumber" Field. The "ProtocolNubmer" field (which is used in the
first equation) has the "total" set to "Count" to count how many entries have
been made for each protocol. None of the fields are sorted or have required
criteria.

You probably have an ORDER BY clause (sort) using the alias. With Jet, the
alias defined in the SELECT clause cannot be used in the ORDER BY clause
(except in an UNION query).


Vanderghast, Access MVP

I created a query which has 2 simple equations

The first is to add "StartingTotal" and the count of "ProtocolNumber"
together, the equation used is: TotalEnrolled:
[StartingTotal]+[CountOfProtocolNumber]

The second equation is to subtract the first equation "TotalEnrolled" from
the "MaxAnimals"

When I run the query everything works like it should, except that two
seperate boxes to Enter the "Parameter Value" pop-up. One box is to enter
the Parameter Value for "CountOfProtocolNumber" and the other is to enter
the
parameter value for "TotalEnrolled". It doesn't matter what number is put
in
the parameter value entry boxes the query always calculates the same
value.

It appears that the problem is that the field name for the column doesn't
exactly match the field name for the equation, but the equations still
work.
Is there any way to prevent the Parameter Value Entry boxes from popping
up?

 

[Lord Kelvan]

TotalEnrolled: [StartingTotal]+Count([ProtocolNumber])

newmaxanimals: [MaxAnimals]-([StartingTotal]+Count([ProtocolNumber]))

thoes pop ups were because you entered something into your query that
didnt exist in your table

i think michel was way off and didnt understand what was going on (no
offence michel)

as a note i am very happy to see a new databse user NOT putting spaces
in field names congrats

so in conclusion try thoes fields in different queries as another note
it woudl pay not to put in any other fields into the query because it
seems you want a total value and you wont get that if you put in other
values even with group by unless you are trying to get the total for a
group of animals

ie total cats or total dogs

rather than the total of all animals

hope this helps

Regards
Kelvan

 

[Michel Walsh]

Try the following in Northwind, and you will get prompted for "aa" :

SELECT [freight]+1 AS aa
FROM Orders
ORDER BY aa;


Since Jet does not recognize the alias in ORDER BY. You HAVE TO use
something like:


SELECT [freight]+1 AS aa
FROM Orders
ORDER BY [freight]+1;


or


SELECT *
FROM ( SELECT [freight]+1 AS aa
FROM Orders) AS x
ORDER BY aa


The only place you can, with Jet, use an alias in the order by clause is
when you use an UNION query:


SELECT freight + 1 AS aa
FROM Orders
UNION ALL
SELECT freight - 1 AS aa
FROM Orders
ORDER BY aa





Vanderghast, Access MVP

 

[Thorson]

Thanks for the help. I ended up having to split my query up into 3 different
ones, but it works great now.

Thanks!