473,395 Members | 1,688 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Append Query trying not to add duplicate records to new table

245 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 13959
32,556 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
455 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
245 128KB
Hmm. Are you referring to line 3 on the equal sign?

Jan 13 '22 #4
245 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,556 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,556 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
214 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,556 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
455 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

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

Similar topics

by: shahjapan | last post by:
create table temp(comp_id int,branch_id int); insert into temp values (1000,1); insert into temp values (1000,2); insert into temp values (1000,3); insert into temp values (1000,1); insert into...
by: jennwilson | last post by:
Using Access 2000 - I have a query that is suppose to return the records from table within specified time range and find matching data from another table . Table houses Clinician name, location...
by: Michael Gramelspacher | last post by:
In article <b30a2278-d696-4f99-8bf5-9754110c7b03 @x8g2000yqk.googlegroups.com>, john.marruffo@illinois.gov says... What book are you learning from? SQL that might work for SQL Server 2008 will...
by: David Shears | last post by:
I am trying to use an append query to add data to another table i have. I have tableA and tableB. tableA has 1000 rows while tableB has 800 rows. i would like to add the other 200 rows in tableA to...
by: Fspinelli | last post by:
I have an append query which takes data from a query and updates a table. However, instead of one record I get 3 or 4 of the same records dumped into the table. I might enter three records, press...
by: Adam Tippelt | last post by:
Situation: When a user 'confirms' that they want to save the information they've inputted, I need to append the database records from a temporary table to a different table. Problem: The...
by: ricardusmaximus | last post by:
Hi guys. I am using an Append Query to add records to an Archive Table in an MS Access Application. The code generates 2 records for each one identified. strSQLAppend(1) = "INSERT INTO...
by: prose01 | last post by:
I was able to create a multiple field unique index for the EI&Fees table, restricting to unique values for FacilityId, EmissionInvYear, and FeeYear; however, can you show me how to apply the same...
by: Berard | last post by:
I have created two tables: One is called PARENT and the other CHILDREN. I have created a one to many relationship meaning one parent can have many children. When I create a query the parent name...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.