Constraints

[Guest]

I am trying to design a table so that the team shown in the team 1 column
will never equal the team shown in the team 2 column

 

[Adrian Jansen]

I am trying to design a table so that the team shown in the team 1 column
will never equal the team shown in the team 2 column

Presumably so that you never have the situation where a team is picked
to play itself ?

Consider redesigning so that you only have one field "teams", and make
entries in it unique. Then use whatever means you need to pick two
entries from the one field. Then you can never have the situation you
are trying to avoid.

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.

 

[Jeff Boyce]

If you have one table that lists teams, and a second table that holds a
TeamID in a HomeTeam field and another TeamID in an AwayTeam field, you have
a reasonably normalized design.

If you are storing data in Access/Jet, you don't have "constraints" (if you
are using SQL Server as a back-end, you do).

If you use forms for data entry, and keep the user(s) away from data entry
directly into tables, you can use a pair of combo boxes for the two teams.
Your row source for the second combo box can exclude whichever team's been
picked in the first.

 

[Tim Ferguson]

If you are storing data in Access/Jet, you don't have "constraints"
(if you are using SQL Server as a back-end, you do).

Why can't he use a Table-level ValidationRule like

(HomeTeam Is Null) OR (AwayTeam Is Null) OR (HomeTeam <> AwayTeam)

or am I missing something really obvious (which I never doubt...)

B Wishes


Tim F

 

[Jeff Boyce]

Thanks, Tim!

Forest and trees!

Jeff