Fastest way to to achieve results from query (1 Viewer)

[radek225]

I need to check, is some duplicated records or not (by column which is text) in my table before do my code. so I wrote the query which is working fine but slow:/ - that's the problem. Is there any fastest way to check that what my query do?

strSQL = "Select count(nazwa) " & _
"from tblGoraZleceniaZakonczone " & _
"where id_zlecenia='" & idStarego & "' " & _ 
"group by nazwa " & _
"having count(nazwa)>" & 1
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
...

 

[Uncle Gizmo]

Do you mean you want to find duplicates in different columns in your table?

 

[radek225]

Do you mean you want to find duplicates in different columns in your table?

I want to know if there are duplicate records tblGoraZleceniaZakonczone.nazwa in tblGoraZleceniaZakonczone where tblGoraZleceniaZakonczone.id_zlecenia = idStarego


tblGoraZleceniaZakonczone.nazwa - text field
tblGoraZleceniaZakonczone.id_zlecenia - text field
idStarego - string variable

So i don't need to find these records. I just want to know that there are duplicates or not

 

[Uncle Gizmo]

tblCondition
SELECT ID, fldColumn1, fldColumn2
FROM tblCondition;

qUnionOfFld1Fld2
SELECT fldColumn1
FROM tblCondition
UNION ALL SELECT fldColumn2
FROM tblCondition;

qCountOfDuplicates
SELECT First(qUnionOfFld1Fld2.fldColumn1) AS [fldColumn1 Field], Count(qUnionOfFld1Fld2.fldColumn1) AS NumberOfDups
FROM qUnionOfFld1Fld2

See attached example dB ...

 

[radek225]

tblCondition
SELECT ID, fldColumn1, fldColumn2
FROM tblCondition;

qUnionOfFld1Fld2
SELECT fldColumn1
FROM tblCondition
UNION ALL SELECT fldColumn2
FROM tblCondition;

qCountOfDuplicates
SELECT First(qUnionOfFld1Fld2.fldColumn1) AS [fldColumn1 Field], Count(qUnionOfFld1Fld2.fldColumn1) AS NumberOfDups
FROM qUnionOfFld1Fld2

See attached example dB ...

Yes I see you do it exactly what i made. But I try to understand logic of sql and recordset. This is only a part from my code. I need to use information about duplicates later. So there isn't fastest way (time when computer doing code) to check these information about duplicates than write string query and use RecrodCount dao.Recordset to achieve?

 

[Uncle Gizmo]

If you have two Fields which you suspect have duplicate data in then that's an indication that you shouldn't have two separate Fields you should have the information in just one field. I think if you did it like that you would probably improve the speed.

See this article HERE:- Excel In Access

You need to "Transpose" the data....

 

[sneuberg]

If DCount ("[nazwa] ","[tblGoraZleceniaZakonczone]","[id_zlecenia] ='" & Starego & "'") > 0 Then

would be more concise, but probably not faster. Do you have indexes on nazwa and id_ziecenia? If not I suggest trying that.

 

[radek225]

If DCount ("[nazwa] ","[tblGoraZleceniaZakonczone]","[id_zlecenia] ='" & Starego & "'") > 0 Then

would be more concise, but probably not faster. Do you have indexes on nazwa and id_ziecenia? If not I suggest trying that.

Yes I have an index on id_zlecenia. If field is indexed then we should use recordset because it's faster than vba like dcount....am I right?

About recordset maybe instead of .recordcount there is some faster way to find out that there is some records or not?

it's faster using recordset 2 istead of recordset?

 

[sneuberg]

If field is indexed then we should use recordset because it's faster than vba like dcount....am I right?

If this is true, then whoever created the code for DCount really screwed up. I hope other forum members can tell you if that's true or not.

About recordset maybe instead of .recordcount there is some faster way to find out that there is some records or not?

How about testing the end of file and beginning of file. If both are true, there are no records, i.e.,

If rs.EOF AND rs.BOF Then
'there are no records

You could test some of these ideas by using something like the code below and then you'd be the expert.

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

 

[radek225]

If this is true, then whoever created the code for DCount really screwed up. I hope other forum members can tell you if that's true or not.

what do you mean?

 

[sneuberg]

what do you mean?

I mean that if you can get the results that DCount gives faster by other means then the folks at Microsoft who wrote the code for DCount should have incorporated those other means into the code for DCount