FoxPro Calculations using two tables

Orongo123 · #1 (**permalink**) 12-05-12, 08:24

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 ?

healdem · #2 (**permalink**) 12-05-12, 08:31

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

jrbbldr · #3 (**permalink**) 12-05-12, 10:15

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

Orongo123 · #4 (**permalink**) 12-05-12, 10:45

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?

jrbbldr · #5 (**permalink**) 12-05-12, 11:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech