CountIf Access

[icepirates1234]

Hello, Im in 'Table' view right now...and in
Column 23 (Column Letter W) if you had to assign a letter, in my list there are sets of numbers like this:
02-03-005-09-W2
02-03-005-09-W2

The reason there is two numbers is because one is volume 1, one is volume 2...

The table Im using is 100,000 lines...And Im trying to develop a Macro that I can run that will automatically look at the numbers and when it see's two of the same it will add a 1.0, 2.0, and so on in the column next to the column that has the numbers.

The column thats beside the number column is called Volumes and is column 24....When it only finds one number it will just be 1.0...

I have a macro that does this in Excel
Private Sub CommandButton1_Click()
With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 1)
.FormulaR1C1 = "=COUNTIF(R1C1:RC1,RC1)"
.NumberFormat = "0.0"
Remove this comment if wish for formulae to persist in Column B rather than just result
.Value = .Value
End With
End Sub


Which works, but when using Access, and going into the VBE editor, Im not quite sure how do apply this same concept...
(Ive tried using what I have above, and it doesn't work)

Any Help would be great.

 

[zevw]

I don't understand exactly what you want.

The first thing the table should be sorted or idexed.

Secondly create a recordset

that goes through the table have 2 variables one with the previous value and the second with the new value if they match assign 2.0 to the new value. You are always assigning 1.0 anyway to the first value you encounter.

I hope this gives you some idea!

 

[MajP]

In Access you not do this in code but with an update query. It probably will be helpful for you to give the real names of your fields(columns).

lets assume your table is called "tblExcel", and you have a field called "autoID" a unique ID for that record (row), orderNumber (this thing like 02-03-005-09-W), and a new blank field called "volNumber". So my data looks like this

autoID	  	OrderNumber  VolNumber
1		10249
2		10249
3		10256
4		10256
5		10259
6		10259
7		10261
8		10261
9		10264
[code]

You go to the query wizard and select new query and pick tblExcel.  Then select "query", "update query".  In the update line type "1.0".
run that query and
[code]
autoID	OrderNumber	volNumber
1	10249	       1.0
2	10249	       1.0
3	10256	       1.0
4	10256	       1.0
5	10259	       1.0
6	10259	       1.0
7	10261	       1.0
8	10261	       1.0
9	10264	       1.0
[code]

Now writ a quick code to find all records where there are two of the same OrderNumber.  This is done using an aggregate query.  It is simple to do using the query builder but hard to explain.  Look at the help file.
But this gives me
[code]
SecondID	OrderNumber
2	        10249
4	        10256
6	        10259
8	        10261
10	        10264

The secondID is the ID of the row(record) of the second volume.

Now Run a simple update query on these records putting in "2.0"

autoID	OrderNumber	volNumber
1	10249	1.0
2	10249	2.0
3	10256	1.0
4	10256	2.0
5	10259	1.0
6	10259	2.0
7	10261	1.0
8	10261	2.0
9	10264	1.0

 

[MajP]

If you put the real names of your fields it will be easier to give you the SQL string than try to explain how to do it.

 

[icepirates1234]

Hey,

Thanks for your time...Ill explain a little more...
There is a ID column that automatically gets assigned when a new record is entered.

The data is already in order...but there can be three instances of a number like this:
RowID Number VolumeNum
1 10-02-003-04-W5
2 10-02-003-04-W5
3 10-02-003-04-W5
4 11-21-009-06-w2
But the table goes down to about 100,000 lines and I need a way of just running a Macro that will automatically look at the 'Number' column find instances of the same number and assign 1.0, 2.0, 3.0, 4.0 and so on in the VolumeNum column.

I think your solution will work, so Im going to give it a shot tomorrow (Thursday, February 19th/2009) and Ill post my results Thursday and let you know how it works out...

Thanks again!

 

[PHV]

A simple update query (SQL code):

UPDATE yourTable
SET VolumeNum = DCount("*","yourTable","Number='" & [Number] & "' AND RowID<=" & [RowID])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[icepirates1234]

Ok, now that I have tested a few things out, I also made some sample data with 9 rows, and attached it to this post.

All the attempts and suggestions I tried failed...

Im thinking this could work:

UPDATE WELLS
SET VolumeNum = DCount("*","WELLS","Number='" & [Number] & "' AND RowID<=" & [RowID])

But I edited this query a bit, and went to Tooks > Macro > Visual Basic Editor, and inserted this code, and had no luck...so any suggestions would be good.

To explain a little further, I have 128,000 lines in an Access table called WELLS...When opening my attached sample data you want to look for the UWI column, then the column beside it called 'VOL'...I need something that can look through all the lines and find the alike numbers and assign a volume number beside each one.

All the numbers are already in order, I need a query that just needs to find alike numbers like:
100/07-08-004-02-W5
100/07-08-004-02-W5
and in the VOL column beside the numbers assign a 1, then a 2, then 3 and so on, depending on how many instances of the number the query finds.

let me know any suggestions