If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
adding fields
I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#2
|
|||
|
|||
adding fields
So which do you want F4 to show? The total or the average?
Basically what you need is a function you can call from the After Update event of F1, F2, and F3. It would be something like this: Private Function CalcControls() As Boolean Dim dblTotal As Double Dim lngCounter as Long With Me dblTotal = Nz(.F1, 0) + Nz(.F2, 0) + Nz(.F3,0) If Not IsNull(.F1) Then lngCounter = 1 End If If Not IsNull(.F2) Then lngCounter = lngCounter + 1 End If If Not IsNull(.F1) Then lngCounter = lngCounter +1 End If 'To get the total .F4 = dblTotal 'To get the arverage If lngCounter = 0 then .F4 = dblTotal Else .F4 = dblTotal / lngCounter End If End With End Function -- Dave Hargis, Microsoft Access MVP "jpnyc" wrote: I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#3
|
|||
|
|||
adding fields
First things first ... let's agree that even though you have F4 on your form
(and by the way, on forms they are termed "controls", in tables they are termed "fields" -- a "control" is like a window through which you see the "field"'s value), you are NOT trying to store that "calculated value" in your form's underlying table. It's rarely necessary and based on your description, this is not one of the exceptions. Next, "blank" may not = "blank". Even though you don't see anything in the control, this could be because the underlying field has no value (i.e., Null), or because the underlying field has a "zero-length string" (zls), or because the underlying field has spaces, or ... Those all look the same to the human eye. So look into using the Nz() function, which converts a Null to whatever you want (say, zero!). You could use something like: Nz([F1],0) Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of the (non-null) values. But "0" can be meaningful! How many dollars do you have left in your wallet? "0"! So how you do this will depend on what you are measuring. You can put an expression in the Control Source property of your [F4] on the form in design view, and use the Nz() function, and other functions that count the number of non-null values, but there's an underlying potential issue. If you have multiple fields in your table to store numbers that can be added together like this, you may be committing spreadsheet on Access. Using multiple columns to capture numbers, then adding them together is something you'd do like this if limited to using a spreadsheet. But Access is a relational database, not a spreadsheet. Consider posting a bit more description about where these numbers came from and why you want to do this. Folks here may be able to offer ways for you to get more out of the tools Access offers. Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#4
|
|||
|
|||
adding fields
Thanks for your responses. I will attempt to explain what I am doing so bear
with me. Meanwhile, I will play with the advice I was given. I want to create a database for a Parkinson's research project. Basically, as the study subjects complete tasks(fields/controls), they each receive a score based on the time it took to complete. If they go over the time limit the recieve a score "0" [i.e unscramble letter] (task1= F1 = 0). If they complete above the 85th percentile, they score "100"[ i.e draw an square]( task3= F3 = 100), if they perform average they score "50" [i.e touch left knee with right hand] (task2= F2=50), . etc.etc. If they don't want to do the task, I can't give them a score and leave the value blank [hop on one foot = task8 = " " = null), . So I need to tally the scores of each task and show the average for each category (i.e mental (fields/controls) = F4 or physical limitation (fields/controls)=F5, ) by dividing the total sum of tasks by the number of tasks completed. {maybe the calculation belongs in the query but I still need to account for null} F1(0)+F2(50)+F3(100) = 150 --150/3 = 50(F4) mental F1(0)+F3(100)+F8(null) = 100--100/2 = 50(F5) physical Now for each patient visit, I would enter these scores for each task on a new form and allow the database to calculate the scores entered and average the current visit categories (mental and physical) Another small problem I see is that my primary key has to be a random generated number to separate each visit(new forms) which I think I accomplished. Each patient has at least three visits, so any identifier to them can't be used as the primary key because of repetition. Now with access, I hope to eliminate the hand writing/paper searching and store the values for each visit to monitor the progression. I then developed a query for each patient (i.e query Smith and query Rivers) to restrict the patient value and then Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith 7/1/09. Sorry to essay, but I will play with the advice already given. Any further advice would be appreciated. Thanks again for the assistance "Jeff Boyce" wrote: First things first ... let's agree that even though you have F4 on your form (and by the way, on forms they are termed "controls", in tables they are termed "fields" -- a "control" is like a window through which you see the "field"'s value), you are NOT trying to store that "calculated value" in your form's underlying table. It's rarely necessary and based on your description, this is not one of the exceptions. Next, "blank" may not = "blank". Even though you don't see anything in the control, this could be because the underlying field has no value (i.e., Null), or because the underlying field has a "zero-length string" (zls), or because the underlying field has spaces, or ... Those all look the same to the human eye. So look into using the Nz() function, which converts a Null to whatever you want (say, zero!). You could use something like: Nz([F1],0) Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of the (non-null) values. But "0" can be meaningful! How many dollars do you have left in your wallet? "0"! So how you do this will depend on what you are measuring. You can put an expression in the Control Source property of your [F4] on the form in design view, and use the Nz() function, and other functions that count the number of non-null values, but there's an underlying potential issue. If you have multiple fields in your table to store numbers that can be added together like this, you may be committing spreadsheet on Access. Using multiple columns to capture numbers, then adding them together is something you'd do like this if limited to using a spreadsheet. But Access is a relational database, not a spreadsheet. Consider posting a bit more description about where these numbers came from and why you want to do this. Folks here may be able to offer ways for you to get more out of the tools Access offers. Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#5
|
|||
|
|||
adding fields
Based on your further elaboration, you really need to spend the time
deciding whether to 'add up' your scores using a tool designed to add up values (e.g., Excel), or whether you want to learn relational database design, Access tips/tricks, graphical user interface design and application development. You will need experience with all four of those if you want to get good use of THAT tool (MS Access). What's more important, adding scores or learning application development using relational databases? How soon must this be completed? What's the budget? This is a "pay now or pay later" situation. If you try to stuff multiple (repeating) fields into an Access table, you and Access will have to work overtime to overcome data for which Access is not optimized (i.e., 'sheet data). Good luck! Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... Thanks for your responses. I will attempt to explain what I am doing so bear with me. Meanwhile, I will play with the advice I was given. I want to create a database for a Parkinson's research project. Basically, as the study subjects complete tasks(fields/controls), they each receive a score based on the time it took to complete. If they go over the time limit the recieve a score "0" [i.e unscramble letter] (task1= F1 = 0). If they complete above the 85th percentile, they score "100"[ i.e draw an square]( task3= F3 = 100), if they perform average they score "50" [i.e touch left knee with right hand] (task2= F2=50), . etc.etc. If they don't want to do the task, I can't give them a score and leave the value blank [hop on one foot = task8 = " " = null), . So I need to tally the scores of each task and show the average for each category (i.e mental (fields/controls) = F4 or physical limitation (fields/controls)=F5, ) by dividing the total sum of tasks by the number of tasks completed. {maybe the calculation belongs in the query but I still need to account for null} F1(0)+F2(50)+F3(100) = 150 --150/3 = 50(F4) mental F1(0)+F3(100)+F8(null) = 100--100/2 = 50(F5) physical Now for each patient visit, I would enter these scores for each task on a new form and allow the database to calculate the scores entered and average the current visit categories (mental and physical) Another small problem I see is that my primary key has to be a random generated number to separate each visit(new forms) which I think I accomplished. Each patient has at least three visits, so any identifier to them can't be used as the primary key because of repetition. Now with access, I hope to eliminate the hand writing/paper searching and store the values for each visit to monitor the progression. I then developed a query for each patient (i.e query Smith and query Rivers) to restrict the patient value and then Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith 7/1/09. Sorry to essay, but I will play with the advice already given. Any further advice would be appreciated. Thanks again for the assistance "Jeff Boyce" wrote: First things first ... let's agree that even though you have F4 on your form (and by the way, on forms they are termed "controls", in tables they are termed "fields" -- a "control" is like a window through which you see the "field"'s value), you are NOT trying to store that "calculated value" in your form's underlying table. It's rarely necessary and based on your description, this is not one of the exceptions. Next, "blank" may not = "blank". Even though you don't see anything in the control, this could be because the underlying field has no value (i.e., Null), or because the underlying field has a "zero-length string" (zls), or because the underlying field has spaces, or ... Those all look the same to the human eye. So look into using the Nz() function, which converts a Null to whatever you want (say, zero!). You could use something like: Nz([F1],0) Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of the (non-null) values. But "0" can be meaningful! How many dollars do you have left in your wallet? "0"! So how you do this will depend on what you are measuring. You can put an expression in the Control Source property of your [F4] on the form in design view, and use the Nz() function, and other functions that count the number of non-null values, but there's an underlying potential issue. If you have multiple fields in your table to store numbers that can be added together like this, you may be committing spreadsheet on Access. Using multiple columns to capture numbers, then adding them together is something you'd do like this if limited to using a spreadsheet. But Access is a relational database, not a spreadsheet. Consider posting a bit more description about where these numbers came from and why you want to do this. Folks here may be able to offer ways for you to get more out of the tools Access offers. Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#6
|
|||
|
|||
adding fields
Thanks Jeff for your advice. There is no pay for this project as I ama
medical resident and this is part of my research thesis. I will have medical students administering tests and making phone calls, so I can focus on the literature development and advancement for the field. My purpose for access is to have quick synopsis of the patients and easy data entry for the students. Time to complete is the sooner the better, but no deadline. Just add the hard copy to the stack of papers already here. Thanks again. "Jeff Boyce" wrote: Based on your further elaboration, you really need to spend the time deciding whether to 'add up' your scores using a tool designed to add up values (e.g., Excel), or whether you want to learn relational database design, Access tips/tricks, graphical user interface design and application development. You will need experience with all four of those if you want to get good use of THAT tool (MS Access). What's more important, adding scores or learning application development using relational databases? How soon must this be completed? What's the budget? This is a "pay now or pay later" situation. If you try to stuff multiple (repeating) fields into an Access table, you and Access will have to work overtime to overcome data for which Access is not optimized (i.e., 'sheet data). Good luck! Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... Thanks for your responses. I will attempt to explain what I am doing so bear with me. Meanwhile, I will play with the advice I was given. I want to create a database for a Parkinson's research project. Basically, as the study subjects complete tasks(fields/controls), they each receive a score based on the time it took to complete. If they go over the time limit the recieve a score "0" [i.e unscramble letter] (task1= F1 = 0). If they complete above the 85th percentile, they score "100"[ i.e draw an square]( task3= F3 = 100), if they perform average they score "50" [i.e touch left knee with right hand] (task2= F2=50), . etc.etc. If they don't want to do the task, I can't give them a score and leave the value blank [hop on one foot = task8 = " " = null), . So I need to tally the scores of each task and show the average for each category (i.e mental (fields/controls) = F4 or physical limitation (fields/controls)=F5, ) by dividing the total sum of tasks by the number of tasks completed. {maybe the calculation belongs in the query but I still need to account for null} F1(0)+F2(50)+F3(100) = 150 --150/3 = 50(F4) mental F1(0)+F3(100)+F8(null) = 100--100/2 = 50(F5) physical Now for each patient visit, I would enter these scores for each task on a new form and allow the database to calculate the scores entered and average the current visit categories (mental and physical) Another small problem I see is that my primary key has to be a random generated number to separate each visit(new forms) which I think I accomplished. Each patient has at least three visits, so any identifier to them can't be used as the primary key because of repetition. Now with access, I hope to eliminate the hand writing/paper searching and store the values for each visit to monitor the progression. I then developed a query for each patient (i.e query Smith and query Rivers) to restrict the patient value and then Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith 7/1/09. Sorry to essay, but I will play with the advice already given. Any further advice would be appreciated. Thanks again for the assistance "Jeff Boyce" wrote: First things first ... let's agree that even though you have F4 on your form (and by the way, on forms they are termed "controls", in tables they are termed "fields" -- a "control" is like a window through which you see the "field"'s value), you are NOT trying to store that "calculated value" in your form's underlying table. It's rarely necessary and based on your description, this is not one of the exceptions. Next, "blank" may not = "blank". Even though you don't see anything in the control, this could be because the underlying field has no value (i.e., Null), or because the underlying field has a "zero-length string" (zls), or because the underlying field has spaces, or ... Those all look the same to the human eye. So look into using the Nz() function, which converts a Null to whatever you want (say, zero!). You could use something like: Nz([F1],0) Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of the (non-null) values. But "0" can be meaningful! How many dollars do you have left in your wallet? "0"! So how you do this will depend on what you are measuring. You can put an expression in the Control Source property of your [F4] on the form in design view, and use the Nz() function, and other functions that count the number of non-null values, but there's an underlying potential issue. If you have multiple fields in your table to store numbers that can be added together like this, you may be committing spreadsheet on Access. Using multiple columns to capture numbers, then adding them together is something you'd do like this if limited to using a spreadsheet. But Access is a relational database, not a spreadsheet. Consider posting a bit more description about where these numbers came from and why you want to do this. Folks here may be able to offer ways for you to get more out of the tools Access offers. Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
#7
|
|||
|
|||
adding fields
The point I tried to make is that there is no "sooner" when you start with a
new tool. Access is a relational database. Access is optimized to work best with well-normalized data. If these are unfamiliar terms, and if you haven't used Access before, you could be old and gray before you get done what you want g. If you want/need the sums of some variable number of measurements, the quick/dirty way to do this is in Excel. If you have the time, you certainly can do this in Access, but it won't look/act the same as a spreadsheet (unless, of course, you learn enough about Access to circumvent the well-designed built-in tools g). I suspect you're describing something like (untested, unproven): trelTestResults TestResultID PersonID (who took the test) TestID (which test) TestDate (when) TestResult (however you measure) With a table design like this, you could have one person with one or ten or 100 'tests', and adding another test result for that person would mean adding one more record in the table. Also, if you introduced a new "test", you'd add it to your "other" table (tblTest), then use it's ID in the above table to show persons' results on that new test. No need to add columns, no need to redesign queries, no need to modify forms and reports, no need ...! Best of Luck! Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... Thanks Jeff for your advice. There is no pay for this project as I ama medical resident and this is part of my research thesis. I will have medical students administering tests and making phone calls, so I can focus on the literature development and advancement for the field. My purpose for access is to have quick synopsis of the patients and easy data entry for the students. Time to complete is the sooner the better, but no deadline. Just add the hard copy to the stack of papers already here. Thanks again. "Jeff Boyce" wrote: Based on your further elaboration, you really need to spend the time deciding whether to 'add up' your scores using a tool designed to add up values (e.g., Excel), or whether you want to learn relational database design, Access tips/tricks, graphical user interface design and application development. You will need experience with all four of those if you want to get good use of THAT tool (MS Access). What's more important, adding scores or learning application development using relational databases? How soon must this be completed? What's the budget? This is a "pay now or pay later" situation. If you try to stuff multiple (repeating) fields into an Access table, you and Access will have to work overtime to overcome data for which Access is not optimized (i.e., 'sheet data). Good luck! Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... Thanks for your responses. I will attempt to explain what I am doing so bear with me. Meanwhile, I will play with the advice I was given. I want to create a database for a Parkinson's research project. Basically, as the study subjects complete tasks(fields/controls), they each receive a score based on the time it took to complete. If they go over the time limit the recieve a score "0" [i.e unscramble letter] (task1= F1 = 0). If they complete above the 85th percentile, they score "100"[ i.e draw an square]( task3= F3 = 100), if they perform average they score "50" [i.e touch left knee with right hand] (task2= F2=50), . etc.etc. If they don't want to do the task, I can't give them a score and leave the value blank [hop on one foot = task8 = " " = null), . So I need to tally the scores of each task and show the average for each category (i.e mental (fields/controls) = F4 or physical limitation (fields/controls)=F5, ) by dividing the total sum of tasks by the number of tasks completed. {maybe the calculation belongs in the query but I still need to account for null} F1(0)+F2(50)+F3(100) = 150 --150/3 = 50(F4) mental F1(0)+F3(100)+F8(null) = 100--100/2 = 50(F5) physical Now for each patient visit, I would enter these scores for each task on a new form and allow the database to calculate the scores entered and average the current visit categories (mental and physical) Another small problem I see is that my primary key has to be a random generated number to separate each visit(new forms) which I think I accomplished. Each patient has at least three visits, so any identifier to them can't be used as the primary key because of repetition. Now with access, I hope to eliminate the hand writing/paper searching and store the values for each visit to monitor the progression. I then developed a query for each patient (i.e query Smith and query Rivers) to restrict the patient value and then Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith 7/1/09. Sorry to essay, but I will play with the advice already given. Any further advice would be appreciated. Thanks again for the assistance "Jeff Boyce" wrote: First things first ... let's agree that even though you have F4 on your form (and by the way, on forms they are termed "controls", in tables they are termed "fields" -- a "control" is like a window through which you see the "field"'s value), you are NOT trying to store that "calculated value" in your form's underlying table. It's rarely necessary and based on your description, this is not one of the exceptions. Next, "blank" may not = "blank". Even though you don't see anything in the control, this could be because the underlying field has no value (i.e., Null), or because the underlying field has a "zero-length string" (zls), or because the underlying field has spaces, or ... Those all look the same to the human eye. So look into using the Nz() function, which converts a Null to whatever you want (say, zero!). You could use something like: Nz([F1],0) Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of the (non-null) values. But "0" can be meaningful! How many dollars do you have left in your wallet? "0"! So how you do this will depend on what you are measuring. You can put an expression in the Control Source property of your [F4] on the form in design view, and use the Nz() function, and other functions that count the number of non-null values, but there's an underlying potential issue. If you have multiple fields in your table to store numbers that can be added together like this, you may be committing spreadsheet on Access. Using multiple columns to capture numbers, then adding them together is something you'd do like this if limited to using a spreadsheet. But Access is a relational database, not a spreadsheet. Consider posting a bit more description about where these numbers came from and why you want to do this. Folks here may be able to offer ways for you to get more out of the tools Access offers. Regards Jeff Boyce Microsoft Access MVP "jpnyc" wrote in message ... I'm a novice at access and using Access 2000 There is probably a simple answer, but I can't make the operation work so specific details would probably help me considerably. I have Four fields on a form: F1, F2, F3, F4 Say I populate the fields F1=0, F2=50 and F3=100 I would like F4 to add F1+F2+F3 as the fields are populated ---- 150 Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3--100 Trick #2: I would also like F4 to divide the total sum by the number of fields populated. (i.e 150/3 or 100/2) Thanks in advance |
Thread Tools | |
Display Modes | |
|
|