Need ODD or EVEN ID

[penguinspeaks]

Ok. I use access DC on a windows server. I use frontpage as well. My DB has columns named 'ID' 'username' 'username2' 'score' 'score2' .
What I want to be able to do, is display the EVEN numbered rows by ID, and also to be able to display the ODD numbered rows by ID in another area of the web. Can this be done ???
Thanks
Jeff

 

[swampBoogie]

What do you mean by odd and even numbered rows? Rows does not have a number (nor position).

 

[penguinspeaks]

Yes. The ID column will be consecutive, therefore having odd and even numbers, based on the entries.

 

[penguinspeaks]

Ok. Here is what is should do. I want it to display the 1,3,5,7,9... ext rows of the table, based on the ID number. Since this number WILL NOT repeat itself, it won't display more than that.

 

[rac2]

SELECT * FROM myTable
WHERE ID%2 = 0

will give you all of the rows with an even ID

SELECT * FROM myTable
WHERE ID%2 = 1

will give you all of the rows with an odd ID.

The symbol that we usually refer to as a percent sign is the modulo operator in Microsoft Transact SQL. The result of the modulo operator is the remainder of the first number divided by the second number; remainder is 1 for odd numbers and 0 for even numbers.

In Access the symbol for the modulo operator is Mod so

SELECT * FROM myTable
WHERE ID Mod 2 = 1

In general, if oddness were actually a property of the things described in your table you might want to make a column for that, perhaps oddness with values yes and no.

If you merely wish to divide up the rows into equal groups, then using the ID will work approximately, especially if you never delete rows from the table.


For future consideration, this is not really an ANSI SQL question. Your post might have been better located in the Access Queries forum.

 

[penguinspeaks]

I guess I didn't say what I meant. I don't want it to display based on the ID number, what I need is to show every other row period. The database is set up by ID, but i need it to sort the scores first, then display them by a row count, not the ID. Does this make sense?
Jeff

 

[r937]

jeff, since you are using frontpage, you will have control over the returned rows that you display on the web page, and i would strongly urge you just to return the result set in the order you want, and let frontpage -- or asp, if that's what frontpage uses (i dunno, i use coldfusion) -- just skip over the alternate rows

sorting in sql and then returning every other row will bust your nuts, don't try it, it's exceedingly inefficient

of course, i would love to be shown wrong on that last statement...

rudy

http://r937.com/