Remove Duplicates - multiple fields matching

[Trancemission]

I have inherited multiple access databases for use in the hotel industry.

My problem is the data is spread among 5 databases - The world is split into 5 parts [brochures]. I am looking to create a report bringing these tables together in one table.

The problem I have found [eventually nobody decided to tell me] is that some hotels could be in 2 databases. :!

I have used the Access 'find duplicate query' which is great and shows my duplicates. I define a duplicate by the following fields:

HtName
RoomType
Occupancy
StartDate
End Date

Should all these be the same then that can be considered a duplicate.

Currently I have a master table called tbl_all_hotels which contains all the fields which I want in my master list.

I have 5 append queries, one for each part of the world. These simply appends data to the main table from the relevent data in the relevent database, with the fields mapped correctly. I simply import all data.

As stated the problem is that some hotels could be in 2 parts of the world [this is correct the 'parts' are actually brochures which overlap]. How can I remove these from my master table? The data _should_ be the same [my manager assures me they are so they are ]

I thought this should be straight forward but not as easy as I thought.

Not sure what code to include but as stated above my master table looks like:

----------------
|tbl_all_hotels|
----------------
HtName
RoomType
Occupancy
StartDate
End Date
CostPrice
Currency
FromDB

As you can see when I update what DB, I update where the data has come from and when I run the 'find duplicates' query they are all in 2 'brochures'

My thought was to create a unique key but I don't understand how access addresses these - i have tested but my results were un-sucessful. Can I identify HtCode,RoomType,Occuppancy,Startdate as primary keys?

oooomph

Many Thanks





Trancemission
=============
If it's logical, it'll work!

 

[PHV]

Can I identify HtCode,RoomType,Occuppancy,Startdate as primary keys
Yes, in table design view.
With the Ctrl key depressed click this fields and when they are all selected click on the key icon.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trancemission]

Slaps self on head and wakes up.

Cheers for that PH.

Trancemission
=============
If it's logical, it'll work!