Count-If

[jal]

I am still working on a way to rewrite the original query. I'm not sure I can jump the last hurdle.

 

[CHOMPGATOR]

I am still working on a way to rewrite the original query. I'm not sure I can jump the last hurdle.

Hey there, y eah Ive been playing around with it too, can't seem to get anything..

I just attempted your code, and I got this error:
'Run-Time error 94' Invalid Use of Null

Any luck on your end yet

 

[jal]

Hey there, y eah Ive been playing around with it too, can't seem to get anything..

I just attempted your code, and I got this error:
'Run-Time error 94' Invalid Use of Null

That code should work.
What line is throwing the error?

 

[jal]

I think I got a new query that will do the trick - needs testing. It did a 150, 000 rows in less than 30 seconds (of course I have a very fast machine). Give me a few minutes to upload it.

 

[jal]

Ok, here's a solution, but it's probably not faster than the recordset version, since I had to create two temporary tables.

I had to use two temp tables because GROUP BY queries are not updateable. Works like this. Let's say you have these volumes:

Vol.
Mike
Mike
Mike

Start with a Count of Mike's (which in this case is 3), and records this figure in a column like this:

Vol...Count
Mike...3
Mike...3
Mike...3

by means of this query ("qryCountOfEachVol")

INSERT INTO CountOfEachVol
SELECT W.Vol, W.RecordID, CountForThisVol
FROM
(
SELECT Count(Vol) as CountForThisVol, Vol
FROM WELLS
GROUP BY Vol
) as CountOfEachVol
INNER JOIN Wells as W ON W.Vol = CountOfEachVol.Vol
ORDER BY W.Vol

The temp table above (CountOfEachVol) has an autonumber column called Sequence which is used in the next query (my assumption is that the engine will generate perfectly consecutive autonumbers although the starting point might not be the number one). The way it works is that let's suppose the three Mike's get autonumbered like this:

Vol...Sequence
Mike...101
Mike...102
Mike...103

The query will capture the mininum of these and store it like this (and note we can now capture the difference)

Vol...Sequence....Min
Mike...101........101
Mike...102........101
Mike...103........101

which is this query ("qryMinForEachVol")

INSERT INTO MinForEachVol
SELECT Min(Sequence) as MinForThisVol, VOl
FROM CountOfEAchVol as C
GROUP BY C.Vol

The purpose of storing the minimum is that we can now do the following calculation and then update the Well table (we subtract the Min value from the Sequence value - and then add 1.

Vol...Sequence....Min....Diff....Dif plus 1 is the UWI
Mike...101........101....0........1
Mike...102........101....1........2
Mike...103........101....2........3

which is this query ("qryUpdateUWI")

UPDATE ((Wells as W
INNER JOIN CountOfEachVol as C ON C.RecordID = W.RecordID)
INNER JOIN MinForEachVol as M on M.Vol = C.Vol)
SET UWI = Sequence - MinForThisVol + 1

Seems to work, but needs testing!

To test this, use the qryEnlargeSampleData (run it repeatedly) to keep doubling the fake Wells table in size until it reaches your desired test-size (128,000 records). Then click the button on Form1 to run it. Then click qryResults in the Object Pane to see the turnout.

 

[jal]

I tested for myself. The recordset was about twice as fast as using the temp tables. Took only about 10 seconds on my machine.

 

[CHOMPGATOR]

Hey,

Ok Im going to attempt this here in a bit...

I have one other question - instead of showing the volume numbers as 1.0, 2.0, 3.0 etc..

I need to show them as 001, 002, 003 - Ive tried just typing 001 into the database, but Access removes the zero's...(I need them to show)
So I tried opening the table in design view, and selecting the volume column and assigning the Allow Zero Length...but that is only available for columns that have the 'Data Type' of text, not the 'Data Type' of Number

Any ideas on how I can get these zeros to show?

 

[jal]

Hey,

Ok Im going to attempt this here in a bit...

I have one other question - instead of showing the volume numbers as 1.0, 2.0, 3.0 etc..

I need to show them as 001, 002, 003 - Ive tried just typing 001 into the database, but Access removes the zero's...(I need them to show)
So I tried opening the table in design view, and selecting the volume column and assigning the Allow Zero Length...but that is only available for columns that have the 'Data Type' of text, not the 'Data Type' of Number

Any ideas on how I can get these zeros to show?

When you say "volume" numbers do you mean the ordinal numbers created in my loop?

 

[jal]

BTW, don't know if I mentioned, I had to do this:

DAO.DBEngine.SetOption dbmaxlocksperfile,50000

The default is around 10,000. I had to increase it to 50,000 because of an error thrown.

 

[CHOMPGATOR]

Hey,

Nope sorry about that, I should have clarified it more...

I meant in the 'VOL' column in the sample data I provided a link for...

If you tried typing 001 instead of 1.0
Access removes the zeros. Is there a way to keep the Zero's shown?

I went into the 'Design' View for the table and was going to assign the Zero-Length Value, but since the 'VOL' field is strickly meant for 'Numbers' only - I can't change the field...

Im not sure if assigning the zero-length value is how you would get the Zero's to still be shown, but I thought it might be an easy method (but of course it didn't work.

Anyway - the reason I have to do this, is because Im told the VOL field has to show volume numbers like this:
001 instead of 1.0
002 instead of 2.0
003 instead of 3.0
004 instead of 4.0

and so on.

Talk to you later!

Ill post here once Ive altered the code you provided and tested it on my end!

Thanks,

 

[jal]

I thought your "VOL" column was a TEXT datatype in which case Access shouldn't be changing anything. I'll have to look at your sample data, I'm too tired right now though.

 

[CHOMPGATOR]

Sounds good, get some rest, and Ill chat with ya later.


- Regards,

 

[jal]

Hey,

Nope sorry about that, I should have clarified it more...

I meant in the 'VOL' column in the sample data I provided a link for...

If you tried typing 001 instead of 1.0
Access removes the zeros. Is there a way to keep the Zero's shown?

I went into the 'Design' View for the table and was going to assign the Zero-Length Value, but since the 'VOL' field is strickly meant for 'Numbers' only - I can't change the field...

Im not sure if assigning the zero-length value is how you would get the Zero's to still be shown, but I thought it might be an easy method (but of course it didn't work.

Anyway - the reason I have to do this, is because Im told the VOL field has to show volume numbers like this:
001 instead of 1.0
002 instead of 2.0
003 instead of 3.0
004 instead of 4.0

and so on.

Talk to you later!

Ill post here once Ive altered the code you provided and tested it on my end!

Thanks,

I downloaded the sample MDB file of yours. Vol is typed as MEMO which is basically TEXT. Therefore it worked fine for me to type "001" into the field.

Apparently you somehow changed it from MEMO to NUMBER in your current version? If so, why?

 

[CHOMPGATOR]

Hey Jal,

Sorry, I posted a more up-dated version here:

http://www.aiimcalgary.org/Test Data.mdb

My bad..Let me know your thoughts

 

[jal]

Please do a compact (Tools > Database Utilities > Compact). Your tying up my computer downloading 200 MB files that should only be 5MB in size.

 

[jal]

And by the way, you didn't answer the question. Did you change that field to numeric and, if so, why? I can't look it at yet because it maybe an hour before this huge file finishes downloading.

 

[CHOMPGATOR]

Hey Jal,

Ill fix up the size.

I did change the field back to 'Number' (Mainly because I thought you wanted to play with it when it had 'Number' as the data type value.)

Let me know your thoughts...And thanks for the tip with compacting the db size, Ill get that done right now!

Thanks again!

 

[jal]

I just dont' see how "Number" is going to work if you intend to store string-type data in there such as hyphens, extra zeroes in front, etc. Perhaps there is a way to do this but it would be a headache, so why bother?

You asked why it was changing 001 to 1.0. Answer: It's due to "Number". Change it back to TEXT.

 

[jal]

And I was hoping it would compact down to less than 65 MB but I guess it won't. My mistake.