Dear Anand:
I use a two step process to develope something like this, which I
think illustrates what is happening.
First, write a a query that shows the rows from tbltemp that will not
be appended due to duplicating the EmployeeID/AddDate key:
SELECT T1.*
FROM tbltemp T1
LEFT JOIN tblperm T2
ON T2.EmployeeID = T1.EmployeeID
AND T2.AttDate = T1.AttDate
WHERE T2.EmployeeID IS NOT NULL
While it is normal to write the above with an INNER JOIN, which will
give the same results, the above is easily modified to also show those
rows that WILL be added to tblperm.
The above is usefull, as it is often necessary to report those rows
that are not added and show why this has happened.
Then, you can change the above query very slightly to show which rows
will be added:
SELECT T1.*
FROM tbltemp T1
LEFT JOIN tblperm T2
ON T2.EmployeeID = T1.EmployeeID
AND T2.AttDate = T1.AttDate
WHERE T2.EmployeeID IS NULL
Just drop the "NOT" from the last line.
You can then easily transform this into an APPEND query.
There are other conditions that could also cause rows from tbltemp to
not append. These include:
- any row in tbltemp that duplicates EmployeeID/AttDate with another
row in tbltemp (attempting to add the "same" row twice)
- any row in tbltemp that would violate relationships enforced in
tblperm.
- any row in tbltemp with a NULL value in a column that is Required in
tblperm.
The above can be prevented using similar constraints for tbltemp when
that is appropriate. Otherwise, you can use similar query work to
detect them, report them, and eliminate them from the append.
Hi,
I am using an A2000 app. I need to append multiple
records from one table (tbltemp) to another (tblperm)
after checking that each record in tbltemp does not exist
in tblperm.
I need to check two fields in tblperm - EmployeeID and
AttDate and append a record only if it does not already
exist in tblperm. I need to achieve this in code.
What is the best way to do this? Please let me know.
thanks,
Anand
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
