Append unique existing "" field works but not "abc"

[Hilding]

Hi,


I'm a casual level i think.. have a product--part db that import xls then do some aggregate to export result as a xls-file. It worked fine.


But now, I don't understand what goes wrong here!
I have a import temp-table tblTemp_Comp, and the residing table TblManuf_comp that is a linked DD-field in the major Manufacturer table.
Learning from forum I start populate the Look-up table "TblMauf_comp" before doing the other append stuff to the manufacturer table. It all works fine.
But, the last import there was some blank/"" rec's manufactName and partName fields, and those has find its places before!!


Now, I tested, these BLANK rec's are now actually appended to the DD-table even though ID 941 is the ""-post.
If I now put an 'G' in that blank field and run the query it responds with "...will append 0 rec(s)" , i.e it's a dupe! (and I have a G there)


My append query:
INSERT INTO TblManuf_Comp ( Manuf_name )
SELECT TblManuf_Comp.Manuf_name
FROM tblTemp_Comp LEFT JOIN TblManuf_Comp ON tblTemp_Comp.Manuf = TblManuf_Comp.Manuf_name
WHERE (((TblManuf_Comp.Manuf_name) Is Null));


The DD-table:
Field: Manuf_name (shrt_txt);
Required No
Z length No
Indexed Yes (No dupes)
...the rest No.


Best regards
Per

[Gicu]

Can you please post a couple screen shots showing us the problem, sorry but the explanation is a bit hard to follow?

Cheers,

[CJ_London]

Agree with Gicu - you sql does not make sense

it is saying you want to insert Manuf_name from TblManuf_Comp into TblManuf_Comp but only when it is null


suspect you should be selecting tblTemp_Comp.Manuf

[Hilding]

Hello again
Thank's for responding my question.


Yes, that query was wrong!


I did attached 3 pics to desribe my issue... as I still have!


Capture_Test_Query1.png:
You can see the rec's 19 and 26 are missing. 19: I put "King Carl" as manufname in the input table only; a mismatch = not present.
The rec 26 I left it blank in hope that TblManuf_Comp ID 941 would have it linked. I try to set k in both sides and it works: ID 26 goes in.
I even try to use the Nz function in field AB -not working.


Capture_tblManufact_Comp_ID941:
This is the table TblManuf_Comp that store all manufacturers name. This rec #941 = "" I was thinking to have linked all records what don't have a manf-name assigned.


Capture_Qry3:
This is how I setup to test this trouble. TblManuf_Comp is a look-up table to showing the Company name in TblManufact.

[Hilding]

Hello again
Thank's for responding my question.


Yes, that query was wrong!


I did attached 3 pics to desribe my issue... as I still have!


Capture_Test_Query1.png:
You can see the rec's 19 and 26 are missing. 19: I put "King Carl" as manufname in the input table only; a mismatch = not present.
The rec 26 I left it blank in hope that TblManuf_Comp ID 941 would have it linked. I try to set k in both sides and it works: ID 26 goes in.
I even try to use the Nz function in field AB -not working.


Capture_tblManufact_Comp_ID941:
This is the table TblManuf_Comp that store all manufacturers name. This rec #941 = "" I was thinking to have linked all records what don't have a manf-name assigned.


Capture_Qry3:
This is how I setup to test this trouble. TblManuf_Comp is a look-up table to showing the Company name in TblManufact.

[CJ_London]

I'm no further forward with understanding what you are trying to do or what the problem is

[Gicu]

Same here, would you please upload a sample db with the two tables and the query you have and try to explain what would the expect end result be.
If you want to add missing records (that are present in the source table but not the destination table) you need to have a left outer join between the matching fields with criteria Is Null on the field from the destination table.

Cheers,
Vlad