Select query not pulling correct results

[turnerbk]

I am new to this forum. I hoping someone can help me. I’m at the end of my rope and completely frustrated.

I have two tables.

Table one - tblContent
Table two - tblConentImport

I have built a select query that will identify all records that from the tblConentImport table that do not already exist on the tblContent table.

Here is the SQL -
SELECT [tblConentImport].[ID], [tblConentImport].[LoginName], [tblConentImport].[Title], [tblConentImport].[Type], [tblConentImport].

, [tblConentImport].[Date Assigned], [tblConentImport].[Date Started], [tblConentImport].[Last Accessed], [tblConentImport].[Progress], [tblConentImport].[Date Completed], [tblConentImport].[Time Spent (min)], [tblConentImport].[Score], [tblConentImport].[Result]
FROM tblConentImport LEFT JOIN tblContent ON ([tblConentImport].[Code]=[tblContent].[Code]) AND ([tblConentImport].[Type]=[tblContent].[Type]) AND ([tblConentImport].[Title]=[tblContent].[Title]) AND ([tblConentImport].[LoginName]=[tblContent].[LoginName])
WHERE ((([tblContent].[LoginName]) Is Null) And (([tblContent].[Title]) Is Null) And (([tblContent].[Type]) Is Null) And (([tblContent].[Code]) Is Null));

This query is supposed to provide a list of records that do not already exist on tblContent. This doesn’t appear to be working if the [tblContent].[Type]=”Class”.

I then built an append qry that appends the new records to the tblContent table.

Here is the SQL - 

INSERT INTO tblContent
SELECT [qrySelectContentTable].[LoginName] AS LoginName, [qrySelectContentTable].[Title] AS Title, [qrySelectContentTable].[Type] AS Type, [qrySelectContentTable].[Code] AS Code, [qrySelectContentTable].[Date Assigned] AS [Date Assigned], [qrySelectContentTable].[Date Started] AS [Date Started], [qrySelectContentTable].[Last Accessed] AS [Last Accessed], [qrySelectContentTable].[Progress] AS Progress, [qrySelectContentTable].[Date Completed] AS [Date Completed], [qrySelectContentTable].[Time Spent (min)] AS [Time Spent (min)], [qrySelectContentTable].[Score] AS Score, [qrySelectContentTable].[Result] AS Result
FROM qrySelectContentTable;

This works great as long as the [tblContent].[type] is not equal to "Class".

This select query and append query have now created duplicate records in my tblContent for [tblContent].[Type]=”Class”.


Please help me. I'm loosing my mind. Below is the address to view this database. The select qry is named - qrySelectContentTable. The append qry is named qryAppend Content Table.

[URL unfurl="true"]http://briefcase.yahoo.com/turnerbkgabrobins[/URL]

Any help you can provide will be greatly appreciated.

 

[lupins46]

'This query is supposed to provide a list of records that do not already exist on tblContent. This doesn’t appear to be working if the [tblContent].[Type]=”Class”.'

In what way do you think it is not working?

 

[formerTexan]

Hi,

I have had a look at the db and am also puzzled.

First I went to the query wizard (easy can be good) and created an unmatched records query with the 4 linked fields. It returned 370 (I think) supposedly unmatched records. Running your query I got the same results.

Looking at the two tables and comparing the "unmatched" records, I am at loss to see what the differences are.

I think your difficulty lies not with "Class" Type, but with the Code field. Drop this link from your query and no records are returned. Drop the other three links instead and 370 records are returned.


So I ran update queries on the Code field in both tables and replaced nulls with "." This reduced the unmatched returns by 97. Replacing the "." with nulls again added the 97 records back.

Sooo... what you have seems to be a problem of Jet not reading apparently duplicate strings/values in this field as the same.

The Code field properties in both tables seem to be the same, but have a look again in case I overlooked something. Otherwise I am at loss for reasons for the problem.

As for temporary fixes,
1. you can try scrubbing the data in the Code field;
2. for grins, create a new table, set a multifield primary key and copy/paste the old data into a new table and see what happens;
3. leave the Code field out of the table links in the query for unmatched records.

Or hope for sharper minds elsewhere.

Cheers,
Bill

 

[dRahme]

Hi, I think Bill is correct. It is the Code field that seems to be causing the problem. The only thing I might add would be to set the default value for the Code field to something other than null.

 

[turnerbk]

Thank you all for your help. I'm going to populate the code fields for type = class. Those are the only records that have no data in the code field. If that doesn't work, I will try to eliminate the code field from the query altogether. Thank you for all of you help. I'll let you know how it goes.

Sincerely,

Kelly

 

[turnerbk]

RESOLVED - For some reason, the select query did not like that fact that [tblConentImport].

 for anything with [tblConentImport].[Type] = "Class" was blank.   I updated the Import and main table with a value in that field.  After doing this the query works. 



Thank you for all of your help.