I need the count the number of entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need the count the number of entries in each grouping:

This is my data:
CHPSN CHDOCO EXTCOST
17540678 60117419 40.44
17540678 60117419 40.44
17540678 60117419 40.44
17540693 60119216 24.56
17540693 60119216 24.56
17602258 60132016 64
17602258 60132016 64
17602338 60135398 0

I want to create a new field called NewFieldDups that will assign a number
to each record. My data should look this this when complete:

CHPSN CHDOCO EXTCOST NewFieldDups
17540678 60117419 40.44 1
17540678 60117419 40.44 2
17540678 60117419 40.44 3
17540693 60119216 24.56 1
17540693 60119216 24.56 2
17602258 60132016 64 1
17602258 60132016 64 2
17602338 60135398 0 1

Thanks.
 
Do you have another field in the table like counter or DateInsert that define
each record?
 
Yes there is an "id" field.

Ofer said:
Do you have another field in the table like counter or DateInsert that define
each record?


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck
Click to expand...
 
Should the number start from 1 when the field CHPSN change, or when the
combination of CHPSN and CHDOCO changes?
 
You need to combine a query with a function.
Copy this function to any module
Function ReturnRecordCount(CHPSN As String, CHDOCO As String)
If CHPSN <> GlobCHPSN Or CHDOCO <> GlobCHDOCO Then
GlobCHPSN = CHPSN
GlobCHDOCO = CHDOCO
MyCount = 1
Else
MyCount = MyCount + 1
End If
ReturnRecordCount = MyCount
End Function
=========================================
Create Global variable in the module
Option Compare Database
Global GlobCHPSN As String, MyCount As Integer, GlobCHDOCO As String
=========================================
Create a query that sort by the two field and call the function passing the
two fields

SELECT TableName.CHPSN, TableName.CHDOCO, TableName.EXTCOST ,
ReturnRecordCount([CHPSN],[CHDOCO]) AS NewFieldDups
FROM TableName
ORDER BY TableName.CHPSN, TableName.CHDOCO

Don't forget to change the TableName in the SQL
 
Why do you need to do this? Do you need the information in a report? If
so, there is a very simple method to get a one-up number in the report that
will restart with each group of records.

If you need to do this in a query then you can do it in an SQL statement
using a correlated sub-query.


SELECT CHPSN, CHDOCO, EXTCOST, [IdField],
(SELECT Count(*) FROM [YourTable] as Tmp WHERE Tmp.Chpsn =
[YourTable]Chpsn AND Tmp.Chdoco = [YourTable].Chdoco AND tmp.IdField<=
[YourTable].IdField) as NewFieldDups
FROM [YourTable] as M

In the query grid, in a new column, enter the following
Field: NewFieldDups:(SELECT Count(*) FROM [YourTable] as Tmp WHERE Tmp.Chpsn
= [YourTable]Chpsn AND Tmp.Chdoco = [YourTable].Chdoco AND tmp.IdField<=
[YourTable].IdField)
 
The end result is that I need to take all the entries with a "1" and put them
into one file and all the others into another file.

John Spencer said:
Why do you need to do this? Do you need the information in a report? If
so, there is a very simple method to get a one-up number in the report that
will restart with each group of records.

If you need to do this in a query then you can do it in an SQL statement
using a correlated sub-query.


SELECT CHPSN, CHDOCO, EXTCOST, [IdField],
(SELECT Count(*) FROM [YourTable] as Tmp WHERE Tmp.Chpsn =
[YourTable]Chpsn AND Tmp.Chdoco = [YourTable].Chdoco AND tmp.IdField<=
[YourTable].IdField) as NewFieldDups
FROM [YourTable] as M

In the query grid, in a new column, enter the following
Field: NewFieldDups:(SELECT Count(*) FROM [YourTable] as Tmp WHERE Tmp.Chpsn
= [YourTable]Chpsn AND Tmp.Chdoco = [YourTable].Chdoco AND tmp.IdField<=
[YourTable].IdField)

Gloria Lewis said:
It should change to 1 when the combination of CHPSN and CHDOCO changes.
Click to expand...
Click to expand...
 
Thanks, this worked perfectly.

Ofer said:
You need to combine a query with a function.
Copy this function to any module
Function ReturnRecordCount(CHPSN As String, CHDOCO As String)
If CHPSN <> GlobCHPSN Or CHDOCO <> GlobCHDOCO Then
GlobCHPSN = CHPSN
GlobCHDOCO = CHDOCO
MyCount = 1
Else
MyCount = MyCount + 1
End If
ReturnRecordCount = MyCount
End Function
=========================================
Create Global variable in the module
Option Compare Database
Global GlobCHPSN As String, MyCount As Integer, GlobCHDOCO As String
=========================================
Create a query that sort by the two field and call the function passing the
two fields

SELECT TableName.CHPSN, TableName.CHDOCO, TableName.EXTCOST ,
ReturnRecordCount([CHPSN],[CHDOCO]) AS NewFieldDups
FROM TableName
ORDER BY TableName.CHPSN, TableName.CHDOCO

Don't forget to change the TableName in the SQL
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Gloria Lewis said:
It should change to 1 when the combination of CHPSN and CHDOCO changes.
Click to expand...
Click to expand...
 
Back
Top