Count-If

[CHOMPGATOR]

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 tips would be great.

 

[jal]

Let's assume the column is called Volume, and the table is called tblData. Let's also assume it has an ID-number column (preferably autonumber column) called autoNum. You probably want something like this:

SELECT T1.*, (SELECT Count(t2.Volume) FROM tblData as t2 WHERE t1.Volume = t2.Volume AND t1.AutoNum <= t2.AutoNum) as TheOrdinal
FROM tblData as T1

The only problem I see is this won't necessarily give you the "order" you want. In other words if you have three repeats in a row it doesn't know which one should be labeled 3, which one should be labeled 2, and which should be labeled 1.


If the table doesn't have an autonumber column, you can add one by pasting this command into SQL view and running it.

ALTER TABLE tblData ADD COLUMN autonumm COUNTER

 

[CHOMPGATOR]

Hey,

Thanks for the update...There is an ID Column that automatically gets assigned when a new record is entered. There are very other columns, but for simplicity, I tried to shorten my post to just what my problem is!

Thanks for your time and letting me know a solution.

How the table is laid out, there may be three instances of this number
01-02-003-04-W2
01-02-003-04-W2
01-02-003-04-W2

The data was entered very well by users, so the first number will always be Volume 1, the second will always be Volume 2, the third will always be Volume 3...
Does that give you any additional information for making a query/script that will automatically assign volume numbers?

If not thats ok, Im going to try your solution, and I have one other solution Im going to attempt and Ill post my results here on
Thursday, February 18/2009.

Thanks again!

 

[fermunky]

It may be easier to put it back into excel, make a new column next to the one with the numbers, sort the numbers column so the like items are next to each other, and have a simple formula return something when the cell next to it is equal to the cell below it. If you only have items that may repeat a handful of times, just manually give them their version numbers and import it into the DB...

 

[fermunky]

Here's an example Excel. The third column had a formula looking for "Duplicate" next to it, and left the cell empty if Duplicate was there, so you could manually add a version number. Later you could copy that column and paste the values to remove all formulas. The formula is really only needed I guess if all the items need a version number, including the ones that aren't duplicates...

 

[jal]

Hey,

Thanks for the update...There is an ID Column that automatically gets assigned when a new record is entered. There are very other columns, but for simplicity, I tried to shorten my post to just what my problem is!

Thanks for your time and letting me know a solution.

How the table is laid out, there may be three instances of this number
01-02-003-04-W2
01-02-003-04-W2
01-02-003-04-W2

The data was entered very well by users, so the first number will always be Volume 1, the second will always be Volume 2, the third will always be Volume 3...
Does that give you any additional information for making a query/script that will automatically assign volume numbers?

