unmatched query

[kkson]

I have a dos based program that I export data from in text files. I can't change any of the export criteria so I have to make 2 files instead of one. One file contains all the publication info. However the export field sizes for pubnum and pubtitle are not big enough to show the complete field. So the other file contains the pubnum and pubtitle. (still limited on the dos programs setup, so the title still gets cut off, but it shows enough to work)
I then import the text files to tables (tblpub and tbltitle) then I update the pub title in the in tblpub with the full title in tbltitle. However I can't get the pubnum to update correctly. In the tblpub i have 3 recs that are12-3-5-t-e-h) - but due to the field being cutoff the same pubnum in tbltitle are: (12-3-5-t-e-h-1, 12-3-5-t-e-h-2, 12-3-5-t-e-h-3) I do an unmatched query and get 3 records. But if I try to update tblpub to correct the pubnum I get double the records, even if i set the unique records on in the query, during the update it doubles. But if I just view the records I shows correctly.

How do I get the unique records to stay during the update.

thanks.

 

[kkson]

last post may have been to confusing. If one table has the field:
PUBNUM
------
the3
the3
the3

with idno as a autonumber key. due to the pubnum field being cutoff in the import(not due to the import process but the text file it is importing)

And the other table has the field:
PUBNUM
------
the34
the35
the36

with the pubnum (ie the34) as the key.

how can I update the first table to reflect what is in second table? I need the full pubnum in the first table.

thanks

 

[SkipVought]

Wouldn't you be guessing?

What is the SOURCE DATA for the DOS program?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[kkson]

More info. Using an unmatched query and another query I can get the second query to show the the full pubnum associated with the two tables: Here is the sql:

unmatched query:

SELECT tblImportPubTXT.MFLOCATION, tblImportPubTXT.PUBNUM
FROM tblImportPubTXT LEFT JOIN tblImportTitleTXT ON tblImportPubTXT.PUBNUM = tblImportTitleTXT.PUBNUM
WHERE (((tblImportTitleTXT.PUBNUM) Is Null));

then the second query that shows the truncated pubnum and the full pubnum:

SELECT DISTINCT qryUnmatchedtblImportPubTXTtitles.PUBNUM, tblImportTitleTXT.PUBNUM AS TitlePubNum
FROM qryUnmatchedtblImportPubTXTtitles, tblImportTitleTXT
WHERE (((tblImportTitleTXT.PUBNUM) Like "*" & [qryUnmatchedtblImportPubTXTtitles]![pubnum] & "*"));

At this point I can't update the tblimportpubtxt.pubnum with the tblimporttitletxt.pubnum. The second query is unupdatable so using that as an update query will not work.

thanks

 

[SkipVought]

I believe you are getting what is known as a Cartesian Join -- each row in table A matched with each row in table B.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[kkson]

The dos source is in a .dat file. I can't get access to import it. If I could that would eliminate alot of querys.