True/False NULL fields

[chillay]

Hello

CREATE TABLE Speed
(
ID# CHAR (50) Primary Key NOT NULL,
Slow BIT,
Fast BIT,
Faster BIT,
Fastest BIT,
);
How do I create a query where only one of the last four fields can be true the other ones have to be NULL

Thank you,

Chillay

 

[r937]

Yes/No columns can't be null

at least, i couldn't do it, i just tested it

every time i tried to insert a null, it got changed to zero (i.e. No)

FYI your CREATE TABLE statement needed a little tweaking before access would accpet it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[dhookom]

I don't know if those are your actual field names but your structure is not generally considered good if you want only one value to be true. I would create one field for all the yes/no fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LittleSmudge]

I'm with dhookom on this

And i'd add DO NOT USE # or any other 'fancy' characters in field names.
# is a Date value delimiter in Access any people might think you're flagging this as a Date field.
Other people use # as some other data type that is not universally defined - so avoid it to save confusion.

Also see FAQ faq700-2190 about space characters.


This job is exactly what Option Group controls are for, and the principle of the Enum data type ( even though I'd just use a simple Byte in this case. )

CREATE TABLE Speed
(
SpeedId Text(50) Primary Key NOT NULL,
SpeedVal Number(Byte),
);


Where SpeedVal Enums to 1 = Slow, 2 = Fast, 3 = Faster, 4 = Fastest

Your Create Table layout is very MySQL speak format.
Are you sure you're in the right Forum ?



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BNPMike]

I don't really see the point of using Bit fields nowadays. Access runs on desktops which are extremely powerful so the potential saving in processing is utterly insignificant.

Logically,as Smudge says, 1-2-3-4 is altogether better.

 

[chillay]

Thanks everyone!

Chillay