472,119 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Append Query trying not to add duplicate records to new table

244 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#]));
Jan 13 '22 #1
9 13628
32,497 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 :-)
Jan 13 '22 #2
409 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?
Jan 13 '22 #3
244 128KB
Hmm. Are you referring to line 3 on the equal sign?

Jan 13 '22 #4
244 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.
Jan 13 '22 #5
32,497 Expert Mod 16PB
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).
Jan 14 '22 #6
32,497 Expert Mod 16PB
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 :-)
Jan 14 '22 #7
202 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 
  8.     [Machine Hours] 
  9.     ON tblNotes.[MODSERIAL#] = [Machine Hours].[MODSER#]
  10. WHERE
  11.     [tblNotes].[MODSERIAL#] Is Null;
Jan 15 '22 #8
32,497 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 :-)
Jan 15 '22 #9
409 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
Jan 16 '22 #10

Post your reply

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

Similar topics

reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.