Counting 12-28-07

I have a data element in a table named Results.  This is defined as a number 
field.  The Values go from 0 to 11.  
The problem is I need to count a certain value but sometimes that value has 
not been chosen and that, of course, returns nothing.  I would like it to 
return the result value I am looking for and a '0'.  Any ideas?
0
Utf
12/28/2007 9:29:02 PM
access 16762 articles. 3 followers. Follow

5 Replies
818 Views

Similar Articles

[PageSpeed] 32

Post your SQL.
-- 
KARL DEWEY
Build a little - Test a little


"Bunky" wrote:

> I have a data element in a table named Results.  This is defined as a number 
> field.  The Values go from 0 to 11.  
> The problem is I need to count a certain value but sometimes that value has 
> not been chosen and that, of course, returns nothing.  I would like it to 
> return the result value I am looking for and a '0'.  Any ideas?
0
Utf
12/28/2007 9:39:02 PM
SELECT Count([Distinct Waitlist Desired].Results) AS CountOfResults, 
IIf([CountOfResults] Is Null,0,[CountOfResults]) AS ZeroResults, [Result 
Table].[Result number]
FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result 
Table].[Result number] = [Distinct Waitlist Desired].Results
WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist 
Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or 
([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist 
Desired].Preferred)='M') AND (([Distinct Waitlist Desired].[Date Worked]) 
Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))))
GROUP BY [Result Table].[Result number]
HAVING ((([Result Table].[Result number])=4));

The dates I am using are a begin of 11/01/07 and an end of 12/01/07.

"KARL DEWEY" wrote:

> Post your SQL.
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Bunky" wrote:
> 
> > I have a data element in a table named Results.  This is defined as a number 
> > field.  The Values go from 0 to 11.  
> > The problem is I need to count a certain value but sometimes that value has 
> > not been chosen and that, of course, returns nothing.  I would like it to 
> > return the result value I am looking for and a '0'.  Any ideas?
0
Utf
12/28/2007 9:43:01 PM
On Fri, 28 Dec 2007 13:29:02 -0800, Bunky <Bunky@discussions.microsoft.com>
wrote:

>I have a data element in a table named Results.  This is defined as a number 
>field.  The Values go from 0 to 11.  
>The problem is I need to count a certain value but sometimes that value has 
>not been chosen and that, of course, returns nothing.  I would like it to 
>return the result value I am looking for and a '0'.  Any ideas?

You may need a little twelve-row auxiliary table with one record for each
value from 0 through 11. If you Left Join this table to your Results table and
use this query as the basis for your count you should get the desired result.

             John W. Vinson [MVP]
0
John
12/28/2007 9:50:12 PM
As John suggested I filled Results table and removed criteria.

SELECT [Result Table].[Result number], Sum(IIf([Results] Is Not Null,1,0)) 
AS [Result Count], Sum(IIf([Results] Is Null,1,0)) AS ZeroResults
FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result 
Table].[Result number] = [Distinct Waitlist Desired].Results
WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist 
Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or 
([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist 
Desired].Preferred)='M' Or ([Distinct Waitlist Desired].Preferred) Is Null) 
AND (([Distinct Waitlist Desired].[Date Worked]) Between [Begin Date:] And 
DateAdd("d",1,CDate([End Date:])) Or ([Distinct Waitlist Desired].[Date 
Worked]) Is Null))
GROUP BY [Result Table].[Result number];

-- 
KARL DEWEY
Build a little - Test a little


"Bunky" wrote:

> SELECT Count([Distinct Waitlist Desired].Results) AS CountOfResults, 
> IIf([CountOfResults] Is Null,0,[CountOfResults]) AS ZeroResults, [Result 
> Table].[Result number]
> FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result 
> Table].[Result number] = [Distinct Waitlist Desired].Results
> WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist 
> Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or 
> ([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist 
> Desired].Preferred)='M') AND (([Distinct Waitlist Desired].[Date Worked]) 
> Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))))
> GROUP BY [Result Table].[Result number]
> HAVING ((([Result Table].[Result number])=4));
> 
> The dates I am using are a begin of 11/01/07 and an end of 12/01/07.
> 
> "KARL DEWEY" wrote:
> 
> > Post your SQL.
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Bunky" wrote:
> > 
> > > I have a data element in a table named Results.  This is defined as a number 
> > > field.  The Values go from 0 to 11.  
> > > The problem is I need to count a certain value but sometimes that value has 
> > > not been chosen and that, of course, returns nothing.  I would like it to 
> > > return the result value I am looking for and a '0'.  Any ideas?
0
Utf
12/28/2007 11:01:00 PM
Thank you both!   I knew of that fix but 1) did not want to have to add 
another table and 2) had trouble getting it working correctly.  It is working 
to the 'T' now. 

Have a Happy New Year!

"John W. Vinson" wrote:

> On Fri, 28 Dec 2007 13:29:02 -0800, Bunky <Bunky@discussions.microsoft.com>
> wrote:
> 
> >I have a data element in a table named Results.  This is defined as a number 
> >field.  The Values go from 0 to 11.  
> >The problem is I need to count a certain value but sometimes that value has 
> >not been chosen and that, of course, returns nothing.  I would like it to 
> >return the result value I am looking for and a '0'.  Any ideas?
> 
> You may need a little twelve-row auxiliary table with one record for each
> value from 0 through 11. If you Left Join this table to your Results table and
> use this query as the basis for your count you should get the desired result.
> 
>              John W. Vinson [MVP]
> 
0
Utf
12/31/2007 9:31:01 PM
Reply:

Similar Artilces:

Total daily e-mail count?
This might seem like an odd question, but my CIO is asking for a total e-mail count for messages both sent and/or received by our organization on any given day. I'm currently using Exchange Server 2003. Can this information be found within System Manager, or will a third party utility be needed to accumulate such information? Thanks. Perfmon will give you some basic metrics. Otherwise have a look at MessageStats from http://quest.com/messagestats. Fully-functional 30-day eval on the site. -- David Sengupta M.T.S., B.Sc., MVP, MCSE, MCSE 2000, CCA Ottawa, Canada Exchange Rep...

How to incorporate page counts in TOC
I need to find out how to incorporate each document’s total page count into the Table of Contents instead of the current page number. In my case, we have 20-60 separate document files that need a single TOC that states the total number of pages for each file. Example: Section No. of Pages 100 3 200 2 300 10 I forgot to mention, I am using MS Word 2007 "Krystal" wrote: > I need to find out how to incorporate each document’s total page count into > the Table of Contents instead of the current page number. > > In my case, we have 20-60 s...

Questions about rebuilding a db 03-28-10
Lateley I've notices my database freezing from time to time, so I tried the following... a) created a new data base b) imported all tables, queries, forms, ...... ,modules c) reset the References d) reset the Startup options e) compile Everything is working great now, but I have a few questions about this process. Did I miss any crucial steps? (don't want any surprises later) Is there a prefered order to do these steps? Finally, the database is now about 30% smaller, even though I was regularly compiling, compacting/repairing the former one. I'd like to know w...

Outlook 2007 12-13-09
How do you display Tasks in To-Do Bar using .pst file? "spikey789" <spikey789@discussions.microsoft.com> wrote in message news:A3D07AF5-AB82-4F01-9153-4D097DD392E2@microsoft.com... > How do you display Tasks in To-Do Bar using .pst file? Don't quite understand where the "pst file" bit comes in, but have you right-clicked on "To Do Bar" and made sure that Tasks is checked? ...

work 04-30-07
Michael Johnson On August 1, 1996, Michael Johnson electrified the world by winning an Olympic gold medal in the 200-meter dash with a time of 19.32, shattering his own world record and capping one of the greatest individual track and field performances in Olympic history. Johnson became the first person in history to win both the 200 meters and 400 meters in the same Olympic Games. These performances in front of an American audience highlight a decorated career for Johnson, who will be competing in his third Goodwill Games in 1998. Some of Johnsons accomplishments include: � W...

Migration #12
Hi, there. I want to do migration from Notes to Exchange 2007, Where can I find some resources about this, step by step guide is best. Thanks very much! ITUser, kejia.li@163.com Hi, This link should help you: http://www.microsoft.com/technet/interopmigration/collaboration/default.mspx Cheers Nathan "ITUser" <kejia.li@163.com> wrote in message news:EAFE3DF5-E4BA-4503-8115-894AF8AE108C@microsoft.com... > Hi, there. > > I want to do migration from Notes to Exchange 2007, Where can I find some > resources about this, step by step guide is best. > &g...

delete record 03-06-07
Hi,I have a button in a form that delete that current record.before it deleted, access ask me if I'm sure I want to delete.Can I cancel this message and just delete (I want to put my own question before)thanks. On Mar 6, 5:13 am, "Lior Montia" <l...@matrix.co.il> wrote:> Hi,>> I have a button in a form that delete that current record.> before it deleted, access ask me if I'm sure I want to delete.> Can I cancel this message and just delete (I want to put my own question> before)>> thanks.These occur when the Tools>Options>Confirm Record Ch...

