if statement

[FreeBird]

I am trying to normalize a large number of data. There are several instances of a name, some are missing middle name. I need to write an if statement and update fields so that, if last name, first name and address are the same then it will fill in the middle Init from another records (that has the same last, first and address). I also need to do this with e-mail, if last, first, etc are the same then it wll fill in the e-mail from another record.
The purpose of this is to have all the records in the table match. The dups operation just doesn't do it.
Please help someone. Thanks:)

[G Gabriel]

Re: if statement

Check the update operation instead.
And then again, in the end you will end up with duplicate records. Is that the object?
sounds to me, you need to be deleting the ones that match all but missing the middle initial rather than duplicate the complete ones.

[FreeBird]

Re: if statement

It is the update operation I want to do with the if statement. Yes there will be redundancy. I am creating a set: parent will have unique names, the problem is that if the child has 2 records and the only diff is middle initial, then I will have 2 unique records in the parent, so I am trying to eliminate this.

[MikeC]

Re: if statement

The Update operation sounds right.....I take it either of two ways as I read your question---both of which would use Update operations.

Either you have just many different names that are missing information that another table can supply....OR...you have many names in a table (such as an Invoice table) that are missing information and a different table can supply.

Regardless, the operation will take an expression and the genie may very well guide you through it....get to that point and then show us what expression you have tried and we can help.

MAKE certain you backup up your data (at least once) prior to doing this!!!

[FreeBird]

Re: if statement

so the has to come from another table? There is no other table unless I create one.

[Mike Wilson]

Re: if statement

huh? OK. I get it. You will have to open two instances of the table, one that goes through each record and gets the name/check for completeness, and a second one that you filter to middle initial field .not. blank to get the value and then all the records to for that name to change and update.

[FreeBird]

Re: if statement

Ok, let me make sure I understand. I open the same table twice. Then in one, I do a query if middle name is blank. What do I do with the 2nd instance of it?
I know how to do this if i choose the ones that don't have the middle name blank and then run a post operation to fill in the blank middl enames I created on another table. Is this what you meant?

[Tom Cone Jr]

Re: if statement

Senia, to begin with if you're using the concatenated field values of last, first, address, and zip (or state) to uniquely identify each record, you are not normalizing the table. You will have records with duplicate "key" values. The concatenated field values will not be unique, so they can't be the primary key for your table.

Nevertheless, what you are attempting can be done. I think this may be a case where you will need a custom script. The script, in pseudo code, would do this:

a) open the table
b) loop through the records first to last
c) if a record has an empty middle name or a missing middle initial then open a second instance of the table; set an index primary that sorts the records in order by your concatenated field values (excluding the middle name); fetches the first record that matches the current concatenated field value; examines it for a middle name; if present assigns the found middle name to the current record; else fetches and examines recs in the second instance of the table until all recs with the same concatentated field value have been checked or fetch past eof
d) skip records that have a middle name or initial in them already
e) don't forget to close both instances of the table

[MikeC]

Re: if statement

****Started this a while ago before Tom's method---which, of course, will work....but also means that xbasic will be required. The following should also work and utilizes genies.

I think I also understand now....one table with many of the same name but some do not have a middle name (or initial)....these you want to change to be exactly the same.

Let me know if this may be an easier concept.....
Creation of a "temporary" table that only has the full names--just people who have something for their middle name. This could most likely be produced from a Summary operation that simply looks for the record that has the Middle name field and groups on it.

Then use an Update operation to add the middle names to the actual table linked on First_Name, Last_Name, and Address.Or if what Tom stated is true, the concatenated field(s)

The same could be done for the email fields. Tom's method is more concise and less time would be involved....but if this is a one time update then time may be irrelevant.

Just the way I thought would be the simplest for others---Other ways am sure exist as well.

[G Gabriel]

Re: if statement

Maybe I took you down the wrong way.. maybe not.
Yes you can use update operation. Update operation does not use a transaction table (I think that's what Mike W was thinking).
In the update operation, use the General tab then assign a calc value to a field, then use an expression like previous() to make sure the values in the fields in question match the ones from the previous records. In the records selection, use selected records, use .t. for filter then order the records by those fields in question: First, Last, address etc. and also records in which middle="".

You could also do this using Post operation and As far as I can remember, yes you can use the same table both as the Master and Transaction, however this is a risky business as you might do things backward and copy blank middle field to the ones that are not. It is much better to copy the table to a new temp table, then use the temp table as your transaction table.

Better than all of the above is to write a simple script to do all of that. Here is a mock script:
First create an index with all records and sorted with first/middle/last/address

t=table.open("..your table..")
t.index.primary_put("..your index..")
t.fetch_first()
while .not.t.fetch_eof()
v1=t.first
v2=t.middle
v3=t.last
v4=t.address
t.fetch_next()
if t.first=v1.and.t.middle="".and.t.last=v3.and.t.address=v4
t.change_begin()
t.middle=v2
t.change_end(.t.)
t.fetch_next()
end while
t.close()

[MikeC]

Re: if statement

G,
I think the sort should be inverted maybe (descending)? A blank middle name would come prior to one with a value.

[G Gabriel]

Re: if statement

good catch.

[FreeBird]

Re: if statement

Tom, I agree that the concatenated field is not ideal. Here is a little more info on this: the database consists of licensed individuals. One of these individuals can have multiple license numbers in multiple states. He/She can also have multiple license types(so not just numbers). The information I received was from each of the 50 states and it is safe to say the data is all different. I was able to put it together all on one table (as oposed to having 50 different tables for each state). The total number of records is about 3 million.
Here is the problem, since a licensee can be listed in more than one state, and he can also have different licesnce types, in my final table he is appearing more than once. Also, a licensee can have multiple addresses depending on the state he is licensed in. For example, it is the same license number, but for one license type he has one address and or another license type he has another address.
I have to be able to search unique names by license type and then export an address, email, phone number for each agent.
Chances are very slim that one licensee with the same last, first and middle name in one state will appear twice.
The data to this database will be added on a large scale, like 100k agents. If I create a unique ID field I'd have to make sure that there are no dup ID's created, not sure how to ensure that.

[G Gabriel]

Re: if statement

Chances are very slim that one licensee with the same last, first and middle name in one state will appear twice.

Probably as slim as Michael Moore!
I think you better off with something like all of the above plus license Number. License number, one would assume and wrongly so, that it should be unique, but the conglomerate of all of the above should offer more unique values
That said.. not sure what is the point of the above exercise?
You need a set with the agents as the parent, then licenses as child. The parent does not have to have duplicate records.

[FreeBird]

Re: if statement

You are right Gabriel. You just gave me an idea which I actually thought of a while back and then it got lost in the ocean of other data.
Thanks Gabriel, MikeC and Tom. i'm going to work on this tonight :)