Comparing two tables to see if there is any match

[aman]

Hi All

I have two access tables named as "DestructionTBL" and "AnnutiesTBL". Now I have to check each PolicyNumber of AnnutiesTBL with PolicyNumbers in "DestructionTBL". If match is found then add that PolicyNumber into "NODestructionTBL" and delete that PolicyNumber record from "DestructionTBL".

We have to keep on doing untill we searched all the policy numbers of AnnutiesTBL.

I hope anyone can help me in this.

Regards,
Aman

 

[vbaInet]

Aman what have you done? Doing the match against both tables is fairly easy, do some research.

 

[aman]

Can you please help me in this? As when the match is found then we have to delete that policy number form DestructionMasterTBL and also we need to add that Policy Number in a table named NoDestructionTBL.

Thanks

 

[vbaInet]

Google is your friend aman. First find a way to match the records using the Policy Number.

 

[aman]

Do we need to write VBA code for this?

 

[vbaInet]

No VBA, just queries. You need a query to find matching records. Then you need two more queries to insert and delete the records from the appropriate tables based on the "matching records" query.

 

[aman]

Thanks. I would really appreciate if you can guide me step by step . Its very urgent to get done.

Thanks for your help so far.

 

[jdraw]

I agree with vbaInet that Google can be you best tool for researching and getting examples.
I would also suggest you consider a logical delete (what we used to call shadow delete) instead of a physical delete. This may be a big issue in your business where you are dealing with Policies.
With physical delete, you delete a record and all history - is GONE.
With logcal delete, you have a field in your record to indicate IsDeletedYN. This really means if this field is valued No (the default), then the record is active/live/good. If the field is valued Yes, set when the record is "marked as Deleted", then the record is "logically" deleted. All history is available. The down side of this approach is that all queries and references to the record has to be aware of the existence, use and value of the IsDeletedYN field.

Good luck with your research.

 

[aman]

Guys I am writing the following query but it doesn't display any record:

SELECT AnnutiesTBL.PolicyNumber
FROM AnnutiesTBL INNER JOIN DestructionMasterTBL ON AnnutiesTBL.PolicyNumber=DestructionMasterTBL.PolicyNumber
WHERE (((DestructionMasterTBL.PolicyNumber) Is Null));

 

[aman]

And If I write the following query then it gives error "Query must have atleast 1 destination field"

SELECT 
FROM AnnutiesTBL INNER JOIN DestructionMasterTBL ON AnnutiesTBL.PolicyNumber = DestructionMasterTBL.PolicyNumber;

 

[vbaInet]

Aman, using INNER JOIN alone will return the matches. You don't need any criteria.

 

[jdraw]

As vbaInet advised

SELECT AnnutiesTBL.PolicyNumber
FROM AnnutiesTBL INNER JOIN DestructionMasterTBL 
ON AnnutiesTBL.PolicyNumber = DestructionMasterTBL.PolicyNumber;

 

[aman]

Thanks Guys. There is one issue here. I have to compare each policynumber from DestructionMasterTBL with all the policy numbers in AnnutiesTBL to see if the match occurs. And also there are some policy numbers in AnnutiesTBL that have extra 0's at the end or in the beginning so basically I want to use like operator 'like % Policynumber %' .

In the cancellation TBL, the policy numbers are in right format. But in AnnutiesTBL there are policy numbers with exceeding 0's.

I hope anyone of you can amend the query accordingly.

ANy help will be much appreciated.

Thanks

 

[aman]

The following query gives me the error message:

Select TBLCancellationModes.*, AnnutiesTBL.ID
From TBLCancellationModes
Inner join AnnutiesTBL
On TBLCancellationModes.PolicyNumber like concat("%",AnnutiesTBL.PolicyNumber,"%")

 

[aman]

The following query gives an error "Join expression not supported":

Select TBLCancellationModes.*, AnnutiesTBL.ID
From TBLCancellationModes
Inner join AnnutiesTBL
On TBLCancellationModes.PolicyNumber like "%AnnutiesTBL.PolicyNumber%"

 

[vbaInet]

like "%" & AnnutiesTBL.PolicyNumber & "%"

 

[jdraw]

When you do a join you match on some field(s) values in the related tables.
Your posts 15 and 16 are invalid syntax --just as the error says.
You said the policy numbers in the tables were not exactly the same format --hence the error.

What exactly is the format of PolicyNumber in Annuities table?
Field values have to match exactly.

 

[aman]

The policy numbers in both the tables have same data type (Text) but in AnnutiesTBL, the policy numbers sometimes appear like 08977660000000, 67800000

and in TBLcancellationModes, the policy numbers right as 0897766, 678 etc.. so I have to use Like operator to match the values in both tables.

 

[jdraw]

Why are the lengths different in the tables? Seems like a design issue and perhaps that should be priority..

....so I have to use Like operator to match the values in both tables.

OK, you know what is required- have you tried anything more since posts 15-16?

Post a copy of your database -remove anything confidential. We only need enough records in each table to illustrate the problem.

 

[aman]

Jdraw, I am not able to upload the access file because of security restrictions in the company so I have copied the data into notepad . Please see attached.

Many Thanks