Required field only if another field is empty

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I am setting up a database of inventory for our computers. We have a table
called tblcomputers and a table called tblusers. Most of our computers are
matched to a login, e.g. the computer name GSMITH would be matched to a login
named GSMITH. There may also be a second computer called GSMITH2 which would
be matched to the GSMITH logon. So I have a one to many relationship of
computers to logins.

We also have a field called FLOOR and one called ROOM. On tblComputers, we
only want to enter information into these fields if the computer name is not
matched to a user id. For instance we have some scannner machines and guest
machines. How would I say that the FLOOR and ROOM fields are required only
if the computer ID isn't matched to a user? Thanks for all of your help.
 
Joanne

It sounds as if you might be trying to do this directly in the table.
Access tables are great for storing data, but Access forms are design for
data-entry/edit, and have a very rich event environment.

Use forms and you'll be able to, say, use the form's BeforeUpdate event to
check on whether values have been added to fields. This is where you could
do the validation you described.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hello,
I am setting up a database of inventory for our computers. We have a table
called tblcomputers and a table called tblusers. Most of our computers are
matched to a login, e.g. the computer name GSMITH would be matched to a login
named GSMITH. There may also be a second computer called GSMITH2 which would
be matched to the GSMITH logon. So I have a one to many relationship of
computers to logins.

We also have a field called FLOOR and one called ROOM. On tblComputers, we
only want to enter information into these fields if the computer name is not
matched to a user id. For instance we have some scannner machines and guest
machines. How would I say that the FLOOR and ROOM fields are required only
if the computer ID isn't matched to a user?
Click to expand...

I assume you have something similar to this (all SQL tested using
ANSI-92 Query mode):

CREATE TABLE ComputerRooms (
floor_number INTEGER NOT NULL,
CHECK (floor_number BETWEEN 1 AND 6),
room_number INTEGER NOT NULL,
CHECK (room_number BETWEEN 1 AND 20),
UNIQUE (floor_number, room_number)
)
;
CREATE TABLE tblusers (
userID VARCHAR(8) NOT NULL UNIQUE
)
;
CREATE TABLE tblcomputers (
computer_name VARCHAR(8) NOT NULL UNIQUE
)
;
CREATE TABLE tbllogins (
userID VARCHAR(8)
REFERENCES tblusers (userID),
computer_name VARCHAR(8) NOT NULL UNIQUE
REFERENCES tblcomputers (computer_name),
UNIQUE (userID, computer_name)
)
;

You *could* use a table-level CHECK constraint to test for the
existence of rows in the logins table...

CREATE TABLE tblnonusercomputerlocations (
computer_name VARCHAR(8) NOT NULL UNIQUE
REFERENCES tblcomputers (computer_name),
CONSTRAINT cannot_be_a_user_computer
CHECK (NOT EXISTS (
SELECT * FROM
tbllogins
WHERE tblnonusercomputerlocations.computer_name =
tbllogins.computer_name)
),
floor_number INTEGER NOT NULL,
room_number INTEGER NOT NULL,
FOREIGN KEY (floor_number, room_number)
REFERENCES ComputerRooms (floor_number, room_number)
);

....however, I'm coming around to the idea that the CHECK constraints
should avoid referencing rows in other tables. This is borne out by
both the SQL-92 standard, for which CREATE ASSERTION syntax is
provided, and the Jet implementation, which checks constraints on a
table-by-table basis rather than on a single SQL statement basis (see
http://groups.google.com/group/microsoft.public.access/msg/
e4cd2c5badd26bc8).

An alternative design, one that doesn't involve CHECK constraints, is
a subclassing approach e.g.

CREATE TABLE Computers (
computer_name VARCHAR(8) NOT NULL UNIQUE,
location_type CHAR(4) DEFAULT 'Room' NOT NULL,
CHECK (location_type IN ('Room', 'User')),
UNIQUE (computer_name, location_type)
)
;
CREATE TABLE Logins (
userID VARCHAR(8)
REFERENCES tblusers (userID),
computer_name VARCHAR(8) NOT NULL UNIQUE,
UNIQUE (userID, computer_name),
location_type CHAR(4) NOT NULL,
CHECK (location_type = 'User'),
FOREIGN KEY (computer_name, location_type)
REFERENCES Computers (computer_name, location_type)
)
;
CREATE TABLE NonUserComputerLocations (
computer_name VARCHAR(8) NOT NULL UNIQUE,
location_type CHAR(4) NOT NULL,
CHECK (location_type = 'Room'),
FOREIGN KEY (computer_name, location_type)
REFERENCES Computers (computer_name, location_type),
floor_number INTEGER NOT NULL,
room_number INTEGER NOT NULL,
FOREIGN KEY (floor_number, room_number)
REFERENCES ComputerRooms (floor_number, room_number)
)
;

With the above design, a computer can appear once in Logins or once in
NonUserComputerLocations (name could be improved!) but not to both
simultaneously. OK, so I did use CHECK constraints but the ones in the
alternative design can be replaced by column-level (record-level)
Validation Rules and even as CHECK constraints will not suffer the
possible timing issues that the multi-table CHECK could.

Jamie.

--
 
It sounds as if you might be trying to do this directly in the table.
Access tables are great for storing data, but Access forms are design for
data-entry/edit, and have a very rich event environment.
Click to expand...

IMO data constraints should be as close to the data as possible and
'directly in the table' is perfect e.g. DRI ('Relationships'),
Validation Rules, table-level CHECK constraints, etc. This way, once
the table constraints are tested as working they can be used to test
for bugs in the forms' code.

Jamie.

--
 
Back
Top