Comparing tables help

[Allan1875]

Hi lads, first post, sorry if this is in the wrong place. Used access on a very basic level for years but needing to do a bit more with it now. I had a look through the forum and search but i may just be searching wrong.



I have 8 tables inside my database, all contain similar records with the exact same fields and each tables first field is "reference number", which is unique to each record. I need to take all 8 tables and compare the reference numbers and exclude any duplicates. Table 2, 4 and 6 may contain the same reference number, however i only need it to be in one of the 3, doesn't really matter which one. Then tables 2, 4 and 5 may also have the same, once again doesn't matter which table the reference stays in.
Can anyone give me any guidance or point me in the right direction to do some reading on the subject?

Thanks.

[orange]

Having multiple tables with exactly the same fields is a real red flaq. It sounds like a structure problem from the start. Tell us about these 8 tables--what do they represent? Be as precise as you can. Why is table 4 different than table 7 for example?
What is special about the reference numbers?

[Allan1875]

Having multiple tables with exactly the same fields is a real red flaq. It sounds like a structure problem from the start. Tell us about these 8 tables--what do they represent? Be as precise as you can. Why is table 4 different than table 7 for example?
What is special about the reference numbers?

I will try my best to explain as i can only go certain amount of info due to DPA.

It's 8 separate reports that have been run in a different program and results brought over into access tables. The reports identify customer that meet all different types of criteria to do with vulnerability. So say for example report 1 picks out customers who are blind, report 2 picks out customers who are deaf, report 3 picks out customers with a physical disability.

All these customers need to be on reports stating they need extra help.

Person A - is blind, person B - is deaf, person C is deaf, blind and disabled.

Person A will only appear in report 1, person B will only appear in report 2 and person 3 will appear on report A, B and C. I need to provide these lists to another department with no duplicates, therefore i only want person C to appear on either report A, B or C, not all 3. Each person has a unique reference number which I've already mentioned in my first post. All my tables are exactly the same except each table has extra Customers depending what criteria they meet, some Customers meet moer than one criteria, therefore they are in more than one table.

The Customer stuff I've all just made up but it's a good example of exactly what i need to do. Hope it makes sense.

[orange]

Where do the reports get their info? It would be better for you to get raw data from that source.

Data from a report may be aggregated, manipulated etc. and it's likely full of formatting characters.
Just sayin' if you have a chance get the raw data.