how can I replace SQL query with VBA logic

[mls via AccessMonster.com]

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

 

[Tom van Stiphout]

On Thu, 13 May 2010 13:38:37 GMT, "mls via AccessMonster.com"

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

 

[mls via AccessMonster.com]

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

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

 

[Tom van Stiphout]

On Thu, 13 May 2010 18:54:59 GMT, "mls via AccessMonster.com"

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

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

 

[mls via AccessMonster.com]

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

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]

 

[John W. Vinson]

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.

 

[mls via AccessMonster.com]

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.

 

[mls via AccessMonster.com]

Sorry, I mean TestNo for first record should not change as this sample has
single record.

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[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.

 

[Tom van Stiphout]

On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com"

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

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

 

[John W. Vinson]

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!)

 

[mls via AccessMonster.com]

John, Thank you so much. Your query worked perfectly when I added one more
condition A.group='IG'. With this solution I don't have to use VBA code which
has recordset etc. I can directly insert this query into my automatic process.
I never used nested query in update statement before this gave me new ideas
as well. I have to master the nested queries more.
Thanks again.

Sample group TestNo
29045 IG Test 1

[quoted text clipped - 6 lines]

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!)