complex SQL using JET-SQL

andy_routledge · August 5th, 2003, 05:33 PM

hi all,

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??
thanks

Andy

sal · October 2nd, 2003, 09:28 AM

What are you trying to acomplish with this exactly.

Sal

owain · October 2nd, 2003, 09:51 AM

Create the following VB macro function in Access:

Code:

Public Function YourSortKeyFunction(ByVal intField As Integer) As Long

Static lngSortKey As Long

    If intField = 440 Then lngSortKey = lngSortKey + 1
    YourSortKeyFunction = lngSortKey

End Function

Then use the function in the following SQL statement:

Code:

SELECT a.Field1, YourSortkeyFunction(a.Field1) AS SortKey
FROM data204 AS a;

I think that this should be able to cope with 2,500,000 rows, but I am not sure. It's worth a go though.

Regards
Owain Williams

sal · October 2nd, 2003, 10:01 AM

Are you trying to do this just to sort your records?
What is the requirement for this. You may be going about it the hard way.
If all you need is to use it to sort your records, you may be able to use an autonumber field in conjunction with your 440,441,442 field to sort permanently, easing the load on your database permanently.

Sal