Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Simple Update Quert

    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

  2. #2
    Join Date
    Jan 2004
    Location
    amsterdam
    Posts
    31
    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    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"

  4. #4
    Join Date
    Jan 2004
    Location
    amsterdam
    Posts
    31
    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.

  5. #5
    Join Date
    Feb 2004
    Posts
    8
    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?

  6. #6
    Join Date
    Jan 2004
    Location
    amsterdam
    Posts
    31
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Gold Coast, Queensland, Australia
    Posts
    15

    Smile Re: Simple Update Quert

    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
    YuriK

  8. #8
    Join Date
    Jan 2004
    Location
    amsterdam
    Posts
    31
    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.

  9. #9
    Join Date
    Feb 2004
    Posts
    8

    Re: Simple Update Quert

    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!

    Originally posted by Yuri Korin
    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

  10. #10
    Join Date
    Jan 2004
    Location
    amsterdam
    Posts
    31

    Re: Simple Update Quert

    Originally posted by dmacd
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    Gold Coast, Queensland, Australia
    Posts
    15

    Red face Re: Simple Update Quert

    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.
    YuriK

  12. #12
    Join Date
    Feb 2004
    Posts
    8
    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

  13. #13
    Join Date
    Oct 2003
    Posts
    107
    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.

  14. #14
    Join Date
    Feb 2004
    Location
    Gold Coast, Queensland, Australia
    Posts
    15

    Talking

    [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.
    YuriK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •