Moving data from table to table

[drpep]

I use the following program to sum and move numeric data from one .dbf file to another. It works fine for numeric data. My problem is that I need to move character data from one .dbf to another .dbf. Can someone change the following program so it would accommodate character field data? Thanks in advance.

? 'STARTING REPLACE PREVIOUS MONTHS PROGRAM'
GO TOP
FYTOT = 0
ACCTNUM=ACCT
do while acctnum >=32000 .and. acctnum <=36511
*DO WHILE ACCTNUM >=32000 .AND. ACCTNUM <=32018
? ACCTNUM
USE h:\NOV2002\PB121702
SUM fytd FOR ACCT = ACCTNUM .AND. CATEGORY = 2000 TO FYTOT
USE u:\execr\2003database\NOV2002\NOV
REPLACE fytd WITH FYTOT FOR CATEGORY = 2000 .AND. ACCT=ACCTNUM
* ACCTNUM=ACCTNUM+1
ENDIF
IF ACCTNUM = 36511
ACCTNUM = 37500
ENDIF
ACCTNUM = ACCTNUM +1
GO TOP
SKIP
ENDDO

 

[wgcs]

What's not clear is what character data needs to be transferred... (you can't &quot;SUM&quot; a char field... that doesn't have any meaning!)

Do you mean that you have character representations of numbers and you want to SUM those numbers? If so, just change the SUM command to:

CALCULATE SUM( Val( char_fytd ) ) FOR ACCT = ACCTNUM .AND. CATEGORY = 2000 TO FYTOT

 

[drpep]

I apologize for any confusion. I do not want to sum the character data. I simply want to move character data from one .dbf to another using a modified version of the presented code. Thank you for your quick response.

 

[wgcs]

Ok, this should get you going:

  USE h:\NOV2002\PB121702 ALIAS Origin
  SUM fytd FOR ACCT = ACCTNUM .AND. CATEGORY = 2000 TO FYTOT
  USE u:\execr\2003database\NOV2002\NOV ALIAS Dest
  REPLACE Dest.charField WITH Origin.CharField FOR ( Dest.KeyField=Origin.KeyField)
  * Note: The "FOR" clause is optional. Without it,
  *  only the current record has its data replaced.

I don't know what data you're replacing with what; It seems you don't have a one-to-one relationship between tables. If the above doesn't give you the clues, maybe you could explain what char data needs to be moved.

 

[DSummZZZ]

Whatever you're moving, you need to do a replace:

REPLACE TableA.Field1 WITH TableB.Field1 FOR.......
-or-
IF ACCTNUM = 36511
REPLACE acctnum WITH 37500
ENDIF

You can't just do TableA.Field1 = TableB.Field1 or VFP will think you are comparing the values of the fields rather than replacing.
Dave S.

 

[drpep]

The data that I want to move is personnel data from one .dbf to another. The key field in both files is employee number (empln). I need to have the program sequentially select the employee number from the original file, which is a numeric field, and the corresponding employee’s address, which is a character field to a memory variable, I presume. The program then opens the other .dbf file finds the corresponding employee number and replaces the address field for that employee number.

Thanks again for your patience

 

[DSummZZZ]

You can use something like:

USE OldEmp IN 0
USE NewEmp IN 0 ORDER EmpNum  &&... if there is an index

SELECT OldEmp
SCAN
   SELECT NewEmp
   *... if there is an index, use this line
   SEEK OldEmp.EmpNum      
   *... otherwise, use this one
   LOCATE FOR EmpNum = OldEmp.EmpNum  
   IF FOUND()
      REPLACE OldEmp.Address WITH NewEmp.Address
   ELSE
      WAIT WINDOW 'Unable to find employee number'  TIMEOUT 3
   ENDIF
   SELECT OldEmp
ENDSCAN

Dave S.

 

[drpep]

Thank you for your assistance. Your solution worked perfectly.