Working with shared postcodes (different business in the same building)

[jameshoneywill]

Hi

I'm working with a set of data from a National body that shows every public medical practice in the country. 

I have a set of customer data with the practices which are already a customer. 

The only link I have between them is UK Postcode. And I have been merging the queries on this basis. 

On the Government data, there can be multiple practices in the same building and therefore records have the same postcode 

Therefore; 

When queries are merged it returns duplicates of my customer where the Gov data has multiple postcodes for different practices

Once I do a count of these in the report it would show (for example) that I have 5 customers when actually I only have 1 out of 5 at that postcode. 

I understand why this happens and that without a unique identifier per record (on each side of the merge) that this is what to expect. 

However, I wondered if anyone had any ideas on how to overcome this? I have unique reference numbers on each side, however, they are not a shared reference number between the sets of data, probably doesn't help but just thought I would mention it 

Many thanks

James

[speedramps]

Hi James 

A key does what is says in on the tin.

If the key fits 5 door locks then you ned to try use soome of the other properties to try decide which is which.

If you cant and wnat to over come the duplicate key error, then that is easy !

Just add an index column to your query (1,2,3,4,5,6, etc) then add this as decimal to your key

eg key 77.3, 77.4, 77.5.

So you joint key (77) and unqiue keys (77.3, 77.4 and 77.5).

Hope that make sense.

Please click the thumbs up and accept as solution 😁