Growing Records..

Okay,

This will probably sound confusing, but I'm going to try my best to hone down the issue.

First:
There is an imported DBF table that I have to perform calculations on, primarily of the string variety, but also with a few type conversions (such as: String "06" = CDate("1/1/" & "06") -> into a Date/Time field)

Okay, given that, I was doing many things all at once, but found by doing it in several stages allowed me the ability to:

Narrow down the actual amount of items in the DBF that I will import
Speed up some of the calculation in the Transfer


So I have two tables:
tbl_Original
Tape_ID: Text(10) - {Primary}
Title: Text(50) - {Primary}
Title_Rem: Text(50)
Category: Text(3)
TitleLen: Number

ActionQuery on Tbl_Original:

INSERT INTO tbl_Original (tape_id, title, title_rem, category, TitleLen)
SELECT qry_dbf.tape_id, qry_dbf.title, qry_dbf.title_rem, qry_dbf.category, Len(RTrim([qry_dbr.title])) as TitleLen)
FROM qry_dbf;


tbl_CalcTitles
TapeID: Text(10)
MovieTitle: Text(100)
AltTitle: Text(100)
Remarks: Text(50)
Paren: Text(20)
Bracket: Text(30)
Orig_Id: Text(10)
OrigTitle: Text(50)

Now to take the data populated into tbl_Original, there are many rules that I have to follow. Example: if Original.Title = {text}>>{text} the the left of the double ">" signs, is the MovieTitle, to the Right is the Alt Title. If in the MovieTitle there is a (*) {parenthetical} the parenthetical is removed and the text insde the () is instered into Paren. Similar rules apply for Bracket, just with [] square brackets instead of Parentheses (). Also, if the Trimmed Title Length from the Original insert =40 (the maximum for the DBF field, AND the Original Category field = "SEE" then the title_rem field is appended to the AltTitle field of tbl_CalcTitles, and the the tbl_CalcTitles.Remarks field is set to "" (emtpy string), otherwise the title_rem field is populated into tbl_CalcTitles.Remarks. Also for attempted relationships for later use, I directly transfer the tbl_Original.title and tape_id fields to the Orig_Id and OrigTitle fields of tbl_CalcTitles.
Yes, This is very annoying, and i've spent some time writing all the little funcitons to do exactly this, and be accurate every time I run my action query. (It is pointless to post here, since it involves way too much vb back end code to post it all.)

qry_dbf: 33099 records
tbl_Original: 33098 records
tbl_CalcTitles: 33098 records

Now, given this setup, we know that in my insert into tbl_Original, 33098 records of qry_dbf were unique in the paired tape_id and title fields. So now I want to link qry_DBF to the tbl_CalcTitles, so that I get exactly 33098 records from qry_dbf that match up witht he Orig_id and OrigTitle in tbl_CalcTitles. Yet, no matter what i do, I do not get this result. My last attempt was a LEFT JOIN from tbl_Original to qry_DBF and I got 33138 records returns. How did grow extra records. if I ONLY have 33098 in tbl_Original, and 33099 in qry_dbf, where are the 40 extra coming from?

So I tried to link tbl_CalcFields to qry_dbf on an INNER join on the same fields, and now, this is purely mind-boggling:
Each Time I alter the number of fields DISPLAYED from qry_dbf, the total number of records return alters. Example:

tbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title: 33092 records
tbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title, qry_dbf.category, qry_dbf.format: 33072 records

Now this is completely beyond what one would expect. I already know that there are 33098 unique records in qry_dbf, on the pairing of fields, Tape_ID and Title, of with all 33098 records are contained within tbl_CalcTitles with the EXACT replica of those two fields. so If I am doing an Inner Joing of tbl_CalcTitles and qry_Dbf on those two fields which produced 33098 unique records, why would altering the number of DISPLAYED fields affect that outcome. Why am I not getting all 33098 records from qry_dbf that are contained in tbl_calcFields?

Thanks a lot, this has been extermely nerve-wracking.

Jaeden "Sifo Dyas" al'Raec Ruiner

PS - qry_DBF is SQL source defined as:

SELECT tap001.*
FROM tap001 IN 'tap001.dbf'[ODBC;DSN=Visual FoxPro Database;SourceType=DBF;SourceDB=D:Quarterly]
ORDER BY tap001.tape_id, tap001.title;