ok this is the scenario....
I have a table containing roughly 2,500,000 records of mixed record types. e.g.
440, 123456, peoples avenue, ab12 2lx
441, abc, 19970801
451, _A, metering point, available
460, EELLC, 19970901
440, 123457, an address road, bc13 4vx
441, abd, 19980802
451, _A, metering point, occupied
460, EELLC, 19980901
440 ....... [and so on ...]
basically I have imported this file into ACCESS 2000 and need to seperate each record type (i.e. 451, 441 etc...) to their corresponding 440 record
The way I thought to doing this is to add an additional field called 'sortKey'. Whereby for every 440 record sortKey = sortKey + 1
whereby the output would look like this:
440, 123456, peoples avenue, ab12 2lx, 1 (sortKey)
441, abc, 19970801, 1
451, _A, metering point, available, 1
460, EELLC, 19970901, 1
440, 123457, an address road, bc13 4vx, 2 (sortKey + 1)
441, abd, 19980802, 2
451, _A, metering point, occupied, 2
460, EELLC, 19980901, 2
Now, I have managed to achieve this writing a Vb
module within ACCESS however ACCESS will only run my Vb
code providing the total number of records is no more than 170,000 otherwise I get the error 'Invalid Argument'.
what I have noticed is that Access appears to function with no problems with SQL... therefore.. I'm trying to write an SQL that will populate the sortKey field.
the kind of SQL I was looking at was along the lines of using a CASE WHEN statement however I am having some serious syntax probs coz its JET - SQL within ACCESS.
SELECT a.Field1, a.sortKey(CASE a.Field1 WHEN 440 THEN count+1 ELSE count END)
FROM data204 AS a;
can anyone help??