listing records

[gcapp]

I need some help with the way my list of records displays.

I have a table that has over 1000 records and it consists of a directory of people. I have this code which will list records according to a number in front of the name:

memberRank = left(indlisting, 3)
if memberRank = "(1)" then
memberRank = "Dept Head"
else
memberRank = ""
end if

memberlisting = right(indlisting,(len(indlisting)-4))

However, since I have over 1000 names, I'd rather not enter a "(2)" in front of every single name that I want listed after the Department Heads.

Is there a code similar to this that will allow me to have certain records list first then the rest, if I just enter something int he records that I want listed first??

Thanks,
Gary

[oracleguy]

What about adding a ORDER BY clause to your SQL statement? So like whatever have now plus "ORDER BY MemberRank" Or whatever field that has the number in it.

[whammy]

That's what I was wondering. Do you have the records ranked somehow?

If so, just "order by" the records returned using that field, i.e.:

"SELECT * FROM membertable ORDER BY memberRank"

Please (please) tell me you're not actually inserting a number into the same field as the name - if so, you should thoroughly study some relational database tutorials before doing anything else. This is a good start:

http://hotwired.lycos.com/webmonkey/02/35/index4a.html?tw=backend

[gcapp]

Well actually, because there are over 1000 records, I was just hoping to add maybe a number or something in front of the Dept. Heads, which would only be like 40 records and then have the rest, list underneath the records with a "(1)" in front of them.

But I noticed the problem with that is when I use:

memberlisting = right(indlisting,(len(indlisting)-4))

it cuts off the first 4 letters of all the listings that don't have a number in front of them.

So that's why I wondered if there was a simple way to just add something to those 40 or so records, so they would list first.

I have an ORDER BY in my SQL, but it is by alphabetical order for those records that are not Dept. Heads. It's a rather long and complicated task I'm trying to do here.

Thanks

[whammy]

This definitely sounds like a job for a related table as I said above- just have some id for "department heads" in your table, and associate it with another table where it has the same ID.

Then you can use a JOIN on the tables to get the records you want, or order them, i.e.:

members
======
counter
type_id (int) <-- this is the one that would be the primary key in the second table
name
etc.

memberTypes
==========
type_id (primary key)
description
etc.

I'm not sure how your table structure is set up (that's just an example), but there is no reason that this should be long or complicated at all - I could do that in about 5 minutes. ;) Of course, I have been relational databases for a little while.

Although, you may not necessarily need another table, why not just add a field called "memberRank", and update those fields accordingly? Obviously if you only have two choices, you could use a bit field even, and set the department heads to "1" and the other people to "0".

I would definitely check out the link I posted, this is a "need to know" subject regardless, and by spending the time to go through that tutorial (and others if you want), it will save you LOTS of time in the long run! :)