Append Query trying not to add duplicate records to new table

[anoble1]

I am throwing a blank on an easy one.
tblHours has 2 less records than Machine Hours table.

I would like to say if [Machine Hours].[MODSER#] has a record not in tblNotes.[MODSERIAL#] then add it. I don't want any dulicates. Keeps saying there are zero records to add when there should be 2.

INSERT INTO tblNotes ( [MODSERIAL#] )
SELECT [Machine Hours].[MODSER#]
FROM tblNotes RIGHT JOIN [Machine Hours] ON tblNotes.[MODSERIAL#] = [Machine Hours].[MODSER#]
WHERE ((([Machine Hours].[MODSER#])<>[tblNotes].[MODSERIAL#]));

[NeoPa]

Hi again.

You seem to be falling over the concept that Null values are, or are not, equal to something else. They are neither. They are simply Null.

Let me know if you need more help :-)

[isladogs]

Following on from @NeoPa's comment, suggest you use the wizard to create an unmatched query then convert that into an append query.

Having said that, why do you want to have the same info in two tables?

[anoble1]

Hmm. Are you referring to line 3 on the equal sign?

Hi again.

You seem to be falling over the concept that Null values are, or are not, equal to something else. They are neither. They are simply Null.

Let me know if you need more help :-)

[anoble1]

With the 2 tables I can see there are 2 records that are extra on one table and 2 blank spaces on the other table. But after that on the append, I get zero.
I need 2 tables because one of them is going to be a table to put notes in that stays. The other table will be updated daily with new records. Am going to tie that field to both tables so I can add notes to the new records if I need to. I don't want to lose by notes I put in and don't want duplicates.

[NeoPa]

ANoble:
Hmm. Are you referring to line 3 on the equal sign?

No. I'm referring to the <> on line #4. When two records don't match then the value in all the fields of the missing record is Null. If you compare Null with any value it doesn't return either True OR False. It returns Null. Thus you need to check for Null (WHERE [XXX] Is Null).

[NeoPa]

ANoble:
I need 2 tables because ...

That doesn't sound like a very solid approach if I'm honest. Why would you need to create records with no useful values in (at the point of creation) when you can create them as and when you have something you need to add.

Always remember that the most important part of any database project is the part where you decide the structure of the data. You may decide more attention is required on this before you allow yourself to proceed with implementing the design.

However you proceed, I wish you the best of luck :-)

[cactusdata]

Use a Left Join:

INSERT INTO tblNotes 
    ( [MODSERIAL#] )
SELECT 
    [Machine Hours].[MODSER#]
FROM 
    tblNotes 
LEFT JOIN 
    [Machine Hours] 
    ON tblNotes.[MODSERIAL#] = [Machine Hours].[MODSER#]
WHERE
    [tblNotes].[MODSERIAL#] Is Null;

[NeoPa]

Hi ANoble.

Let me just clarify what Cactus is advising, if I may, when he says use a LEFT JOIN instead of a RIGHT JOIN. This is advice I would give myself, though I would say it's not critical. The two are very similar but simply mirror images of each other. Both work, but many people (Myself included.) believe that using LEFT JOIN consistently makes working with your SQL easier and easier to understand.

Do notice though, that he has also given an illustration of what I said earlier in line #11. Note particularly that the use of Is Null is preferred within SQL to any function call such as IsNull([X]).

Otherwise, let us know if that has fixed your issue. I know you generally do so just to say it's appreciated :-)

[isladogs]

Just for the info of the OP, the solution @cactusdata provided in post #8 is exactly what you would have got by doing what I suggested in post #3