Hey @ all,

I need to figure out how records can be counted within a recordset throuh a
SQL statement. I 've got a identifyable number which is repeating and needs
to converted depending on additional information. As it is easier to
understand it by looking on an example please see below.

State of the art:

ID Value1

234 2
234 2
234 3
365 2
365 2

The aim is to convert it as it is attached:

ID Value1 Count(Records)

234 2 2
234 3 1
365 2 2

Any support is greatly appreciated. Thank you very much in advance.

Kind regards

malte

I think you can use the DCount() aggregate function here, although, I must
say, any db that has an ID field that has non-unique values most likely
suffers from some severe normalization issues.

But anyway, to get a count on the number of records that have an ID of 234,
something like this might work

YourCount = DCount("ID", "yourtable", "ID = 234")


I notice in your second example, ID 234 is listed twice, the first one
having a count of 2 and the second 234 having a count of 1? Not sure what
you're looking for...


In a worst case scenario, you could open the recordset in DAO and run some
code to evaluate it.


Dim strSQL As String
Dim rs As DAO.Recordset
Dim lCount As Long

strSQL = "your sql string here"
Set rs = CurretDB.OpenRecordset(strSQL)

'If there is any records, the RecordCount will
'read 1 and this point (not the actual count)

If rs.RecordCount <> 0 Then

'Move to the last record to obtain the full count
rs.MoveLast
lCount = rs.Recordcount

Else

lCount = 0

End If

rs.Close: Set rs = Nothing





Often times when attempting to manually normalize data (take data such as
this and put certain information to it to make it normal), it's much easier
to do it yourself rather than writing code. Code to manipulate every record
based on some strange data that is in there in a nonconsistent manner is
usually a complete pain.

So I'm not sure exactly what you're looking for, but maybe this will ring a
bell somewhere.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"BusyProfessorFromBremen" wrote:

> Hey @ all,
>
> I need to figure out how records can be counted within a recordset throuh a
> SQL statement. I 've got a identifyable number which is repeating and needs
> to converted depending on additional information. As it is easier to
> understand it by looking on an example please see below.
>
> State of the art:
>
> ID Value1
>
> 234 2
> 234 2
> 234 3
> 365 2
> 365 2
>
> The aim is to convert it as it is attached:
>
> ID Value1 Count(Records)
>
> 234 2 2
> 234 3 1
> 365 2 2
>
> Any support is greatly appreciated. Thank you very much in advance.
>
> Kind regards
>
> malte

That appers to be a rather simple aggregate query
SELECT ID, Value1, Count(Value1) as TheCount
FROM YourTable
GROUP BY ID, Value1

In query design view
-- add your table
-- add the id field once and the value1 field twice
-- Select VIEW Totals from the menu
-- Change GROUP BY to COUNT under one of the Value1 fields
-- Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


BusyProfessorFromBremen wrote:
> Hey @ all,
>
> I need to figure out how records can be counted within a recordset throuh a
> SQL statement. I 've got a identifyable number which is repeating and needs
> to converted depending on additional information. As it is easier to
> understand it by looking on an example please see below.
>
> State of the art:
>
> ID Value1
>
> 234 2
> 234 2
> 234 3
> 365 2
> 365 2
>
> The aim is to convert it as it is attached:
>
> ID Value1 Count(Records)
>
> 234 2 2
> 234 3 1
> 365 2 2
>
> Any support is greatly appreciated. Thank you very much in advance.
>
> Kind regards
>
> malte

BusyProfessorFromBremen wrote:
>I need to figure out how records can be counted within a recordset throuh a
>SQL statement. I 've got a identifyable number which is repeating and needs
>to converted depending on additional information. As it is easier to
>understand it by looking on an example please see below.
>
>State of the art:
>
>ID Value1
>
>234 2
>234 2
>234 3
>365 2
>365 2
>
>The aim is to convert it as it is attached:
>
>ID Value1 Count(Records)
>
>234 2 2
>234 3 1
>365 2 2


Without more details about your "recordset", I think maybe
you want to use a query like:

SELECT ID, Value1, Count(*) As RecCount
FROM sometable
GROUP BY ID, Value1

--
Marsh
MVP [MS Access]