Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2003, 05:33 PM
Registered User
Join Date: Aug 2003
Location: Leeds, , United Kingdom.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default complex SQL using JET-SQL

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


Reply With Quote
  #2 (permalink)  
Old October 2nd, 2003, 09:28 AM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

What are you trying to acomplish with this exactly.

Reply With Quote
  #3 (permalink)  
Old October 2nd, 2003, 09:51 AM
Friend of Wrox
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts

Create the following VB macro function in Access:

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:

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.

Owain Williams
Reply With Quote
  #4 (permalink)  
Old October 2nd, 2003, 10:01 AM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL-Union works in Access but not in Jet tonatwinemaker Access ASP 5 February 20th, 2006 04:37 PM
jet sql query help connect2sandep Access 4 October 28th, 2005 02:16 PM
Converting a Jet query to SQL that uses a function Mitch SQL Server 2000 5 February 28th, 2005 12:33 PM
HELP!!! Jet SQL: Error (80040e14) Coldendus Access VBA 3 December 14th, 2004 08:12 PM
Migrating from Jet to SQL Ray Pinnegar Access 2 October 8th, 2004 04:43 AM

All times are GMT -4. The time now is 06:04 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.