Check for duplicate key in related value pair table

[mrubenson]

I hope this makes sense...

 

I am trying to import data from a previous database solution with a slightly different approach to our data.

 

The data is stored i just two tables. One table contains all the people in the database, the other table contains more or less all the actual data about the persons in the first table in value pairs. So for instance the phone number for a person is not in the 'people' table but in the 'data' table.

 

My problem is that this makes it difficult for me to import the data into my new solution where I would have the phone number (and all the other data) in the "people" table (as I have no need to store more than one phone number etc. per person).

 

I could just flatten the old database by hand and then import it, but before I do that I want to make sure that there is not any instances of one person having more than one value pair in the 'data' table - which there shouldn't be but I want to check anyway to be 100% sure.

 

However, I have no idea where to start in checking this save for browsing 20,000 records one by one. Can anyone help me out here?

[comment]

I am not sure I understand what you want to do here. It seems like the previous solution was purposefully designed to accommodate people having multiple values for each key, e.g. several phone numbers. Are you sure you want to discard the "extra" values - and if yes, which ones do you want to keep? An if you only want to keep one of each kind, what's the point of checking for duplicates? Suppose you do find some - do you have room for them in your new solution?

[mrubenson]

There is only supposed to be one value per person actually. The previous solution has keys like "phone 1", "phone 2", "phone 3" - so it really isn't very well thought out.

 

My reason for checking for duplicates is exactly so that I do not discard any data that has unintentionally ended up in the wrong place in the old solution, but can move that data to my new solution as well.

 

As soon as I am confident that there is no more than one instance of every key for each person, I can rely on that when importing the data into my new solution - which does have room for multiple values, but only where I need them.

 

And I did figure it out... though there might of course be an easier way of doing it.

Since I was just going to check for any duplicates and not actually be doing anything with the data (at least for now), I figured using a report might do the trick.

So:
1. Set up a report based on the 'data' table and group and sort by firstly id_person, secondly data:key
2. Add a summary field to count data:key
3. Remove every part of the report except the sub-summary part.
4. Voila! A (super) long list of just '1's.
5. Set to print in columns and print to PDF
6. Search PDF for 2s and 3s and so on.

When I didn't find anything but '1's I checked my solution by intentionally adding a duplicate just to check that it turned into a '2' in my report which it (very politely) did.

Thanks for helping out anyway!