Query Challenge - Merge Data

[rjparker1]

I have a doozie for anyone that thinks they can tackle this one. Let me give some background. I have a table from another program. This program creates records of clients based on products, in this case financial accounts. From this table, all the usual stuff is in there, name, address.. blah blah, and it also has what accounts they have, like mutual fund, IRA, that sort of thing. All I am concerned is, in this table, the first 4 records:

MasterID
ClientID
FirstN
LastN

Now, here is the problem. That program, whenever a client creates a new financial account, it creates a unique ClientID for that customer, even if they already have one. So lets say we have a client, Bob Smith. He starts an IRA. A week later, he comes back, and now he wants a Mutual fund. Instead of the program looking his current info up, it does an internal match againts the MasterID and it creates another ClientID. So essential at this point, Bob started off with ClientID 10. Then when you want to add another account for Bob, internal it looks the same, because it just marks the first record as Master, assigns the same MasterID to another ClientID then copies references it like that.. for the life of the customer.

So here is what it looks like.

MasterID ClientID FirstN LastN
10 10 Bob Smith
10 11 Bob Smith

What I want to know is, is it possible to join the data, to have 1 unique field after I query the data from that table? I can query on records, but it shows say, 4 instances of Bob Smith in the Query, each with their own ClientID, since its a unique record. And then, make a table with the unique ClientID so that I end up with 1 ID for each client. Am I barking up the wrong tree.. and should just leave it at this point?

 

[shacket]

Excellent presentation of your question!

What you wish to do is actually very simple. Use the "Max Of" function to retrieve only one of each person. (You click on the Sigma button up next to the Add Table icon, then in the field you wish to have only one of, choose Max, or First, or Last - all will get you only one instance of each person).

The real issue is: do you have a unique field with which to distinguish people? If the MasterID is actually a primary key for each individual, you are golden. Simply use the above example on that field.

If it is not, then you have to do it by name (you can concatenate first and last if you have to) but that will only work as long as you don't have two investors named Bob Smith.