Combo Box 12-26-07
Is there a way to have more than one bound column in a combo box? No. What is it you are trying to do? Perhaps we can offer a suggestion. -- Dave Hargis, Microsoft Access MVP "Sharon" wrote: > Is there a way to have more than one bound column in a combo box? On Wed, 26 Dec 2007 07:02:00 -0800, Sharon wrote: > Is there a way to have more than one bound column in a combo box? No. Now if you tell us why you think you need more than one bound column, perhaps some one can help with an alternative solution. -- Fred Please respond only to this newsgroup. I do not reply to pe...

Macros #12
I didn't see a group for Excel Macros so I'm posting here. I created a macro, saved it in the workbook that I was working on, gave it a short cut key stroke and it all worked perfectly. When done with the exercise I deleted the macro from that workbook. When I open a new workbook the ctrl key still activates the macro. I can't find where or how I delete the short cut key combo for that macro? All help is appreciated. Thanks in advance. SteveR "Steve" <SteveR7138@msn.com> wrote in news:ebQ92OWsFHA.3604@tk2msftngp13.phx.gbl: > I didn't see a gro...

datestamp 06-09-07
How can I make a datestamp on the records in a table when they are modified or created? I am running Access 97? Thanks. "Wayne-I-M" <WayneIM@discussions.microsoft.com> wrote in message <FB22AFD2-08BD-4F02-A2BF-73577AD5B446@microsoft.com>: > Hi > > Create a new field in your table called DateStamp. > > Insert this new field into your form. > > Use the forms event OnCurrent > > Me.DateStampt = Now() > > Note this will alter the date stampt every time the form is > activated. if you want to keep a record of "each" time the...

duplicates 04-05-07
I have a table with user names and security modules. There are 1700 users and 65 security modules that will have a Y or N depending on if they have that module. I would like to be able to run a query that will group all the users with the same security . I cannot figure out how to do this. The Duplicate wizard will not allow over 10 columns, and as noted I have 65 columns. Thanks, First of all you need to revise your table structure and not have the 65 columns. Have a table with the user information. Next a table listing the 65 security modules and associated information. Third buil...

Page Breaks 02-07-08
Is it possible to insert a page break in a report header? Try it and report back with your results. -- Duane Hookom Microsoft Access MVP "Bev" wrote: > Is it possible to insert a page break in a report header? You can insert a page break into a header so that part of the header prints on the next page, if that is what you mean, but it is hard to see why you would want to do that. Each section of a report, including the header, has a Force New Page property. Perhaps that is what you are seeking. "Bev" <Bev@discussions.microsoft.com> wrote in message new...

Windows Live Mail 07-23-10
I have a new windows 7 pc. I'm trying to export, import my old emails from a xp pc. I have exported all the files to the new pc but I can't import them. I click on Live Mail on the new pc and I don't get the toolbar with "File, Tools, etc" on it. What am I doing wrong. Thanks for any help Hi, Marty. The Windows Live Team decided that we would rather have more screen real estate, so the HID the Menu Bar (File, Tools, etc.) by default. To see it momentarily, just press <Alt>. There are several ways to toggle it on/off (semi-)permanently. Maybe th...

product key 04-28-10
Hi, I had a virus on my computer. I had to restore to factory settings. I went to reinstall office2007 and when I put in the product key it tells me it is "incorrect." How do I reinstall office? Please help. You can email me at mmfouad@gmail.com Mike Your Office 2007 did it come with your computer or did you purchase the Office 2007 separately with a Retail purchase -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. http://www.microsoft.com/protect "mike" <mike...

Report to PDF 07-16-07
Where I can find the code from Lebans ReportToPDF? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200707/1 On Jul 16, 9:45 am, "vianda via AccessMonster.com" <u11700@uwe> wrote: > Where I can find the code from Lebans ReportToPDF? > > -- > Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200707/1 www.lebans.com Did you even try a search engine? -Kris ...

Count records b/w time range
I have a sheet which has data with date & time fields like this 19/10/2005 10:10 19/10/2005 11:30 19/10/2005 12:12 19/10/2005 12:15 19/10/2005 10:12 How do I make a formula to pull out count between each half hour slot? like: 10:00 - 10:30 =2 10:30 - 11:00 =0 11:00 - 11:30 =1 11:30 - 12:00 =0 12:00 - 12:30 =2 Please help. Cheers! Sunny =sumproduct(--(B1:B100>--"10:00:00"),--(B1:B100<=--"10:30:00")) -- HTH RP (remove nothere from the email address if mailing direct) <sunilkeswani@gmail.com> wrote in message news:1131569006.191782.216510@g44g200...

