Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    24

    FoxPro Calculations using two tables

    Hi

    I have a .dbf table with column named ColA and another .dbf file with a column also named ColA and a ColB. How can I make FoxPro to understand that I want to create a table (or extend one of the .dbf files) such as ColA(file1)+ColA(file2)-ColB ?


  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,519
    explicitly refer tot he table within the query
    eg
    select Table1.Cola, Table2.ColA deomTable1
    join Table2 on Table2.commoncolumnn = table1.commoncolumn

    replace the
    commoncolumnname, Col1, Col2 etc with the name(s) of the columns concerned
    table1/table2 likewise
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2002
    Posts
    108
    Once again, this type of question have VERY LITTLE to do with Foxpro/Visual Foxpro.

    Instead it is primarily a SQL Query syntax question
    The ONLY FP-specific syntax issue is the requirement of ending a line with a semi-colon when the command line is extended onto the next typed line.

    "I have a .dbf table with column named ColA and another .dbf file with a column also named ColA and a ColB."

    The first thing not mentioned is how are the two tables related to one another?

    Is there a Field (or multiple Fields) which is common to both tables?

    That is an essential part of getting to: (File1.ColA+File2.ColA)-File2.ColB or any other field value combination.

    Code:
    USE File1 IN 0  && 'Open' File1 in the next available workspace
    USE File2 IN 0  && 'Open' File2 in the next available workspace
    
    SELECT File1.ColA AS ColA1,;
       File2.ColA AS ColA2,;
       File2.ColB AS ColB2,;
       (File1.ColA + File2.ColA) - File2.ColB AS ResultVal;
       FROM File1, File2;
       WHERE File2.ColA = File1.ColB;
       INTO CURSOR Result READWRITE
    
    SELECT Result
    * < do whatever >
    Obviously to do the above something has to be in common with both tables.

    And, obviously, as could be done in ANY SQL Query (not FP/VFP specific) your query does not need to include the original ColA or ColB values.
    It could have just been the arithmetic expression result 'field' all by itself.

    For these type of operations I'd suggest that you study up on 'standard' SQL Query syntax.
    Yes, FP/VFP can utilize its one language-specific functions (not 'standard' SQL Query), but it can also support most of the 'standard' syntax as well.

    Alternatively the same thing can be done using 2 (or more) RELATED tables - over-writing the results into one of the tables
    Code:
    * --- 'Open' File1 in the next available workspace ---
    USE File1 IN 0  
    * --- 'Open' File2 in the next available workspace ---
    * --- EXCLUSIVE Use is required to Build/Create an Index ---
    USE File2 IN 0  EXCLUSIVE 
    SELECT File2
    * --- Build Index on which a RELATION between 2 tables can be established ---
    INDEX ON Field1 TAG Field1
    
    * --- Establish RELATION Between 'Parent' Table and 'Child' Table ---
    SELECT File1
    SET RELATION TO Field1 INTO File2
    * --- Replace ALL File1 ColA values with resultant value ---
    REPLACE ALL File1.ColA WITH (File1.ColA + File2.ColA) - File2.ColB
    Good Luck

  4. #4
    Join Date
    Nov 2012
    Posts
    24
    Hi JRBBLDR
    Thank you for your reply. As you noticed I don't have much experience in FP and none in SQL. You helped me before with shifting rows. I am using your code and now I need to multiply the table with shifted rows with others that are not shifted.
    What is a field?

  5. #5
    Join Date
    Nov 2002
    Posts
    108
    Yikes!
    The concept of a 'field' is not unique to Foxpro/VFP or SQL.
    It is a foundational aspect of ANY data application - regardless of the language involved.

    If you are familiar with Excel you might think of it as a Column.

    Data tables have one or more Records (in Excel those would be Rows)
    And each Record has one or more Fields (in Excel those would be Columns)

    Each Field is defined to be of a specific Field Type
    * Numeric (can contain Real Numbers with decimals)
    * Integer (can contain only Integer values)
    * Character (can only contain characters - even if the characters are numeric)
    * Date (can only contain Date values)
    * DateTime (the existence of these depends on the version of FP/VFP - they contain DateTime values)
    * etc....
    In your FP/VFP Command window type - HELP data AND field types

    You don't "multiply the table" by anything
    Instead you can multiple the table's Field value(s) by something

    And then utilize the field values from the desired records as needed.

    Within a data application you can also work on Variables which are created for specific purposes and those have Data Types and Values.
    The values of those too can be manipulated, but they are not tables

    Again, I think that if you spent some time with the free on-line (or downloadable) video tutorials at:
    Free Visual FoxPro Videos
    it might answer some of these very basic questions and establish a basic foundation of understanding

    And then with a basic understanding of Data Types, Data Tables - their structures and their contents, etc. we could begin to address your less-foundational questions.

    Good Luck

Posting Permissions

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