469,906 Members | 2,224 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,906 developers. It's quick & easy.

Append Query trying not to add duplicate records to new table

anoble1
238 128KB
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblNotes ( [MODSERIAL#] )
  2. SELECT [Machine Hours].[MODSER#]
  3. FROM tblNotes RIGHT JOIN [Machine Hours] ON tblNotes.[MODSERIAL#] = [Machine Hours].[MODSER#]
  4. WHERE ((([Machine Hours].[MODSER#])<>[tblNotes].[MODSERIAL#]));
6 Days Ago #1
9 3011
NeoPa
32,231 Expert Mod 16PB
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 :-)
6 Days Ago #2
isladogs
315 Expert Mod 256MB
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?
6 Days Ago #3
anoble1
238 128KB
Hmm. Are you referring to line 3 on the equal sign?

@NeoPa
6 Days Ago #4
anoble1
238 128KB
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.
6 Days Ago #5
NeoPa
32,231 Expert Mod 16PB
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).
5 Days Ago #6
NeoPa
32,231 Expert Mod 16PB
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 :-)
5 Days Ago #7
cactusdata
188 Expert 128KB
Use a Left Join:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblNotes 
  2.     ( [MODSERIAL#] )
  3. SELECT 
  4.     [Machine Hours].[MODSER#]
  5. FROM 
  6.     tblNotes 
  7. LEFT JOIN 
  8.     [Machine Hours] 
  9.     ON tblNotes.[MODSERIAL#] = [Machine Hours].[MODSER#]
  10. WHERE
  11.     [tblNotes].[MODSERIAL#] Is Null;
4 Days Ago #8
NeoPa
32,231 Expert Mod 16PB
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 :-)
4 Days Ago #9
isladogs
315 Expert Mod 256MB
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
3 Days Ago #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.