Hi,

I amcompletely new to paradox, and have inherited support for a datbase where the data has been incorrectly entered.

I have 2 fields Surname and Forename - however user s have entered the persons full name into the Surname field. I would like to run a query to populate forename and to replace Surname field with the correct value.


Any advice greatly appreciated!

Many thanks,

Dawn

If I understand you correctly, you simple have to rename both fields in

Tools|Utilities|Restructure

If you like (for sake of clarity) you can move field 2 in front of field 1.

No sorry I have been unclear in my description....

I have one field populated with data - Surname = "Joe Bloggs"

I would like to have two fields - Surname = "Bloggs", Forename="Joe"

In that case I would export the database to ASCII delimited, import in a textprocessor, write a macro which places a

","

delimiter just before the surname, write to text format and read it again in Paradox. Then you will have 2 fields. If you can't do this, send it to me and I will do it right away.

Hi,

I think there is a complication with this method - the table also contains a column with the person's photo - when you export would you not loose it?

In that case it will become a little more complicated, but still is possible.
1. Add a field ID, can be an automatic number field
2. export these two fields only
3. separate the name field, you will have 3 fields now
4. perform an ADD query on ID which adds the two new fields in the right records.
5. delete the combined field and the ID field

This may sound rather clumsy, but must work.

Here's an alternative that may do it rather simply:
1. Make a backup of your table!
2. I have assumed that all names entered into the current Surname field have been entered with a consistant format i.e. Forename then a space then the Surname.
3. Run the following script, replacing YourTable with the name of your table:

var
tcPersons tCursor
CurrSurname String
foreEnd smallint ; this holds the position of the space between
; the Forename and the Surname
srnmLong smallint
RecCount longint
endvar

tcPersons.open("YourTable.db")
tcPersons.edit()
tcPersons.home()
RecCount = 0
scan tcPersons:
CurrSurname = tcPersons.Surname
foreEnd = CurrSurname.search(" ") ; find position of the space between names
tcPersons.Forename = substr(CurrSurname,1,foreEnd-1)
srnmLong = CurrSurname.size() - foreEnd
tcPersons.Surname = substr(CurrSurname,foreEnd+1,srnmLong)
RecCount = RecCount + 1
endScan
tcPersons.endEdit()
tcPersons.close()
msgInfo("Records processed","The number of records processed was "+string(RecCount)+".")

Good luck

that is interesting stuff but a lot of work, just exporting, one keystroke in a macro and importing is much faster. And what if the field contains three names? or 4? This can be easily handled in your textprocessor, but to program this takes much more time.

This works great on a test subset of 100 records, however when I try on my database of 6000 records it seems to disappear up its on behind - how can I improve efficiency?

Any ideas!

Thanks again for all advice received!

I still do these things in Wordperfect 5.1 with a very simple but efficient macro system. One macro, perform this one 6000 or whatever times with one keystroke and DONE!
I don't see any problem.

Sorry. There should be an extra statement just before the endscan statment.
It is "tcPersons.postRecord()".
That should clear the buffers. It looks like Paradox tries to hold onto all the updated records unless you post them.
Let me know how it goes.
Cheers.

Hi it is still locking up my system....

The fields being modded are key fields....Surname is changing from 'John Smith' to 'Smith' , so do I need to do something to prevent my table re-ordering every time I write a record?

Thanks,

Dawn

Yes, I'd make a copy of the table using a query (which removes the keys), then make the changes to the copy. After that, you can ADD (update mode) the copied table back into the original.

[QUOTE][SIZE=1]Originally posted by dmacd
Hi it is still locking up my system....

The fields being modded are key fields....Surname is changing from 'John Smith' to 'Smith' , so do I need to do something to prevent my table re-ordering every time I write a record?

Thanks,

Dawn

Hi,
I should have checked to see if there was any index processing involved with this exercise. Just for the programming exercise, which we all need, I have rewritten the script to accomodate a primary index. This should overcome your locking up problem, I hope.

method run(var eventInfo Event)
var
tcPersons tCursor
CurrSurname String
foreEnd smallint ; this holds the position of the space between
; the Forename and the Surname
srnmLong smallint
RecCount longint

tbPersons Table
arFieldNames Array[1] String
dyAttrib DynArray[]AnyType
endvar

tbPersons.attach("YourTable.db")
tbPersons.setExclusive()

; delete YourTable.db primary index
if not tbPersons.dropIndex() then
msgInfo("Error", "Can't drop YourTable.db primary index.")
return
endIf

if tcPersons.open(tbPersons) = FALSE then
msgStop("Stop!", "Can't lock YourTable.db table.")
return
endif

tcPersons.edit()
tcPersons.home()
RecCount = 0
scan tcPersons:
CurrSurname = tcPersons.Surname
foreEnd = CurrSurname.search(" ") ; find position of the space between names
tcPersons.Forename = substr(CurrSurname,1,foreEnd-1)
srnmLong = CurrSurname.size() - foreEnd
tcPersons.Surname = substr(CurrSurname,foreEnd+1,srnmLong)
RecCount = RecCount + 1
tcPersons.postRecord()
endScan

; Now recreate the primary index
arFieldNames[1] = "Surname"
dyAttrib["PRIMARY"] = True
dyAttrib["MAINTAINED"] = True
if not tcPersons.createIndex(dyAttrib, arFieldNames) then
errorShow()
endif

tcPersons.endEdit()
tcPersons.close()
tbPersons.unAttach()
msgInfo("Records processed","The number of records processed was "+string(RecCount)+".")

endMethod

Good luck.