Checking for existing record in multiple tables (1 Viewer)

[jeremie_ingram]

I have a system set up to track the progress on certain items. I import all of the data for all items from another system so as to eliminate unnecessary data entry. This import will occur once a year and will overwrite the existing data.
Only a small percentage from the imported records will actually be needed but all are brought in because there is no way of knowing what is or is not needed in advance. This data changes yearly and the imported data is set to overwrite the existing data each year.
What I have is a button that triggers a query that will transfer (append) the data from one table to a secondary table based on the user input.
What I would like to do is
1. Ensure the record exists in the imported dataset.
2. See if the record already exists in the secondary table.
3. Give the appropriate message to the user according to the previous 2 points, and if all is good simply append the record.

Here is an example

tbl_1
- PIN
- AYEAR
- FName
- LName

tbl_2
- PIN
- AYEAR
- FName
- LName

If the record does not exist in table 1, then msgbox: The record does not exist.

If the record does exist in table 2 (by PIN and AYEAR), then msgbox: The record already exists.

To add a twist....
Since all records is tbl_1 will have the same AYEAR, I would like to use that within the criteria for checking in tbl_2. Since tbl_2 will keep the records it is possible to have one record (PIN) appear multiple times but with different years.

I hope this makes sense. I have looked at MANY posts covering DCOUNT but cannot see how to append it within my code to get it to check the separate tables and apply the appropriate actions. Am I barking up the wrong tree?

 

[battenberg]

was just reading this thread, it maybe of use to you:

http://www.access-programmers.co.uk/forums/showthread.php?t=118406

 

[jeremie_ingram]

Thanks

I appreciate the response, but that was a little off base. I am not worried about duplicating them in that fashion, what I wanted to do was alert them to whether or not the record exists in tbl_1 and whether or not they already have it in tbl_2. I know that I can set the duplicates to no and have a concatenated key for pin and ayear, but I was hoping to utilize something like DCOUNT to help me keep data integrity.

 

[KeithG]

I would create a union query to create one recordset from the two tables then use the DCOUNT on the union query.

 

[jeremie_ingram]

Much appreciated

I have attempted this but I need to rethink the entire setup. It seems that the more I think on it the more compicated I make it and the worse my head hurts.