Help!

I am not very skilled in Access and need to create a very basic form for
quality assurance.  I have the form set up with a set amount of questions and
a score box next to each one.  I have built an expression in the final box
where I am able to add all of them and divide by the amount of standards to
come up with the average.  This is the problem:  I need to be able to also
include "N/A" as an answer and make sure that the question that has this will
not be included in the final average.  This could be different every time
depending on the case.  Some may have 1 or another may have 5 and need to
make sure that the expression will change the number I need to average with...
.. Am I making any sense?

Any help would be appreciated.

Thank you.

0
k12345
3/1/2007 7:06:23 PM
access.forms 6864 articles. 2 followers. Follow

6 Replies
925 Views

Similar Articles

[PageSpeed] 51

k12345,

What you want can be done, however, I have a few questions.

What type of field is the "score box" for each queston?

If this field is a numeric field, then you will need to determine just how 
you are going to flag the field to indicate the "N/A" value.  The fact that 
the field is numeric will prevent you from just entering "N/A" in the field.

The general concept would be that you would have some method of specifying 
(flagging) each question where the "N/A" applies.  Each time you flag a 
question you would increment a variable by 1. If you then remove the flag on 
a question you would simply decrement that variable by 1. Then at the time 
you want to calculate the average you would simply substract the variable 
that is holding the number of "N/A" questions from the total possible 
questions and then use that number to obtain the average.

-- 
HTH

Mr B


"k12345" wrote:

> I am not very skilled in Access and need to create a very basic form for
> quality assurance.  I have the form set up with a set amount of questions and
> a score box next to each one.  I have built an expression in the final box
> where I am able to add all of them and divide by the amount of standards to
> come up with the average.  This is the problem:  I need to be able to also
> include "N/A" as an answer and make sure that the question that has this will
> not be included in the final average.  This could be different every time
> depending on the case.  Some may have 1 or another may have 5 and need to
> make sure that the expression will change the number I need to average with...
> . Am I making any sense?
> 
> Any help would be appreciated.
> 
> Thank you.
> 
> 
0
Utf
3/1/2007 11:40:03 PM
I initially had the "score box" as a numeric field, but changed it to a text
box so I may include "N/A" as an option.  Is it possible to have an
expression where if it recognized that there was nothing in the box, it would
not include that box in the averaging?  

Mr B wrote:
>k12345,
>
>What you want can be done, however, I have a few questions.
>
>What type of field is the "score box" for each queston?
>
>If this field is a numeric field, then you will need to determine just how 
>you are going to flag the field to indicate the "N/A" value.  The fact that 
>the field is numeric will prevent you from just entering "N/A" in the field.
>
>The general concept would be that you would have some method of specifying 
>(flagging) each question where the "N/A" applies.  Each time you flag a 
>question you would increment a variable by 1. If you then remove the flag on 
>a question you would simply decrement that variable by 1. Then at the time 
>you want to calculate the average you would simply substract the variable 
>that is holding the number of "N/A" questions from the total possible 
>questions and then use that number to obtain the average.
>
>> I am not very skilled in Access and need to create a very basic form for
>> quality assurance.  I have the form set up with a set amount of questions and
>[quoted text clipped - 10 lines]
>> 
>> Thank you.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1

0
k12345
3/2/2007 12:24:32 PM
On Fri, 02 Mar 2007 12:24:32 GMT, "k12345 via AccessMonster.com"
<u32154@uwe> wrote:

>I initially had the "score box" as a numeric field, but changed it to a text
>box so I may include "N/A" as an option.  Is it possible to have an
>expression where if it recognized that there was nothing in the box, it would
>not include that box in the averaging?  

I'd really suggest changing it back to a Number field, and leaving it
NULL for the n/a. NULL values will not be included in averaging (i.e.
the Avg() of 5, NULL, 6, NULL, NULL, and 7 is in fact 6).

You can set the Format property of the textbox in which this field is
displayed to

#;-#;#;"N/A"

to show it as a positive, negative, or zero number, or N/A for the
null value.

             John W. Vinson [MVP]
0
John
3/2/2007 7:29:02 PM
John,

Great approach to this problem.  As usual you have demonstrated why you have 
received your MVP designation.  

I have been at this stuff for a long time and did not realize two things 
that you addressed in your answer.  First the formatting of the text box to 
display the value, including the "N/A".  I must admit that many times I 
forget about the formatting capabilities and what they can do.  However, the 
one thing that I was not aware of is that the Average function would not use 
null values as part of the average.  Just did not know it worked that way.

I just wanted to thank you for you continued participation in these 
newsgroups. I have learned much from your postings as well as the posting of 
many ohters.

Thanks again.

Mr B


"John W. Vinson" wrote:

> On Fri, 02 Mar 2007 12:24:32 GMT, "k12345 via AccessMonster.com"
> <u32154@uwe> wrote:
> 
> >I initially had the "score box" as a numeric field, but changed it to a text
> >box so I may include "N/A" as an option.  Is it possible to have an
> >expression where if it recognized that there was nothing in the box, it would
> >not include that box in the averaging?  
> 
> I'd really suggest changing it back to a Number field, and leaving it
> NULL for the n/a. NULL values will not be included in averaging (i.e.
> the Avg() of 5, NULL, 6, NULL, NULL, and 7 is in fact 6).
> 
> You can set the Format property of the textbox in which this field is
> displayed to
> 
> #;-#;#;"N/A"
> 
> to show it as a positive, negative, or zero number, or N/A for the
> null value.
> 
>              John W. Vinson [MVP]
> 
0
Utf
3/3/2007 1:12:08 PM
Thank you so much for your response.  I have done as instructed, but I have
one more question.  Would you be able to advise exactly how I should have the
expression?  I currently (and most likely, incorrectly) have all of the boxes
added and then divided by 24.  I now know this must change so it recognizes
the null value.  I did not use the average function since I did not know how
to use it.  

Thank you.

Mr B wrote:
>John,
>
>Great approach to this problem.  As usual you have demonstrated why you have 
>received your MVP designation.  
>
>I have been at this stuff for a long time and did not realize two things 
>that you addressed in your answer.  First the formatting of the text box to 
>display the value, including the "N/A".  I must admit that many times I 
>forget about the formatting capabilities and what they can do.  However, the 
>one thing that I was not aware of is that the Average function would not use 
>null values as part of the average.  Just did not know it worked that way.
>
>I just wanted to thank you for you continued participation in these 
>newsgroups. I have learned much from your postings as well as the posting of 
>many ohters.
>
>Thanks again.
>
>Mr B
>
>> >I initially had the "score box" as a numeric field, but changed it to a text
>> >box so I may include "N/A" as an option.  Is it possible to have an
>[quoted text clipped - 14 lines]
>> 
>>              John W. Vinson [MVP]

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1

0
k12345
3/12/2007 6:23:29 PM
I would not want to speak for John, but below is an sql statement that uses 
the Avg function. 

SELECT Avg(tblInvNumsTemp.InvAmt) AS AvgOfInvAmt
FROM tblInvNumsTemp;

This sql statement is just a totals type query.

I think, what John was indicating was that if you have values in some 
records and null in some records, using the Avg function will produce the 
average of the records with existing values that are not null and will 
average these values without using the null records at all.

I tested this before pasting the sql statement so I could see that it 
produced what was needed and sure enough, it does. (Not that I questioned 
John, but just wanted to test it out.)  I have a table named 
"tblInvNumsTemp". It has only two fields for testing: "InvNum" and "InvAmt".  
I have a total of 9 records in the table, but only 2 of those records have a 
value in the "InvAmt" field. The two values are $100 and $500.  When the sql 
statement above is run, the average is $250 even though there are 9 records 
and 7 of them have a null value.

If you are not going to use the Avg function, then you would need to track 
the number of instances where the user has entered a number in a field and 
then use that number instead of the 24 that you indicated you are using.  
This would go back to what I posted to start with.

As is often the case in Access, there are multiple ways to approach solving 
any problem. It just depends of how you want to go about it. 

-- 
HTH

Mr B


"k12345 via AccessMonster.com" wrote:

> Thank you so much for your response.  I have done as instructed, but I have
> one more question.  Would you be able to advise exactly how I should have the
> expression?  I currently (and most likely, incorrectly) have all of the boxes
> added and then divided by 24.  I now know this must change so it recognizes
> the null value.  I did not use the average function since I did not know how
> to use it.  
> 
> Thank you.
> 
> Mr B wrote:
> >John,
> >
> >Great approach to this problem.  As usual you have demonstrated why you have 
> >received your MVP designation.  
> >
> >I have been at this stuff for a long time and did not realize two things 
> >that you addressed in your answer.  First the formatting of the text box to 
> >display the value, including the "N/A".  I must admit that many times I 
> >forget about the formatting capabilities and what they can do.  However, the 
> >one thing that I was not aware of is that the Average function would not use 
> >null values as part of the average.  Just did not know it worked that way.
> >
> >I just wanted to thank you for you continued participation in these 
> >newsgroups. I have learned much from your postings as well as the posting of 
> >many ohters.
> >
> >Thanks again.
> >
> >Mr B
> >
> >> >I initially had the "score box" as a numeric field, but changed it to a text
> >> >box so I may include "N/A" as an option.  Is it possible to have an
> >[quoted text clipped - 14 lines]
> >> 
> >>              John W. Vinson [MVP]
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1
> 
> 
0
Utf
3/12/2007 7:58:35 PM
Reply:

Similar Artilces:

Help required in Excel Charting
Friends, Please refer to the attached Sheet. I have some data in " data available". Its something like, say from this km to this km, the # of birds observed is this much. And I need to generate a chart, that appears as in " Chart I need" I have used a scatter plot here using the data I copy into " How I arrange the data". I have these questions. 1) I need a chart like what I have shown. 2) Is copying the data into the second sheet and charting based on the data inthe second sheet the only option available? 3)If yes, what is the alternative? 4) If no, I want ...

Full Screen Help and KB Shortcut
Hello all, Office XP SP3 First, is there a way (registry tweak or anything else) to get the Help files to open up in full screen (maximized) mode? I hate having to double-click the title bar every time. (OT: I haven't used Office 2003 very much, even though I have it on a machine at home (the one we use mainly for a DVR--that's why we don't use Office 2003 that much). It appears that the help files don't open in a separate window like they do in XP. Is this true? Is there a way to get them to open in a separate window? The reason I ask is because the main way I use ...

help about outlook storage
hi i have problem with the storage amount on the server i am working on : its only 300 Mb and i am receiving more than 300 mb sure then i have to remove some of the mails to make it able to receive more mails so there is another way to transfer this incoming mails to another folder on the hard disk and not into another folder in the outlook ?? or even another program to save incoming msgs from out look to another place on the hard disk.??. Submitted using http://www.outlookforums.com Is this an Exchange mailbox? You don't mention what type of account you have. If you are connecting...

coding help
I am working on a project (again). I have a multi page report that has employee information on separate pages. I want to email the individual report to employee. I have been given help before, but I always get in way over my head. I really could use some more help. This is the code I have copied/tweeked so far. The problem is right now I can't get passed "Set rst = CurrentDb.OpenRecordset("All Emp Time To Date")" I step thru the code and soon as I hit that line it goes to the error message. I haven't worked too much on the rest of the code yet. ...

Help #5
Hi, I received email with attachment, when i click on the attachment (paperclip) to view it, it is grey in color and can not open. when opening the message, on top of the screen just below the icon bar the following message appeared: (OE removed access to the following unsafe attachments in your mail:) followed by the file name. How can I get my email attachments read as before, please help. Thanks Imad Is OE not suggesting to you that the file attachment had a virus attached? Was the mail received from someone you know? Perhaps you should contact them separately and ask if they sen...

Help...my charts are acting up!
I don't know what happened! One minute, I was working on a spreadsheet (Excel 2003 SP2), the next thing I know, all of my charts all went blank (kind of a grayish color). The charts reappear individually when selected, but go blank again when I move to another chart or to a cell. And to make matters worse, when I select a chart and try and copy it to PowerPoint, the paste appears blank too. I have a screen shot if that helps, but I haven't any idea how to send it. TIA! Tools menu > Options > View - select Show All. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials...

How can I get help to make my outlook 2003 to work
The connection to Microsoft Exchange Server is unavailable Outlook must be online or connected to complete this action. (OK) when I click ok a window comes up Microsoft Exchange Server: in th boxes is has earthlink.net and the next box says mail box John XXXXXXXX I'v tried everything and I can't get this to work. All other programs work just fine. Are you connecting to an Exchange server? If not, remove it from your = profile and install your Earthlink POP3 account. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicite...

Help With CRM Features
Hello All.... I am cinsidering purchasing CRM for my Win2003 Server. But have a few questions... Do I need exchange to access the CRM Software.... (in that case, would it be easier to but a SBS 2003 server, add it to my domain, and then install the CRM on that) Also, can I fax out of CRM. Our business is quite small, but Faxing is quite important. Can I fax and recieve faxes using CRM, or is that a Win2003 thing? (also can I share the fax for multiple users?) Responses much appreciated... Thanks, Dekkar This is a multi-part message in MIME format. ------=_NextPart_000_09AF_01C45697....

HELP! Exchange server can add feeds but not download items. Any thoughts?
I have configured Exchange to use the NNTP service which works fine on the server but when I add a feed it can connect to the server and replicate the folders, but then i get "STATUS IS FAILURE" when it goes to download the messages. Any thoughts? ...

VBA Help:Change Color of Cells
Dear All, How to change the color of cells? I'm confused whether to use range object or cells object. Pls give me example. Thanks Robert Lie Hi Robert, The Cells property returns a range object, so you could, for example, use either of the following equivalent instructions: Range("A1:A10").Interior.ColorIndex = 6 Cells(1, 1).Resize(10).Interior.ColorIndex = 6 --- Regards, Norman "Robert Lie" <robert.lie24@gmail.com> wrote in message news:%23vv10TVIGHA.2928@TK2MSFTNGP10.phx.gbl... > Dear All, > > How to change the color of cel...

Outlook 2003 Attachment Help
Hi, 1) How do I unblock attchments in outlook 2003? I think its aregistry setting somewhere. I would like to add a few file formats to the list of safe files. 2) I have a lot of folders and subfolders where I organise my email in outlook. I would like to stop outlook opening these folders every time I have a new message in them? Any ideas would help. Thanks Rad ...

HELP!! After Upgrading to POS 2.0 (NOT RMS), Reboot Takes 10-15 Minutes
After upgrading to POS 2.0 (NOT RMS) over the Thanksgiving holiday hoping to take advantage of the reputedly improved reporting and feature set, we now experience a 10-15 minute boot duration waiting for Point of Sale to fully start. Now, if the system crashes or loses database connectivity (over the wired LAN) during business hours, which it's prone to do at least once daily since upgrading, a reboot is required to get the cashier station to reconnect to the office db. The POS Manager (also POS 2.0) is installed on a backoffice PC running dual processors and 4GB RAM. The cashier...

Formula help please
HI, HELP REQUIRED WITH FORMULA FOR EXCEL PLEASE Q: EXCEL WORKBOOK WITH TWO SHEETS, ONE TITLED MON & THE OTHER STAT. ON SHEET MON, I HAVE TWO COLUMNS (H, K) WITH A RANGE OF SAY 20 LINES EACH. ON SHEET STAT, I HAVE TWO CELLS (B5, F5) ON A LINE. I WANT ‘MON’ ‘H’ TO COMPARE WITH ‘STAT’ ‘B5’. ON ‘H’ LINES THAT MATCH WITH ‘B5’, IF A NUMBER IS ENTERED IN THE CORRESPONDING ‘MON’ ‘K’ LINE, ‘STAT’ ‘F5’ WILL COUNT IT. EXAMPLE: ‘STAT’ B5 = MM ‘MON’ H8 & H12 = MM ‘MON’ K8 = 0800 & K12=’ANYTHING BUT A NUMBER’ RESULT ON ‘STAT’ F5 SHOULD BE ‘1’ THANKS! JOHN Hi John...

Desperate help for daughter
Hi my daughter has to do a poyroll excercise for school. While I can help her with most of it I am having trouble helping her do a VLOOKUP for the tax table. eg Income Tax $0.00 0.00% $200.00 5.00% $500.00 10.00% $1,000.00 15.00% $2,000.00 25.00% $3,000.00 35.00% $4,000.00 45.00% What I need to help her with is a formula that basically states if total pay is equal to or between two figure e.g 1000 to 2000 then the the appliacble tax % is applied. I really hope someone can help me out on this. I think the god like status I used to hold for her slipping!!! ;) -- All help much apprecia...

i need help with finding a specific formula
Hi Within Excel I want to be able to run the following formula. I want excel to do the following in laymans terms We have a coloumn of figures. I want excel to look through the coloumn and if any of the figures match I want to add a value on to the original value ! Hope that makes sense and hope someone can help Cheers You could do this with a pivot table. "Dave Edinburgh" <Dave Edinburgh@discussions.microsoft.com> wrote in message news:934EF11A-8442-4A3C-9FCD-4185646C5AED@microsoft.com... > Hi Within Excel I want to be able to run the following formula. I want &g...

Help needed with form and Combo box
This is probably very simple and I have overcomplicated things I have a form which uses a combo box to pull data from my client database where once found there details appear on the form which is actually a new quote/job ClientID / Name / Address JobID / ClientID / Job data ClientID being related. On the form for Jobs I use a combo box to pull in the details of the customer uses ClientID The problem is that I may start a new job unaware that the client does not exist, then obviously it puts up an error message. I would like the ability to enter a new client using the same form, t...

help needed to add values with conditions
Hi, I have some data N O P Q TD (Y/N) Value VAT Total Val Y £71.44 £10.72 £82.16 4 N £71.44 £10.72 £82.16 5 N £84.08 £12.61 £96.69 6 Y £84.08 £12.61 £96.69 N £102.85 £15.43 £118.28 N £34.22 £5.13 £39.35 N £0.00 £0.00 £0.00 N £0.00 £0.00 £0.00 N £0.00 £0.00 £0.00 N £84.08 £12.61 £96.69 N £84.08 £12.61 £96.69 Y £84.08 £...

Some basic excel help.
I have tried a number of methods to solve 2 problems and just cannot get it right, I thought conditional formatting might do problem 1 but no joy, so I would appreciate any guidance . 1) I have a basic spread sheet which is as follows. Hours Rate Total Acuum Row 4 would read the total (D4) as =sum( b4*c4) and the accum(E4) would read =sum(e3+d4) So I have a number of hours multiplied by a variable rate which is entered into the total cell for each row., this value is added to the previous rows accum (total) to give a new ( running ) accum ( total ) on the row. Each row therefore has H...

Help
I'm trying to import a stock portfolio listing using a web query from MSN web site. Not getting the proper data. Excel procedure follows: click on data tab, get external data from web new web query box pops up put in web address (moneycenteral.msn.com/home.asp) my stock portfolio listing appears with yellow arrow next to it click on arrow, turns to a green check mark click on import What happens is that Excel is not importing the stock listings but some other data from the msn web site. I also tried a similar procedure using the Fidelity web site - same problem, Excel does not import...

HELP !!! I have a ARRAY Formula HELP !!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the numbe are anything from nothing to 10000. I want the hourly number to appea in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = $100.00 G2 Should be $100.00 2nd Hour D48 = $100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = $1...

Help Programming an XY scatter Chart
I have a user form that inputs data onto a spreadsheet. I have a command button that runs a macro that activates the sheet, selects the data, and creates the XY Scatter chart. My Problem is that I don't know how to set a loop to set each series' properties . (The number of series changes every time) Bellow is an example where I manually set the properties for three of the series. Any Ideas on how to create a loop to set all. Range("A:A,C:C,D:D").Select Range("D1").Activate Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Sour...

Help on Graphs
i can never get the Xaxis to swtich with the Yaxis in the line graph mode i have this data 1 mol 350 2 mol 80 3 mol 67 i want the xaxis to show 350, 80, 67 but ti never does, no matter how i rearrange the colums. ?! thx -- SS2006 ------------------------------------------------------------------------ SS2006's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28174 View this thread: http://www.excelforum.com/showthread.php?threadid=477015 Hi, Here is an explanation that should help, http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html Cheers Andy SS200...

odd formula help
I have cell A1 that is a derived number. I have cell B1 that i dependent on some other calculations (including Cell C1). My problem is that I want to take cell C1 and have it look at A1 and B and adjust to where it makes Cell B1 as close as possible to Cell A1 t the hundredths or thousands precision. I'm pretty sure this is going to take some sort of fancy statistica function that goes a bit beyond my expertise with excel. Thanks in advance -- goodpastur ----------------------------------------------------------------------- goodpasture's Profile: http://www.excelforum.com/member...

Subreports Not Showing in PrintView Pls Help
I hope someone can help me as this is driving me crazy! I had to create another report similar to one I already have. Only changes were to the main report a couple of fields source. Subreports all the same, same links etc. However, when I printview the second report just one subreport shows. Visible is set to "Yes". CanGrow and CanShrink are set to "Yes". Can anyone give me some insight into why only one of my six subreport show? In my original report, all subreports show just fine. Any help will be greatly appreciated as I have to present this tomorrow. Th...

exch 2000 Problem sending internal email HELP!!!!!!!!!
for some reason everybody in my company can not send emails to each other. Also when i check the Spam filter and release all of the good mail it comes into our server but never goes to the person it is supposed to go to. any ideas? Thanks, Jason. Turn on message tracking and find out where it is getting "stuck" Nue "Jason" <Jason@discussions.microsoft.com> wrote in message news:909404AD-9A75-4CA3-A4DC-ADB00A4C7643@microsoft.com... > for some reason everybody in my company can not send emails to each other. > Also when i check the Spam filter and release...