i have a query where i am trying to link 3 files, the character field is FINVNO, It has six characters in two of the files and can easily be linked (invoice numbers- ie. 156942)...in the 3rd file there are 7 characters...the first six characters are the same as the other two files but it has a final character (a letter)..
how can i link this file to the other two files to run a query?? the last letter in the third file has nothing to do with my query (156942S), the letter S has nothing to do with the query.
I don't know if you just need matching fields from each of the 3 tables or you want to do a JOIN of all of some of the tables - so I will not try to guess what you need in that regard.
But as to finding matching records....
FROM Table1, Table2, Table3;
WHERE LEFT(UPPER(Table3.FINVNO),6) = LEFT(UPPER(Table2.FINVNO),6);
AND LEFT(UPPER(Table1.FINVNO),6) = LEFT(UPPER(Table2.FINVNO),6);;
By doing the above you are:
1. Using UPPER() on all values will eliminate any case-sensitivity issues
2. Using LEFT(,6) will only look at matches where the left-6 characters are the same
As it is above everything needs to be the same as the values in Table2 ('main' table).
If you use your 3rd table as the 'main' table you might get more records returned.
Play with it some to see how it works.
You can change the above around anyway you need to achieve the results you want.
How would i link these files in a query? Both have a field called FINVNO, one file has six characters for the field, the other file has 7 characters for the field. In both files the first six characters are the same.(for example 157141 in one file, and 157141S in the other file) I need to link the files based on the fact that each has the same first six characters. How is this done in a query in dbaseiv? Thank you.
I already showed you how to link the files in a query in my previous post.
Its is true that I am not using Dbase IV, so I cannot be 100% sure that the language functions that I showed are in that 'flavor' of the language, but UPPER() and LEFT() are supported in most, if not all, languages that incorporate SQL queries.
LEFT(UPPER(Table3.FINVNO),6) = LEFT(UPPER(Table2.FINVNO),6)
That links Table2 to Table3 using the LEFT 6 characters of the FINVNO field
LEFT(UPPER(Table1.FINVNO),6) = LEFT(UPPER(Table2.FINVNO),6)
Links Table2 to Table1 using the LEFT 6 characters of the FINVNO field
Therefore using both of those as shown in my sample code would link both Table1 & Table3 to Table2
So what is it SPECIFICALLY that you do not understand?