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
|
|||
|
|||
how can I replace SQL query with VBA logic
I have a table which looks like the following..
Sample group TestNo 29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 and I need to correct my TestNo, If the sample is same and group is different then I need to assign different no, My query looks like this.. DoCmd.RunSQL "UPDATE tbl_Results set TestNo='Test 2' where group='IG' and TestNo='Test 1';" this query updates every row with IG whcih is not correct. I wan to update only if the sample is same and group is different. I want to get values of FixedNo and not ErrorNo. For this I need to use count(*) and having having count 2 but how can I do this. Is there a way to do this in VBA? ErrorNO FixedNo Test 2 Test 1 Test 2 Test 2 Test 1 Test 1 Test 2 Test 2 Test 1 Test 1 Thanks a lot -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#2
|
|||
|
|||
how can I replace SQL query with VBA logic
On Thu, 13 May 2010 13:38:37 GMT, "mls via AccessMonster.com"
u55943@uwe wrote: dim rs as dao.recordset set rs=currentdb.openrecordset("select * from tblResults order by Sample, Group", dbOpenDynaset while not rs.eof 'do your processing rs.edit rs!TestNo = "???" rs.update rs.movenext wend rs.close Note the orderby clause in the sql statement. It is important to understand a table is unordered until we add an orderby clause. The processing code may involve keeping the values of the last-visited row, and comparing with the current row. I didn't write this code because it depends on the finer points of what you really want. You can probably handle that. -Tom. Microsoft Access MVP I have a table which looks like the following.. Sample group TestNo 29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 and I need to correct my TestNo, If the sample is same and group is different then I need to assign different no, My query looks like this.. DoCmd.RunSQL "UPDATE tbl_Results set TestNo='Test 2' where group='IG' and TestNo='Test 1';" this query updates every row with IG whcih is not correct. I wan to update only if the sample is same and group is different. I want to get values of FixedNo and not ErrorNo. For this I need to use count(*) and having having count 2 but how can I do this. Is there a way to do this in VBA? ErrorNO FixedNo Test 2 Test 1 Test 2 Test 2 Test 1 Test 1 Test 2 Test 2 Test 1 Test 1 Thanks a lot |
#3
|
|||
|
|||
how can I replace SQL query with VBA logic
Thanks Tom. It worked but I could not fix the group by clause in the code, it
is giving me ERRORNo values as I am not using group by clause. I tried the this: int1 = DCount("*", "tbl_results", "sample), this is showing syntax error .. ErrorNO FixedNo Test 2 Test 1 Test 2 Test 2 Test 1 Test 1 Test 2 Test 2 Test 1 Test 1 Sub Testno() Dim rs As dao.Recordset Dim cnt1 As Integer Set rs = "CurrentDb.OpenRecordset(select * from tbl_results order by sample, group, dbOpenDynaset)" While Not rs.EOF cnt1 = DCount("sample", "tbl_results") If cnt1 1 Then rs.Edit If (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value = "Test 1") Then rs!Testno = "Test 1" ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value = "Test 1") Then rs!Testno = "Test 2" ElseIf (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value = "Test 3") Then rs!Testno = "Test 3" ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value = "Test 3") Then rs!Testno = "Test 4" End If End If rs.Update rs.MoveNext Wend rs.Close End Sub Tom van Stiphout wrote: dim rs as dao.recordset set rs=currentdb.openrecordset("select * from tblResults order by Sample, Group", dbOpenDynaset while not rs.eof 'do your processing rs.edit rs!TestNo = "???" rs.update rs.movenext wend rs.close Note the orderby clause in the sql statement. It is important to understand a table is unordered until we add an orderby clause. The processing code may involve keeping the values of the last-visited row, and comparing with the current row. I didn't write this code because it depends on the finer points of what you really want. You can probably handle that. -Tom. Microsoft Access MVP I have a table which looks like the following.. Sample group TestNo [quoted text clipped - 20 lines] Thanks a lot -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
how can I replace SQL query with VBA logic
On Thu, 13 May 2010 18:54:59 GMT, "mls via AccessMonster.com"
u55943@uwe wrote: The syntax error is because you don't have a closing double-quote: int1 = DCount("*", "tbl_results", "sample") But that is probably not what you meant to do, since "sample" is not a normally-formatted criteria string. Normally you would see "sample=29045" or some such. -Tom. Microsoft Access MVP Thanks Tom. It worked but I could not fix the group by clause in the code, it is giving me ERRORNo values as I am not using group by clause. I tried the this: int1 = DCount("*", "tbl_results", "sample), this is showing syntax error .. ErrorNO FixedNo Test 2 Test 1 Test 2 Test 2 Test 1 Test 1 Test 2 Test 2 Test 1 Test 1 Sub Testno() Dim rs As dao.Recordset Dim cnt1 As Integer Set rs = "CurrentDb.OpenRecordset(select * from tbl_results order by sample, group, dbOpenDynaset)" While Not rs.EOF cnt1 = DCount("sample", "tbl_results") If cnt1 1 Then rs.Edit If (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value = "Test 1") Then rs!Testno = "Test 1" ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value = "Test 1") Then rs!Testno = "Test 2" ElseIf (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value = "Test 3") Then rs!Testno = "Test 3" ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value = "Test 3") Then rs!Testno = "Test 4" End If End If rs.Update rs.MoveNext Wend rs.Close End Sub Tom van Stiphout wrote: dim rs as dao.recordset set rs=currentdb.openrecordset("select * from tblResults order by Sample, Group", dbOpenDynaset while not rs.eof 'do your processing rs.edit rs!TestNo = "???" rs.update rs.movenext wend rs.close Note the orderby clause in the sql statement. It is important to understand a table is unordered until we add an orderby clause. The processing code may involve keeping the values of the last-visited row, and comparing with the current row. I didn't write this code because it depends on the finer points of what you really want. You can probably handle that. -Tom. Microsoft Access MVP I have a table which looks like the following.. Sample group TestNo [quoted text clipped - 20 lines] Thanks a lot |
#5
|
|||
|
|||
how can I replace SQL query with VBA logic
Tom, I fixed the syntax error.
If the sample appears twice in the list then only I have to execute my code and change the TestNo. In order to capture the duplicate sampleid I am using dcount function to count. I can't use the WHERE clause as my samples change each and every time and I don't know what they will be. Thanks Tom van Stiphout wrote: The syntax error is because you don't have a closing double-quote: int1 = DCount("*", "tbl_results", "sample") But that is probably not what you meant to do, since "sample" is not a normally-formatted criteria string. Normally you would see "sample=29045" or some such. -Tom. Microsoft Access MVP Thanks Tom. It worked but I could not fix the group by clause in the code, it is giving me ERRORNo values as I am not using group by clause. [quoted text clipped - 65 lines] Thanks a lot -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#6
|
|||
|
|||
how can I replace SQL query with VBA logic
On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com" u55943@uwe
wrote: Tom, I fixed the syntax error. If the sample appears twice in the list then only I have to execute my code and change the TestNo. In order to capture the duplicate sampleid I am using dcount function to count. I can't use the WHERE clause as my samples change each and every time and I don't know what they will be. You certainly do NOT need to know the sample number to find duplicates. Could you explain the logic which identifies which records need to be updated? A Query referencing the table should certainly be able to do this. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
how can I replace SQL query with VBA logic
Sample group TestNo
29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 John, In the above table, I want to update Testno, only for the records which has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record should not change. Thanks John W. Vinson wrote: Tom, I fixed the syntax error. If the sample appears twice in the list then only I have to execute my code and change the TestNo. In order to capture the duplicate sampleid I am using dcount function to count. I can't use the WHERE clause as my samples change each and every time and I don't know what they will be. You certainly do NOT need to know the sample number to find duplicates. Could you explain the logic which identifies which records need to be updated? A Query referencing the table should certainly be able to do this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#8
|
|||
|
|||
how can I replace SQL query with VBA logic
Sorry, I mean TestNo for first record should not change as this sample has
single record. mls wrote: Sample group TestNo 29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 John, In the above table, I want to update Testno, only for the records which has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record should not change. Thanks Tom, I fixed the syntax error. If the sample appears twice in the list then only I have to execute my code [quoted text clipped - 5 lines] you explain the logic which identifies which records need to be updated? A Query referencing the table should certainly be able to do this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#9
|
|||
|
|||
how can I replace SQL query with VBA logic
On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com"
u55943@uwe wrote: Sorry, MLS, but it seems apparent you need professsional programming assistance to complete this task. In my mind one of the problems is that the criteria have not rigorously been defined. If they have in your mind, then the issue is of communicating this to the developer. -Tom. Microsoft Access MVP Tom, I fixed the syntax error. If the sample appears twice in the list then only I have to execute my code and change the TestNo. In order to capture the duplicate sampleid I am using dcount function to count. I can't use the WHERE clause as my samples change each and every time and I don't know what they will be. Thanks Tom van Stiphout wrote: The syntax error is because you don't have a closing double-quote: int1 = DCount("*", "tbl_results", "sample") But that is probably not what you meant to do, since "sample" is not a normally-formatted criteria string. Normally you would see "sample=29045" or some such. -Tom. Microsoft Access MVP Thanks Tom. It worked but I could not fix the group by clause in the code, it is giving me ERRORNo values as I am not using group by clause. [quoted text clipped - 65 lines] Thanks a lot |
#10
|
|||
|
|||
how can I replace SQL query with VBA logic
On Fri, 14 May 2010 16:36:41 GMT, "mls via AccessMonster.com" u55943@uwe
wrote: Sample group TestNo 29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 John, In the above table, I want to update Testno, only for the records which has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record should not change. UPDATE tablename AS A SET [TestNo] = "Test 2" WHERE EXISTS (SELECT B.[Sample] FROM tablename AS B WHERE B.Sample = A.Sample AND B.Group A.Group) should work, if I understand you correctly (but back up your database first because I probably don't!) -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|