Why does a record count in a query (with "group by") indicates more records than there are?

  • Thread starter Thread starter Hyphessobricon
  • Start date Start date
H

Hyphessobricon

Hallo,

Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
 
Hyphessobricon said:
Hallo,

Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
Click to expand...

Perhaps it's wise to post the query?

FB

--
 
Frans Bouma said:
Perhaps it's wise to post the query?

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...

So, in my Module1
the count says : 5 row in table
and th'n an error no row at 2
in the query there are indeed only 2 rows, an din the table there are 5, but
I work with the query.

Private Sub brandstof1() 'huisbrandolie

i = dtqBs1.Rows.Count '=dtBS1 is for the tbl, dtqBs1 is the query,
everything containing q is for the query

MsgBox("brandstofrijen " & i) '= here I get the 5 rows

Dim dattum As Date

For i = 0 To i - 1

'query gebruikt om de laatst ingevoerde te tonen

If dsqBrandstof1.Tables(0).Rows(i).Item("Brandstofjaar") = CInt(Year(Now()))
Then '= the query dataset

frmVariabele.lblStookVJ.Text = Format(dsqBrandstof1.Tables(0).Rows(i -
1).Item("SomvanBrandstof"), "0.00")

frmVariabele.txtGasT.Text =
Format(dsqBrandstof1.Tables(0).Rows(i).Item("SomvanBrandstof"), "0.00")

frmVariabele.lblVersStook.Text = Format(Val(frmVariabele.lblStookVJ.Text) -
Val(frmVariabele.txtGasT.Text), "0.00")

End If

Next i

i = dsBrandstof1.Tables(0).Rows.Count =that is the regular table dataset

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum") > dattum Then

dattum = dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum")

End If

Next i

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum") = dattum Then

frmVariabele.txtGas.Text =
Format(dsBrandstof1.Tables(0).Rows(i).Item("Brandstof"), "0.00")

frmVariabele.txtGasDat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofinfo")

frmVariabele.txtGadat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum")

End If

Next

End Sub
 
Hyphessobricon said:
So, in my Module1
the count says : 5 row in table
and th'n an error no row at 2
in the query there are indeed only 2 rows, an din the table there are 5, but
I work with the query.

Private Sub brandstof1() 'huisbrandolie

i = dtqBs1.Rows.Count '=dtBS1 is for the tbl, dtqBs1 is the query,
everything containing q is for the query

MsgBox("brandstofrijen " & i) '= here I get the 5 rows

Dim dattum As Date

For i = 0 To i - 1

'query gebruikt om de laatst ingevoerde te tonen

If dsqBrandstof1.Tables(0).Rows(i).Item("Brandstofjaar") = CInt(Year(Now()))
Then '= the query dataset

frmVariabele.lblStookVJ.Text = Format(dsqBrandstof1.Tables(0).Rows(i -
1).Item("SomvanBrandstof"), "0.00")

frmVariabele.txtGasT.Text =
Format(dsqBrandstof1.Tables(0).Rows(i).Item("SomvanBrandstof"), "0.00")

frmVariabele.lblVersStook.Text = Format(Val(frmVariabele.lblStookVJ.Text) -
Val(frmVariabele.txtGasT.Text), "0.00")

End If

Next i

i = dsBrandstof1.Tables(0).Rows.Count =that is the regular table dataset

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum") > dattum Then

dattum = dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum")

End If

Next i

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum") = dattum Then

frmVariabele.txtGas.Text =
Format(dsBrandstof1.Tables(0).Rows(i).Item("Brandstof"), "0.00")

frmVariabele.txtGasDat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofinfo")

frmVariabele.txtGadat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatum")

End If

Next

End Sub
Click to expand...

No I need the SQL query, which performs the group by. Also, if the
columns in the group by are in the wrong order, you might get different
results. Also try COUNT(DISTINCT columnname) which might help.

(en je hebt een typo: dattum -> datum ;))

FB

--
 
No I need the SQL query, which performs the group by. Also, if the columns
in the group by are in the wrong order, you might get different results.
Also try COUNT(DISTINCT columnname) which might help.

(en je hebt een typo: dattum -> datum ;))

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...





Hallo Frans,

Van harte bedankt voor respons.
dattum is een variabele met die naam.
Maar Distinct kan me inderdaad helpen.

Hyphessobrycon
 
Back
Top