Counting

[Jeannie]

I have two tables: issues and position.
The issues fields are populated with the lookup value from the position table.
I want a query that will count each "oppose" and each "support" for each
issue in the issue table.
I tried the DCount like this in the criteria for the issue I'm trying to
count opposes:
DCount("*","issues","field='Oppose' ")
and get an error that says "can't find the 'field' you entered in the
expression"
Your help is much appreciated! thanks.

 

[John Spencer]

Do you have a field named "Field" in the table Issues? If so, try
DCount("*","issues","[field]='Oppose'")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeannie]

Thanks for responding. No, I don't. I apologize, I'm rather new at this. My
issues table has only fields for each issue. Each issue issue data type is a
lookup to the position table with the fields oppose or support. What should I
do? Thanks!

Do you have a field named "Field" in the table Issues? If so, try
DCount("*","issues","[field]='Oppose'")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have two tables: issues and position.
The issues fields are populated with the lookup value from the position
table.
I want a query that will count each "oppose" and each "support" for each
issue in the issue table.
I tried the DCount like this in the criteria for the issue I'm trying to
count opposes:
DCount("*","issues","field='Oppose' ")
and get an error that says "can't find the 'field' you entered in the
expression"
Your help is much appreciated! thanks.

 

[John Spencer]

What fields do you have in the ISSUES table?
What fields do you have in the Position table?

One part of the problem may be that you have used the Lookup combobox
feature in your table. If you have done so, there is a really good chance
that what you see on the Issues table is not what is stored.

Try
DCOUNT("*","Issues","TheFieldNamethatStoresPostion = 1") and see if that
returns a result. If so the field is storing a number and displaying a text
value that it is getting from the position table. Note: I am NOT saying
that 1 = Oppose and 2=Support. You could find that out by looking at the
Position table.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for responding. No, I don't. I apologize, I'm rather new at this.
My
issues table has only fields for each issue. Each issue issue data type is
a
lookup to the position table with the fields oppose or support. What
should I
do? Thanks!

Do you have a field named "Field" in the table Issues? If so, try
DCount("*","issues","[field]='Oppose'")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have two tables: issues and position.
The issues fields are populated with the lookup value from the position
table.
I want a query that will count each "oppose" and each "support" for
each
issue in the issue table.
I tried the DCount like this in the criteria for the issue I'm trying
to
count opposes:
DCount("*","issues","field='Oppose' ")
and get an error that says "can't find the 'field' you entered in the
expression"
Your help is much appreciated! thanks.

 

[Jeannie]

There are four fields in the issues table titled by the name of the issue
(ex. Rebates).
The fields in the position table are oppose and support.

You are exactly right about how I have them set up. I still get an error.
Perhaps I am putting the query criteria in the wrong place. Oppose is 2, so I
am putting:
DCount("*","Issues","Rebates = 2")
I am putting his in the criteria under Rebates; should I be putting it
somewhere else? Thanks!

What fields do you have in the ISSUES table?
What fields do you have in the Position table?

One part of the problem may be that you have used the Lookup combobox
feature in your table. If you have done so, there is a really good chance
that what you see on the Issues table is not what is stored.

Try
DCOUNT("*","Issues","TheFieldNamethatStoresPostion = 1") and see if that
returns a result. If so the field is storing a number and displaying a text
value that it is getting from the position table. Note: I am NOT saying
that 1 = Oppose and 2=Support. You could find that out by looking at the
Position table.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for responding. No, I don't. I apologize, I'm rather new at this.
My
issues table has only fields for each issue. Each issue issue data type is
a
lookup to the position table with the fields oppose or support. What
should I
do? Thanks!

Do you have a field named "Field" in the table Issues? If so, try
DCount("*","issues","[field]='Oppose'")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have two tables: issues and position.
The issues fields are populated with the lookup value from the position
table.
I want a query that will count each "oppose" and each "support" for
each
issue in the issue table.
I tried the DCount like this in the criteria for the issue I'm trying
to
count opposes:
DCount("*","issues","field='Oppose' ")
and get an error that says "can't find the 'field' you entered in the
expression"
Your help is much appreciated! thanks.

 

[John Spencer]

You should be putting 2 in the criteria under rebates. And forget the
DCount.

If you are trying to countthe number of Oppose or Support values in the
four fields then you have a bit of a problem as your data tables are not
designed correctly.

The first thing you need to do is to use a UNION query to re-organize
the data. UNION queries cannot be constructed in the Query Design view
(query grid).

If you want to get the results for ONE fried at a time, you can use a
totals query.

Open a new query
-- Add your table
-- Add the Rebates field to the query fields TWICE
-- SELECT View: Totals from the Menu
-- Change GROUP BY to Count under ONE of the fields
-- Run the query and you should get a row for each response for this field.

If you wanted to get answer counts for all four fields, then you would
need a query something like the following. This query can only be built
in SQL view by typing

SELECT "Rebates" as ItemType, Rebates as TheResponse
FROM YourTable
UNION ALL
SELECT "SomeOther" as ItemType, SomeOther
FROM YourTable
UNION ALL
SELECT "Anotherquestion" as ItemType, AnotherQuestion
FROM YourTable
UNION ALL
SELECT "TheLastThing" as ItemType, TheLastThing
FROM YourTable

Now save that query and open a new query
-- Add the saved query to the new query
-- Add the ItemType field and TheResponse field (Twice) to the query
-- SELECT View: Totals from the Menu
-- Change GROUP BY to Count under ONE of the TheResponse fields


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================