[AccessD] A2K: Need a Smarter Append Query

Something like:

INSERT INTO tblWarnings ( BookingID, BookingDate, BookingTime, Cancelled )
SELECT Bookings.BookingID, Bookings.BookingDate, Bookings.BookingTime,
Bookings.Cancelled
FROM Bookings

WHERE (((Bookings.BookingDate)=Date())) AND (Bookings.BookingID NOT
IN(Select tblWarnings.BookingID from tblWarnings));


IOW, use a sub query that selects all of the tblWarnings.BookingsIDs.
Place that in a IN() clause.
NOT it.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren DICK
Sent: Monday, May 12, 2003 10:24 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] A2K: Need a Smarter Append Query


Hello all
I have an append query that gets a few columns from one table(Bookings) and
puts those
few columns into another table (tblWarnings) Based on today's date. Very
basic, see SQL below.

What I really want is for the SQL to only get bookings from the table
Bookings and put them into
tblWarnings if they are not already in tblBookings.

So if tblWarnings already has bookings 1, 2 and 3 from table bookings.
I don't want the SQL to get them again if I run it. But if I add record 4 to
table Bookings then
run the append SQL, I do want it to get record 4 and ignore the other 3.
Make sense?

INSERT INTO tblWarnings ( BookingID, BookingDate, BookingTime, Cancelled )
SELECT Bookings.BookingID, Bookings.BookingDate, Bookings.BookingTime,
Bookings.Cancelled
FROM Bookings
WHERE (((Bookings.BookingDate)=Date()));

Many thanks in advance

Darren
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com