Making it More Specific

  • Thread starter Thread starter rokjock
  • Start date Start date
R

rokjock

The following is a query that I developed to pull interviews per state.
When I run the query a pop up box appears asking for the state. My problem
is no matter what state I type in, the same data is pulled. For example:
if I type in "IN" I get 25............if I type in "TX" I get 25. Any
thoughts on how I can make this query more specific so that is recognizes
the different states? I am new to SQL so any help would be most
appreciated! Thanks in advance.




Select TableName.State, Count(TableName.State) as NumberOfIntviewsPerState
From TableName
Group By TableName.State
 
rokjock said:
The following is a query that I developed to pull interviews per state.
When I run the query a pop up box appears asking for the state. My problem
is no matter what state I type in, the same data is pulled. For example:
if I type in "IN" I get 25............if I type in "TX" I get 25. Any
thoughts on how I can make this query more specific so that is recognizes
the different states? I am new to SQL so any help would be most
appreciated! Thanks in advance.




Select TableName.State, Count(TableName.State) as NumberOfIntviewsPerState
From TableName
Group By TableName.State
Click to expand...

The fact that it's prompting you when you have no parameters in your SQL (that I
can see) means that Access cannot find a field with that name.
 
Any suggestions to correct that?
Rick Brandt said:
The fact that it's prompting you when you have no parameters in your SQL (that I
can see) means that Access cannot find a field with that name.
Click to expand...
 
rokjock said:
Any suggestions to correct that?
Click to expand...

Did you build this query using the query design grid? If I create a query...

SELECT Field1 From Table1

....and there is no field in Table1 named Field1 I will get a prompt asking me to
enter the parameter value for "Field1". If Field1 _is_ in the table then I am
not prompted and the query simply runs.

If you have created a query without parameters and then you are prompted for a
parameter it suggests that you spelled the name of the field wrong.
 
Any suggestions to correct that?
Click to expand...

Well... the query as written doesn't use STATE as a criterion. "Typing
in "TX"" isn't going to do anything! A Count operation also counts
*records*, not states.

Try opening the query in design view; add the State field (a second
time); change the Totals row under this instance to WHERE, and type

[Enter two-letter state code:]

on the Criteria line under the field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top