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
|
|||
|
|||
Form to display result
Hi,
The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff |
#2
|
|||
|
|||
Form to display result
Hi Jeff,
build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff |
#3
|
|||
|
|||
Form to display result
Hi Jeanette,
tbResult stores the test result. The result of calculation (counting) based on tbResult are stored in Variables and the number of Variable will increase month after month. My problem is how to display these Variables (result) on a Form. I am thinking if I should use array. Any ideas? -- Jeff "Jeanette Cunningham" wrote: Hi Jeff, build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff . |
#4
|
|||
|
|||
Form to display result
The usual way, using Access, is to use a query as the record source for a
form. If you need calculated fields for your form, you can use the query to do the calculations, or you can use the form to do the calculations or you can use a combination of both query and form to do the calculations for the calculated fields. I would expect that you would store things like Pass or Fail, the type of test and the date of each test in one table. Use another table to store the personal details of each baby tested. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, tbResult stores the test result. The result of calculation (counting) based on tbResult are stored in Variables and the number of Variable will increase month after month. My problem is how to display these Variables (result) on a Form. I am thinking if I should use array. Any ideas? -- Jeff "Jeanette Cunningham" wrote: Hi Jeff, build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff . |
#5
|
|||
|
|||
Form to display result
Hi Jeanette,
Very glad that you can come back to my question. Yes, I have tbPatient to store babies' personal details. tbResult stores the test result (hearing test only). Although some baby may need to test a second time, each baby has only one record in tbResult. tbPatient and tbResult are one to one in relation. The fields in tbResult a ScreenDate1 for date, RResult1 for test result of right ear and LResult1 for test result of left ear tested the first time. RResult2 and LResult2 for right & left ear tested the second time. RResult1, LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule for final conclusion of "Pass" or "Fail" is: If the baby passes both ears for the first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either right or left ear fails or both ears fail the first time, we perform the test again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1, we give a "PASS". If either right or left ear fails or both ears fail this time, we give a "FAIL". Now, I want to general a statistic table to show the total number of babies tested, the number of babies passed or failed in each month. So, I need to search the field "ScreenDate1" in tbResult with DMin() to find the earliest date then count one month by one month until the end of tbResult. I use (a) DCount() to count the number of the babies who pass the first time (RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who fail the first time but pass the second time (RResult2=1 AND LResult2=1), (c)DCount to count those who fail the second time (RREsult2=2 OR/AND LResult2=2). The total number passed will be (a)+(b), and the total number tested will be (a)+(b)+(c). The results of counting are stored in "Variables", I'd like to display these results in a "Form" like a table with each month's result in a row. As time goes by, the records in tbResult will increase and the number of months (rows) to display will increase. Any ideas? Thank you. -- Jeff "Jeanette Cunningham" 來函: The usual way, using Access, is to use a query as the record source for a form. If you need calculated fields for your form, you can use the query to do the calculations, or you can use the form to do the calculations or you can use a combination of both query and form to do the calculations for the calculated fields. I would expect that you would store things like Pass or Fail, the type of test and the date of each test in one table. Use another table to store the personal details of each baby tested. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, tbResult stores the test result. The result of calculation (counting) based on tbResult are stored in Variables and the number of Variable will increase month after month. My problem is how to display these Variables (result) on a Form. I am thinking if I should use array. Any ideas? -- Jeff "Jeanette Cunningham" wrote: Hi Jeff, build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff . . |
#6
|
|||
|
|||
Form to display result
First, it is so much more difficult to learn about building forms when you
are using a one to one relationship. One to one relationships are very rare and are best avoided when learning how to use access. I suggest you make the relationship between tbPatient and tbResult one to many. One patient can have many results. This is how you do the testing anyway. For easy way to do the calculations for the statistics, redesign tbResult like this: tbResult------- PrimaryKey ResultID ForeignKey PatientID DateResult LEar - number, long integer REar - number, long integer If a baby passes the test for LEar, store 1 in the field LEar. If a baby fails the test for REar, store 2 in in the field REar. If a baby fails the first test, next time you test that baby, create a new record. It will have a later date than the first test for the same baby. This way you can easily find out how many tests a baby had. The general statistics table you mention does not need to be built ever. You can get the statistics in a query whenever you want them. Create a calculated field in a query built on both tbPatient and tbResult. In a blank column in the query grid in the Field row, type FinalResult: IIf([REar]+[LEar] = 2,"Pass","Fail") Use the query as the record source for a form. The form will show Pass or Fail each time each baby was tested. To get the total number of tests each month, it is fairly simple to create a totals query. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, Very glad that you can come back to my question. Yes, I have tbPatient to store babies' personal details. tbResult stores the test result (hearing test only). Although some baby may need to test a second time, each baby has only one record in tbResult. tbPatient and tbResult are one to one in relation. The fields in tbResult a ScreenDate1 for date, RResult1 for test result of right ear and LResult1 for test result of left ear tested the first time. RResult2 and LResult2 for right & left ear tested the second time. RResult1, LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule for final conclusion of "Pass" or "Fail" is: If the baby passes both ears for the first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either right or left ear fails or both ears fail the first time, we perform the test again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1, we give a "PASS". If either right or left ear fails or both ears fail this time, we give a "FAIL". Now, I want to general a statistic table to show the total number of babies tested, the number of babies passed or failed in each month. So, I need to search the field "ScreenDate1" in tbResult with DMin() to find the earliest date then count one month by one month until the end of tbResult. I use (a) DCount() to count the number of the babies who pass the first time (RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who fail the first time but pass the second time (RResult2=1 AND LResult2=1), (c)DCount to count those who fail the second time (RREsult2=2 OR/AND LResult2=2). The total number passed will be (a)+(b), and the total number tested will be (a)+(b)+(c). The results of counting are stored in "Variables", I'd like to display these results in a "Form" like a table with each month's result in a row. As time goes by, the records in tbResult will increase and the number of months (rows) to display will increase. Any ideas? Thank you. -- Jeff "Jeanette Cunningham" ??: The usual way, using Access, is to use a query as the record source for a form. If you need calculated fields for your form, you can use the query to do the calculations, or you can use the form to do the calculations or you can use a combination of both query and form to do the calculations for the calculated fields. I would expect that you would store things like Pass or Fail, the type of test and the date of each test in one table. Use another table to store the personal details of each baby tested. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, tbResult stores the test result. The result of calculation (counting) based on tbResult are stored in Variables and the number of Variable will increase month after month. My problem is how to display these Variables (result) on a Form. I am thinking if I should use array. Any ideas? -- Jeff "Jeanette Cunningham" wrote: Hi Jeff, build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff . . |
#7
|
|||
|
|||
Form to display result
Hi Jeanette,
I will try to redesign tbResult following your suggestion. Thank you very much. -- Jeff "Jeanette Cunningham" 來函: First, it is so much more difficult to learn about building forms when you are using a one to one relationship. One to one relationships are very rare and are best avoided when learning how to use access. I suggest you make the relationship between tbPatient and tbResult one to many. One patient can have many results. This is how you do the testing anyway. For easy way to do the calculations for the statistics, redesign tbResult like this: tbResult------- PrimaryKey ResultID ForeignKey PatientID DateResult LEar - number, long integer REar - number, long integer If a baby passes the test for LEar, store 1 in the field LEar. If a baby fails the test for REar, store 2 in in the field REar. If a baby fails the first test, next time you test that baby, create a new record. It will have a later date than the first test for the same baby. This way you can easily find out how many tests a baby had. The general statistics table you mention does not need to be built ever. You can get the statistics in a query whenever you want them. Create a calculated field in a query built on both tbPatient and tbResult. In a blank column in the query grid in the Field row, type FinalResult: IIf([REar]+[LEar] = 2,"Pass","Fail") Use the query as the record source for a form. The form will show Pass or Fail each time each baby was tested. To get the total number of tests each month, it is fairly simple to create a totals query. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, Very glad that you can come back to my question. Yes, I have tbPatient to store babies' personal details. tbResult stores the test result (hearing test only). Although some baby may need to test a second time, each baby has only one record in tbResult. tbPatient and tbResult are one to one in relation. The fields in tbResult a ScreenDate1 for date, RResult1 for test result of right ear and LResult1 for test result of left ear tested the first time. RResult2 and LResult2 for right & left ear tested the second time. RResult1, LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule for final conclusion of "Pass" or "Fail" is: If the baby passes both ears for the first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either right or left ear fails or both ears fail the first time, we perform the test again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1, we give a "PASS". If either right or left ear fails or both ears fail this time, we give a "FAIL". Now, I want to general a statistic table to show the total number of babies tested, the number of babies passed or failed in each month. So, I need to search the field "ScreenDate1" in tbResult with DMin() to find the earliest date then count one month by one month until the end of tbResult. I use (a) DCount() to count the number of the babies who pass the first time (RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who fail the first time but pass the second time (RResult2=1 AND LResult2=1), (c)DCount to count those who fail the second time (RREsult2=2 OR/AND LResult2=2). The total number passed will be (a)+(b), and the total number tested will be (a)+(b)+(c). The results of counting are stored in "Variables", I'd like to display these results in a "Form" like a table with each month's result in a row. As time goes by, the records in tbResult will increase and the number of months (rows) to display will increase. Any ideas? Thank you. -- Jeff "Jeanette Cunningham" ??: The usual way, using Access, is to use a query as the record source for a form. If you need calculated fields for your form, you can use the query to do the calculations, or you can use the form to do the calculations or you can use a combination of both query and form to do the calculations for the calculated fields. I would expect that you would store things like Pass or Fail, the type of test and the date of each test in one table. Use another table to store the personal details of each baby tested. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeff" wrote in message ... Hi Jeanette, tbResult stores the test result. The result of calculation (counting) based on tbResult are stored in Variables and the number of Variable will increase month after month. My problem is how to display these Variables (result) on a Form. I am thinking if I should use array. Any ideas? -- Jeff "Jeanette Cunningham" wrote: Hi Jeff, build a form that is based on tblResult. You can use the form wizard if you haven't built a form before. "Jeff" wrote in message ... Hi, The following is part of my program used to count the result of hearing test on baby and it works. If the baby passes the first time on both ears, we issue a pass. If the baby fails the first time, either on one ear or both ears, we perform a second time. If pass on both ears, we issue a pass. If fail either on one ear or both ears, we issue a fail. I can display the result with MsgBox, but I want to display the results on a "Form" like a table as follow: Year/Month of Test Total number done No. Pass No. Fail Pass rate Fail rate How can I do that? Should I store the results in a "temporary table" first then get the data from the temporary table to display them on a "Form"? Thank you. Private Sub btnCount_Click() Dim strMinDate As String Dim strMaxDate As String Dim dMinDate As Date Dim dMaxDate As Date Dim dScreenMonth As Date Dim intMonthCount As Integer Dim lngPass1 As Long Dim lngPass2 As Long Dim lngPass As Long Dim lngFail2 As Long strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for ScreenDate1 strMaxDate = DMax("[ScreenDate1]", "tbResult") dMinDate = TxtDate(strMinDate) 'I have a function to convert String type ScreenDate1 to Date type dMaxDate = TxtDate(strMaxDate) dScreenMonth = dMinDate intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1 Dim i As Integer Dim strPass As String Dim strFail As String 'pass=1, fail=2 For i = 1 To intMonthCount lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And [LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And [LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") lngPass = lngPass1 + lngPass2 lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'") strPass = strPass & lngPass & " " strFail = strFail & lngFail2 & " " dScreenMonth = DateAdd("m", 1, dScreenMonth) Next MsgBox strPass & strFail End Sub -- Jeff . . . |
Thread Tools | |
Display Modes | |
|
|