I have the following Consumers Table
Name		address		      id		fam
 Bob		A			1		Null
 Joe		B			3		Null
 Mark		C			5		Null
Mary		A			2		1
Vickie		B			4		3
Wayne		D			6		null
How would I delete those rows where fam is null but there are other
family members on the mailing list in SQL?

Also in the Hotel table
RM#(PK)	Arrival Date(PK)	Departure Date	Guest Name
101	10/05/02		10/09/02	Bill
202	10/06/02		10/08/02	Jim
303	10/08/02		10/10/02	Jiff
How do you enforce the rule (departure >= arrival) that you cannot add
a reservation that has given an arrival date conflicting with the
prior departure date for a given room?
The Check() clause enforces the data integrity constraint that you
cannot leave before  you arrived, but you want more.

---

Clarification of Question by fahd-ga on 22 Oct 2002 09:06 PDT

Please note that Columns and Rows are not lined-up in the two tables.

---

Request for Question Clarification by hammer-ga on 29 Oct 2002 12:22 PST

For your first question:
How does one tell if there are other family memebers on the list? Same
Address? Same id?

For your second question:
Are you doing data entry through a form? An SQL Insert query? In other
words, at what level is the enforcement required?

In General:
What version of Access are you using?

---

Clarification of Question by fahd-ga on 30 Oct 2002 10:19 PST

I am using MS Access '97 version
I have created all needed tables but I am having trouble with the way
I should instruct the SQL (program) with the right formula to:
Delete Null for the first Question, and to use:
Departure Date >= Arrival Date

Please Help
Truly,
f-ga

For your first question: 
If you simply want to delete those records where fam is null Then use
this:

DELETE * FROM tblConsumers WHERE (tblConsumers.fam Is Null);

If you want to delete only those records where fam is null, but there
is another record in the table with the same address where fam is not
null, then do this:

DELETE * FROM tblConsumers WHERE (tblConsumers.fam Is Null) AND
(tblConsumers.address In (SELECT address FROM tblConsumers As Tmp
WHERE Tmp.fam Is Not Null));

For your second question:
You mention using a Check constraint. The Jet database engine (which
Access 97 uses) does not support constraints. CHECK is used with T-SQL
in Microsfot SQL Server, but you don't mention an SQL Server back end
in your question.

Depending on what you are doing, you have several choices for data
validation.

You can set a Validation Rule in the Properties of a field when in
Design View for a table that will automatically present the user with
a message if the data does not meet the criteria. This option is
closest to a constraint. Open your table in Design View. Click on the
Departure Date field. Enter the expression you want to validate in the
Validation Rule box on the Field Property sheet.
 
You can set a Validation Rule in the Properties of a control placed on
a form that will automatically present the user with a message if the
data does not meet the criteria.

You can also write code into the BeforeInsert event of the Form
itself. This can be used to verify data and cancel the Insert if
verification fails. The BeforeUpdate event of the control is also
available. For example:

Private Sub Form_BeforeInsert(Cancel As Integer)

    If DepartureDate < ArrivalDate Then
        Cancel = True
        MsgBox "Departure Date cannot be earlier than Arrival Date!"
    End If

End Sub

There are other/additional events which can be used for this purpose.
If this does not answer your question, please request answer
clarification with more detail regarding what you are doing at the
point when the validation needs to occur.

Good luck with your database design!