Yes, that information is helpful. If you use this column as the "AutoNum" column in my code above, the order should be preserved (I haven't tested this). If it comes out in reverse order, than just try some kind of reversal, for example change this:

t1.AutoNum <= t2.AutoNum

to this:


t2.AutoNum <= t1.AutoNum

 

[CHOMPGATOR]

Hey All,

Ok, I tried several examples, none worked.

To elaborate further, the numbers are already in the proper 'Order' they need to be in. What needs to happen now is just a query, or some sort of Macro needs should be executed that looks for duplicate numbers and in the VOL column at the first number it finds it assigns
1.0
then when it finds the same number just on the next line, the script then assigns
2.0
then
3.0

And so on, until the number changes:

Example:
RowID Number VOL
1 01-02-003-04-W5 1.0
2 01-02-003-04-W5 2.0
3 02-03-004-05-W2 1.0

I would manually enter the volume numbers, but the table is over 128,000 lines.
Some sample data is available here, the table Im dealing wi th is called 'Wells'
http://www.aiimcalgary.org/test.mdb

 

[jal]

I don't see what the problem is, I thought that's what my query was doing. What results are you getting when you use my method? And can you paste your translation of my code so I can verify it? Maybe even upload an MDB file (I use Access 2003).

 

[Brianwarnock]

Jal
it works perfectly with the comparison tweek in my test.

Brian

 

[CHOMPGATOR]

Ok, my bad...Lets see so by going too Tools > Macro > Visual Basic Editor > Then "Insert" > "Module"

And entering:

Public Sub DoSQL()
 
Dim SQL As String
 
SQL = "SELECT T1.*, (SELECT Count(t2.VOL) FROM WELLS as t2 WHERE t1.VOL = t2.VOL AND t1.RecordID <= t2.RecordID) as TheOrdinal"
FROM WELLS as T1;"
 
End With

Im getting an error that highlights the word 'As' and says 'Expected End of Statement'

Now, I could have this wrong...but in your example t1, is an SQL compare statement right? And, my code above might not be correct, so I assume that error is outlying where Im going wrong with this!

Again thanks for the help
Some sample data is located here
http://www.aiimcalgary.org/test.mdb

the table Im dealing with is called WELLS and the column with the numbers is called 'UWI' and the column that is supposed to have the volume numbers is beside thE UWI column and is called 'VOL'

 

[Brianwarnock]

Well now I just ran it as a simple! query, why are you using VBA?

Brian

 

[CHOMPGATOR]

Well now I just ran it as a simple! query, why are you using VBA?

Brian

hahah, well aren't I a dumb-ass.

Sorry about that - was running it as VBA code, not in SQL view...

Now I just tried to enter this code in SQL view and it wants me to enter a parameter for t2....What do you suggest putting for that?

Here is my revised code


SELECT T1.*, (SELECT Count(t2.VOL) FROM WELLS as t2 WHERE t1.VOL = t2.VOL AND t1.RecordID <= t2.RecordID) as TheOrdinal
FROM WELLS as T1


Only real problem Im having is the query wont execute because I assume the db is too large perhaps? Its just freezing Access when I run the query

 

[jal]

Well, you've got a point there - this type of query is very slow. Doesn't scale well. Hmmm...

 

[jal]

Maybe this is one of those rare cases where a recordset would fare better. In VBA you could create a loop that works as follows. Pull in the whole table using an ORDER BY clause to make sure that all same-volume tags are consecutive. Then loop through the recordset, numbering the records. Each time the tag changes, restart the numbering back to one. And so on. Maybe later tonight I'll write you some sample code.

 

[CHOMPGATOR]

Hey,

If you could do that, that would be fantastic.
(I dont know if this will help, but incase it does) the column with the numbers is called UWI, the column with the Volume Numbers is labelled VOL. And the ID column is called RecordID

Let me know if you need any additional info.


- Regards,

 

[jal]

By the way, did you put an index on the volume column? That might help this query. On the other hand, an index would slow down future INSERT operations.

 

[jal]

I don't know how to max performance with a rs, but the general logic is:

Dim rs As DAo.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Wells ORDER BY Vol")
Dim index As Long, previousVol As String
index = 0
previousVol = rs("vol")
With rs
Do While Not .EOF
.Edit
If rs("Vol") = previousVol Then index = index + 1 Else index = 1
rs("UWI") = index
previousVol = rs("Vol")
.Update
.MoveNext
Loop
End With

 

[jal]

I was just thinking therre might be a way to rewrite the original query. I'll give this some thought....

 

[CHOMPGATOR]

Maybe this is one of those rare cases where a recordset would fare better. In VBA you could create a loop that works as follows. Pull in the whole table using an ORDER BY clause to make sure that all same-volume tags are consecutive. Then loop through the recordset, numbering the records. Each time the tag changes, restart the numbering back to one. And so on. Maybe later tonight I'll write you some sample code.

Have any luck with the VBA? I tried altering my Excel VBA code that does this already and I couldn't establish something that worked.

 

[jal]

Have any luck with the VBA? I tried altering my Excel VBA code that does this already and I couldn't establish something that worked.

That VBA code I provided a couple of posts back was after a quick test on 5 or 6 sample records which seemed to work okay, although I should have added

rs.Close
set rs = Nothing


It didn't work for you?