How to remove DRM Protection from WMV/WMA/M4P/M4V/M4A/M4B/ASF and convert other popular video and audio even HD video 07-15-10
The guide shows How to remove DRM Protection from WMV/WMA/M4P/M4V/M4A/M4B/ASF and convert other popular video and audio even HD video Stpe 1: Download(http://www.5idownload.com/download/daniu-digital-media-converter-pro_full310_461741.exe) and install the Daniusoft Media Converter Pro( http://www.5idownload.com/dan-Media-Converter-pro.html ) Stpe 2: Run the software and click "Add…" button to load WMA or M4A, M4B, AAC, WMV, M4P, M4V, ASF files ( http://www.5idownload.com/img/guide-dan-media-converter-pro1.jpg) Step 3: Choose output formats. If you want to convert protected aud...

Counting days
Have the following fields in a table: Date,Name,JobNo,Hours. I need a SQL query to count the number of days each Name appears over a period of days. Any Name can appear more than once on any Date. If a Name appeared three times on the same Date it should only count as one day. Any suggestions or help would be appreciated Thanks Phil_mac SELECT name, COUNT(*) FROM (SELECT DISTINCT name, [date] FROM tableName WHERE [date] BETWEEN firstDate AND lastDate) AS a GROUP BY name where firstDate and lastDate are parameters defining the range of date of your interest. ...

Counting Number of Identical Entries in a Spreadsheet Column
I am using XP Home and Office XP. I use a spreadsheet that has about a hundred or so lines and a number of columns, one of which is "Agent Number" in which a four digit number is entered to identify the Agent for that particular line item. Is there an easy way for me to create a table showing a ranked order of the number of times each Agent is listed in the column (e.g., Agent 7818: 20, Agent 7820: 17, etc.)? Thanks in advance! -- Fred 2002 Hi use a pivot table for this . See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.c...

Display count of characters in a cell while typing.
Not sure if this should be here or in the worksheet.functions newsgroup but I think here is a safe spot. As a user types text into a cell I would like to have another cell display the number of characters that are currently in it. The count has to be 'live', meaning that as the person is typing the counter is updating, not simply updating after the user has pressed 'Enter'. Alternately, a count-down counter would be even nicer ;-) Suggestions? Search brought up nothing. -- Toby Erkson Oregon, USA Excel 2002 in Windows XP Hi not really possible as macros don&#...

Tab Control 04-06-07
I'm not sure if anyone has tried or needed to do this... I have a Form already built and I want to add another Form to it (not a subForm). I want to create two Forms separated by tabs. I think this is fairly straight forward from scratch, but I have no idea how to copy the preexisting Form onto one tab and then create a new Form on the second tab...any ideas? alex "alex" <sql_aid@yahoo.com> wrote in message news:1175875391.709252.237520@d57g2000hsg.googlegroups.com... > I'm not sure if anyone has tried or needed to do this... > > I have a Form already bu...

sending emails 12-13-09
i cant not send email keeps telling me my smtp is wrong address and i have checked it serveral times post the complete (verbatim) error message BRENDA WEAVER wrote: > i cant not send email keeps telling me my smtp is wrong address and i have > checked it serveral times "BRENDA WEAVER" <indianangel59@aol.com> wrote in message news:%23xGQ8EDfKHA.6000@TK2MSFTNGP06.phx.gbl... >i cant not send email keeps telling me my smtp is wrong address and i have >checked it serveral times me to i keeep getting errors and they dont get >through ...

Excel #28
I rely do not know if this is the right place to ask but I try! I'am looking for a way to use from Excel Skype! I have an Excel-Sheet with Customer names adresses and phone numbers I want to use this phone numbers straight out from excel instead doing a cut in the excel-sheet start skype and past the phone number in. Does anyone know if this works or hoe i can making it work? With regards, Reinhold On Sat, 12 Feb 2011 14:30:21 +0100, Reinhold Kwauka <Bernd-reinhold.kwauka@t-online.de> wrote: >I rely do not know if this is the right place to ask but I try! > >I'am lo...

PivotTable count unique
I have several pivot tables linked to a single database with 12 fields All database records are unique One pivot table looks at just 2 fields from 12 as per the simplified example below: Field1 Field2 A abc A abc A def B ghi C hij Would like the pivot table to count the unique occurences from Field2 against Field1 The result would be: A 2 B 1 C 1 Any idea how this could be achieved within a pivot table? Ok, not sure if ur using 2007 or pre-2007, but look at subtotals. I have this in 2007: Field1 Field2 A abc ...

Access 2.0 to Access 97 09-07-04
I have a vb 3.0 application that accesses data in an Access 2.0 database. If I convert this database to Access 97 it no longer works saying unrecognizable database format. Is there some way I can access Access 97 databases from vb 3.0? Hi, Nikki. > Is there some way I can access Access 97 databases > from vb 3.0? You'll probably need to upgrade your VB compiler as well. VB 3.0 creates 16-bit applications, whereas Access 97 is 32-bit, so if your VB application is accessing database application objects in addition to accessing data, then you'll likely run into problems. Th...