Hi all, sorry for multiposting.
I need to create a recordset based on a table to use it to insert a new
record in a different table with a different scope and make sure the user
never creates copies of a certain record.
Table 1 contains data related to invoices and has a numeric field that
stores invoice numbers which are unique.
The user selects a specific invoice number and adds a new record in Table 2.
When the user inserts a new record into Table 2, I want him/her to only see
invoice numbers that have NOT already been inserted into Table 2.
At the moment, before opening the form thru which this operation is
accomplished, I do a comparison of each record in Table 2 with the ones in
Table 1 and remove, from the recordset based on Table 1, all records that
already appear in table 2. On a PIII 650 this is preatty fast with only a
few hundreds invoices stored. But as the database grows, I'm afraid this can
turn into a rather time consuming task.
So, my question is:
Is there an SQL statement (or any better way to do this) that can create a
recordset based on Table 1 but that does NOT include all records that were
already added to Table 2, based an a unique number field?
Obviosly this would be a lot easier if there was a flag field in Table 1 to
just store a boolean value showing that a particular record has already been
used, but unfortunately it's too late now and the database cannot be
modified.
I thank you for reading this, I hope it's clear.
--
Andy,
Complex SQL statement