If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Other PC Databases > FoxPro Calculations using two tables

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Nov 2012
Posts: 24
FoxPro Calculations using two tables


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 ?

Reply With Quote
  #2 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,307
explicitly refer tot he table within the query
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
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote
  #3 (permalink)  
Registered User
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.

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;

* < 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
* --- '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 ---
* --- 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 ---
* --- Replace ALL File1 ColA values with resultant value ---
REPLACE ALL File1.ColA WITH (File1.ColA + File2.ColA) - File2.ColB
Good Luck
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Nov 2012
Posts: 24
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?
Reply With Quote
  #5 (permalink)  
Registered User
Join Date: Nov 2002
Posts: 108
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
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On