Counting 01-25-08

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.
0
Utf
1/25/2008 4:12:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
645 Views

Similar Articles

[PageSpeed] 37

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" <Jeannie@discussions.microsoft.com> wrote in message 
news:136F9D35-D7EA-4A63-AD26-20994108B3EA@microsoft.com...
>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. 


0
John
1/25/2008 5:02:52 PM
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!

"John Spencer" wrote:

> 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" <Jeannie@discussions.microsoft.com> wrote in message 
> news:136F9D35-D7EA-4A63-AD26-20994108B3EA@microsoft.com...
> >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. 
> 
> 
> 
0
Utf
1/25/2008 5:30:02 PM
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
..

"Jeannie" <Jeannie@discussions.microsoft.com> wrote in message 
news:A6689DAF-47A7-4FF6-B641-87D126D4F9C5@microsoft.com...
> 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!
>
> "John Spencer" wrote:
>
>> 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" <Jeannie@discussions.microsoft.com> wrote in message
>> news:136F9D35-D7EA-4A63-AD26-20994108B3EA@microsoft.com...
>> >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.
>>
>>
>> 


0
John
1/25/2008 7:35:03 PM
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!

"John Spencer" wrote:

> 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
> ..
> 
> "Jeannie" <Jeannie@discussions.microsoft.com> wrote in message 
> news:A6689DAF-47A7-4FF6-B641-87D126D4F9C5@microsoft.com...
> > 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!
> >
> > "John Spencer" wrote:
> >
> >> 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" <Jeannie@discussions.microsoft.com> wrote in message
> >> news:136F9D35-D7EA-4A63-AD26-20994108B3EA@microsoft.com...
> >> >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.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/25/2008 8:34:03 PM
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
'====================================================


Jeannie wrote:
> 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!
> 
> "John Spencer" wrote:
> 
0
John
1/26/2008 7:20:33 PM
Reply:

Similar Artilces:

Outlook Offline 08-10-06
I recently purchased the book: "Working with Microsoft Dynamics CRM 3.0" We are running Small Business Server premium edition. I installed SQL server 2000 and sp4 on the server today and also installed the trial version of CRM 3.0 to determine if we are going to purchase. Now, when I open outlook, i have a message that says outlook is offline. What is causing this to occur? ...

Count number of times a text occurs
I want to count the number of times a text occurs in a range and then put 1,2,3,ect in a cell adjacent to the occurance. Apple 1 Apple 2 Apple 3 Apple 4 Banana 1 Banana 2 Banana 3 I can use the count function but it doesn't seem to help with the numbering. It seems like it should be simple but it probably isn't. Thanks for any help. -- Lee Try this, copied down =COUNTIF($H$2:H2,H2) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Lee" <lee@nospam.jjlgreenhouses.com> wrote in message news:%239S0dY8KJHA.4292@TK2MSFTNGP03.phx.gbl......

Outlook 2003 01-15-10
Installed Outlook 2003 on new laptop. I'm getting the license agreement screen every time I use Outlook. Why? In Windows 7 "gewhiz" wrote: > Installed Outlook 2003 on new laptop. I'm getting the license agreement > screen every time I use Outlook. Why? gewhiz;115458 Wrote: > Installed Outlook 2003 on new laptop. I'm getting the license > agreement > screen every time I use Outlook. Why? Did you run the setup as administrator? See if this helps: http://www.msoutlook.info/question/166 -- Brian Tillman [MVP-Outlook] h...

combining fields 12-08-09
I have a report that groups three fields together, 'conveyor type', 'conveyor name' and 'maint. records'. There are multiple 'conveyor names' and 'maint. records'for each 'conveyor type'. I have managed to group everything together that is repetitive (a lot of the 'maint. records are the same). So i have a report that has a 'conveyor type' heading with all the 'maint records' underneath very nicely. However I wish to list the 'conveyor names' side by side next to the 'conveyor type'heading. I ...

XPath: counting unique values
Hello, I would like to count the unique values of a specific element in an XPath statement. Let's say I have the next XML document ... <CLUB> <MEMBER> <NAME>Fred</NAME> <LOCATION>Canada</LOCATION> </MEMBER> <MEMBER> <NAME>Louis</NAME> <LOCATION>Belgium</LOCATION> </MEMBER> <MEMBER> <NAME>Gwendy</NAME> <LOCATION>Belgium</LOCATION> </MEMBER> <MEMBER> <NAME>Steve</NAME> <LOCATION>Portugal</LOCATION> &...

Count Formula #5
I will like to create a formula that will look in column a and if the field in column b matches it will give me a count. so for example if in a1 is blue and b1 is yellow than give me a count of 1 but they both must match blue and yellow? Thanks in advance for you assistance Dave Dave not really sure what you're after but here's two ideas for you =IF(AND(a1="blue",b1="yellow"),1,0) or =SUMPRODUCT(((A1:A100)="blue")*((B1:B100)="yellow")) Cheers JulieD "Dave" <anonymous@discussions.microsoft.com> wrote in message news:2c...

Outgoing traffic from port 25?
We have an Exchange 2003 Server. I have been noticing the firewall blocking outbound communication from port 25 on the Exchange server (inbound and outbound traffic to port 25 is allowed). It always appears to be in response to inbound spam, but I can't be 100% certain. NDR is turned off, and Delivery Receipts appear to go TO port 25, not FROM 25. From monitoring the port usage it appears the port 25 traffic is indeed coming from Exchange and not some other process. Is this normal? What is Exchange sending out from port 25? On Mon, 3 Apr 2006 13:39:03 -0700, Jeff Pines <Je...

Worksheet 501: Request full item count is timing out
I have noticed some discrepancies between my store inventory and my headquarters inventory. In order to bring the two inventories back in sync, I am trying to run worksheet 501: Request a full Item count. However, the worksheet keeps timing out. The worksheet will download, appear to be processing the worksheet for about 20 minutes, then return the error message: <<ExecuteCommand>> Error -2147217871: Timeout Expired UPDATE [ItemDynamic] WITH (TABLOCKX) SET [ItemDynamic].[SnapShotQuantity] = #UpdateRsToTable.[SnapShotQuantity], [ItemDynamic].[SnapShotQuantityCommitted] = #Upd...

Count #5
I have the following table for example Meat Chicken Vegetable is there a way to count in excell as total 3 items thanks =COUNTA(A1:A3) -- Gary''s Student - gsnu200803 "Hassan" wrote: > I have the following table for example > > Meat > > Chicken > > Vegetable > > is there a way to count in excell as total 3 items > > thanks > ...

Access 2007 Runtime 01-22-08
I am looking to update a major Access 2000 database to 2007 but I have been hearing that Access 2007 Runtime has some major bugs that might affect functionality or security. Does anyone know where I can find some clarification about this? Thanks DJ hi it is true that microsoft has been having some major bugs. I have been having some problems too with mine. if I were you, I'd find another database. "DJJ" <gemdjj@writeme.com> wrote in message news:koqlj.27772$8A4.8101@trnddc02... > I am looking to update a major Access 2000 database to 2007 but I have > b...

First Day of the month 03-25-10
Does anyone know how to have excel look at a cell that has a date that is formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as 01/01/2007 in B2, another words view as date as first day of the month. In B2: =DATE(YEAR(A2),MONTH(A2),1) -- Gary''s Student - gsnu201001 "Jen_T" wrote: > Does anyone know how to have excel look at a cell that has a date that is > formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as > 01/01/2007 in B2, another words view as date as first day of the month. On Thu, 25 Mar 20...

COUNT while eliminating duplicate values
I am trying to get a count for the number of employees in a range but want to eliminate duplicate values. Does anyone know the easiest wa to accomplish this ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way: http://j-walk.com/ss/excel/usertips/tip061.htm HTH Jason Atlanta,GA >-----Original Message----- > >I am trying to get a count for the number of employees in a range but I >want to eliminate duplicate values. Does anyone know the easiest way &g...

Data counting problem
I have a column of numbers 1 through 3000. Another column has a few numbers scatered throughout this range. I want to have a third column that runs the entire height of 3000 cells with either ones or zeros corresponding to the first column where if the number exists in the second column it gets a one and if it doesn't it gets a zero. Can anybody help me? I've been looking into CountIf stuff, but I can't seem to get the criteria to be any of the numbers in column 2. Thanks in advance. -- GlitchCog ------------------------------------------------------------------------ GlitchCog...

Business Cards 08-11-05
Has anyone used a business card scanner for entering contact data? If so, would you recommend one? I would suggest you check out this one http://www.cardscan.com/index.asp it works well with Microsoft CRM. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "ChannelRep" <ChannelRep@discussions.microsoft.com> wrote in message news:57B02D54-1D74-4D8F-9BA2-817315ACA444@microsoft.com... > Has anyone used a business card scanner for entering contact data? If so, > would you recommend one? ...

Install add-in with user defined functions 02-12-08
Hi, Looking for an easy way to make user defined functions available for any mdb to open in Access 2003. I thought: create and mda, write VBA code and use Tools - Add-in Manager. That doesn't work because of a USysRegInfo table ...? Do I realy have to go through that 'misery' or is a simpler / better way? Many thanks, Frans van Zelm ...

count related records
2 tables - 1) tickets and 2) messages. Tickets contains the main data and messages contains all the comments made relating to that ticket. How do I count the number of records (or messages) in the related table? So, I have 1 ticket in the system and 4 comments have been made in that ticket. I want to be able to take that number and append it to another ticket table showing "4" in that field. Appending the count to another ticket table is not the thing to do! Say you append 4 and then add another message. The count is now 5 but you only have 4 in the other table....

XPathNodeIterator.Count Performance Issues
Hi, I'm trying to compare two XML documents and i'm using XPath queries to select nodes. XPathNavigator's Select method runs fast enough and returns an XPathNodeIterator object. When i try to access this iterator's Count property the process extremely slows down. If i just iterate throgh 18.000 nodes and call XPathNavigator.Select to find equivalent node from the other document it doesn't even take 1 second. But in the same loop, when i try to access XPathNodeIterator.Count, (by accessing i mean just assigning its value to a variable) it takes about 5 minutes. I tried to ...

Excel in Office 2007 01-23-10
My worksheet has gone blank (full screen ok - normal blank) Help! Are you sure you have opened an existing file and not a new one? What happens if you type somethings in A1, save the file, close it, and reopne? -- www.stfx.ca/people/bliengme "Hamish" wrote: > My worksheet has gone blank (full screen ok - normal blank) Help! Perhaps the windowed sheet is moved to a location out of view? Try View, Arrange all, Tiled. Hamish wrote: > My worksheet has gone blank (full screen ok - normal blank) Help! ...

2 Guns 01-05.exe (7/7)
=ybegin part=7 line=128 size=1593914 name=2 Guns 01-05.exe =ypart begin=1497601 end=1593914 �C[=@a=}�/��`�5Ҵ�b��,�'���r�?������/��3NV���q���w�3x��`A���7,$D2=J_SOfu�4p�"��Z&N�褜#-n���̷�@��r�/��5�QҬу��� I͠)?{7@�%��[�ym�z��^�~�:���ڑYdkŝ��� Dx���@3��8�p+�"y��$E�ޓeْ2�b<=}STTY]�=@T9W�6����P�%��C��^o�x+1��c�5[O|N�I�KD xb��1Œ�bz$Ӭk�#⺶H���C:��h��,E�?�\?���~��0볈�j�+���T���ay�$���Y%��-��5���<=}�i+�?O�7�#�m�%V�d��9Bc>Qv_;=}�V��w� ��&�S�F6����3���/;^�n�x��=J���>�$��H���WK�\�-�v�+:dyw�=J��A���n���J-����J�haak�]w���e��Z�f~��O�...

Out of Office Assistant #25
Help....Out of office Assistant does not show up under Tools....why not, and how can I get it? "KJ" <anonymous@discussions.microsoft.com> wrote in message news:01ca01c3d612$e75a8350$a301280a@phx.gbl... > Help....Out of office Assistant does not show up under > Tools....why not, and how can I get it? if you do not connect to Exchange Server then you haven't got it. ...

Counting Problem #4
Hi everyone, My problem is with the COUNTA funciton. I have a formula "=IF(COUNTA(K4:Q4)>0,1,0)", set up so that if at least one cell has letter/value, then formula will return a result of 1. The cells, whic this equation counts, are also formulas as well. (Ex =IF('Protocol!$K4="","",'Protoco !$K4)) I want to be able to count the cells that have data, yet not coun those cells which have a blank result. Is there anyway to do this i Excel? Thanks and Have a great weekend, Pet -- peter_river -----------------------------------------------------------...

Counting Workdays Function
When i try to call the fuction i get a error I am using CalcWorkDays([DateDiverted],[dtmEnd]) The error says The expression is typed incorrectly or it too comples to be evaluated Does anyone know what wrong this I am a beginner VB Function CalcWorkDays(DateDiverted As Date, dtmEnd As Date) As Integer 'Calculated the number of working days between two dates 'DateDiverted - the first day to include in the range 'dtmEnd - the last day to include in the range 'Returns the number of working days between the two dates 'Both dates are counted if they are working days Dim in...

Count cells with data
I receive large databases each month. I have been able to format the data using PROPER. Though when I follow the instuctions from "3 formatting shortcuts" it calls for dragging the fill handle to the end of the new column to show all the converted cells. My databases are hundreds of names long! Is there a way to count the cells with text in them in advance - without scrolling down to the bottom - and then put the range into the =PROPER (range of cells with data) command? -- Thank you kindly You are using a second column to convert to Proper? You can Double-Click the Fill...

Number Counting
This is probably something simple, but I can't figure it out. I have columns with two digit numbers (00 thru 99). I want to write a formula that looks at this column and if any number is repeated, total the number of times it's repeated. Thanks so much for the help!! =countif(data-range,comparison-value) "DNA" wrote: > This is probably something simple, but I can't figure it out. > > I have columns with two digit numbers (00 thru 99). I want to write a > formula that looks at this column and if any number is repeated, total the > number of times it&#...

How do you stop blanks being counted as zero's
I have a database with two columns of single figures. I've been usin the IF function to identify whether a cell contains a zero. If Yes 1 i returned, if No 0. For some reason Excel 2000 seems to count the blank cells as zero's! Have i set up my page wrongly or is there some other reason? Anyone -- Message posted from http://www.ExcelForum.com Not sure why it's doing what it's doing. Count will only count numbers, CountA will count numbers and text, but both ignore blanks. Try using a CountIF function instead e.g. =COUNTIF(A1:A10,0) This will count the Zero's in a ra...