Hi
I have an append query which won’t add new records. I have two tables and I need the new data from TableA (FSP Attainment Import) to add a new record into TableB (Master), however I only need it to add the new data and ignore the old. For example if TableB holds (Row 1) A B C D (Row 2) B C D E (Row 3) C D E F and TableA holds (Row 1) A B C D (Row 2) B C D E (Row 3) C D E F (Row 4) D E F G (Row 5) E F G H. I only need the data from row 4 and 5 added to TableB. I have written an append query and when I run it its says 'about to update 10 Row's, which is wrong as there is only 1 new record in the file, and then comes up with another message saying 'DB cant append all records in append query'. Then when I press yes to ignore this file nothing new or old gets updated to TableB. Here’s the code - INSERT INTO Master ( Forename, Surname, UPN, [CLA School], [Local Authority], [DfE Number], Gender, DOB, [Year], DOA, PEP, Exclusions, Attendance, SEN, FSM, Ethnicity, [FSP PSE AS1], [FSP PSE AS2], [FSP PSE AS3], [FSP PSE AOL], [FSP CLL AS1], [FSP CLL AS2], [FSP CLL AS3], [FSP CLL AS4], [FSP CLL AOL], [FSP KUW AOL], [FSP PHY AOL], [FSP CRE AOL], [FSP TOT], [Achieved NI 72] )
-
SELECT [FSP Attainment Import].Forename, [FSP Attainment Import].Surname, [FSP Attainment Import].UPN, [FSP Attainment Import].[CLA SCHOOL], [FSP Attainment Import].[Local Authority], [FSP Attainment Import].[DfE Number], [FSP Attainment Import].Gender, [FSP Attainment Import].DOB, [FSP Attainment Import].Year, [FSP Attainment Import].DOA, [FSP Attainment Import].PEP, [FSP Attainment Import].Exclusions, [FSP Attainment Import].Attendance, [FSP Attainment Import].SEN, [FSP Attainment Import].FSM, [FSP Attainment Import].Ethnicity, [FSP Attainment Import].[FSP PSE AS1], [FSP Attainment Import].[FSP PSE AS2], [FSP Attainment Import].[FSP PSE AS3], [FSP Attainment Import].[FSP PSE AOL], [FSP Attainment Import].[FSP CLL AS1], [FSP Attainment Import].[FSP CLL AS2], [FSP Attainment Import].[FSP CLL AS3], [FSP Attainment Import].[FSP CLL AS4], [FSP Attainment Import].[FSP CLL AOL], [FSP Attainment Import].[FSP KUW AOL], [FSP Attainment Import].[FSP PHY AOL], [FSP Attainment Import].[FSP CRE AOL], [FSP Attainment Import].[FSP TOT], [FSP Attainment Import].[Achieved NI 72]
-
FROM [FSP Attainment Import] LEFT JOIN Master ON ([FSP Attainment Import].Forename = Master.Forename) AND ([FSP Attainment Import].[DfE Number] = Master.[DfE Number]) AND ([FSP Attainment Import].[Local Authority] = Master.[Local Authority]) AND ([FSP Attainment Import].[CLA SCHOOL] = Master.[CLA SCHOOL]) AND ([FSP Attainment Import].UPN = Master.UPN) AND ([FSP Attainment Import].Surname = Master.Surname) AND ([FSP Attainment Import].Gender = Master.Gender) AND ([FSP Attainment Import].DOB = Master.DOB) AND ([FSP Attainment Import].Year = Master.Year) AND ([FSP Attainment Import].DOA = Master.DOA) AND ([FSP Attainment Import].PEP = Master.PEP) AND ([FSP Attainment Import].Exclusions = Master.Exclusions) AND ([FSP Attainment Import].Attendance = Master.Attendance) AND ([FSP Attainment Import].SEN = Master.SEN) AND ([FSP Attainment Import].FSM = Master.FSM) AND ([FSP Attainment Import].Ethnicity = Master.Ethnicity) AND ([FSP Attainment Import].[FSP PSE AS1] = Master.[FSP PSE AS1]) AND ([FSP Attainment Import].[FSP PSE AS2] = Master.[FSP PSE AS2]) AND ([FSP Attainment Import].[FSP PSE AS3] = Master.[FSP PSE AS3]) AND ([FSP Attainment Import].[FSP PSE AOL] = Master.[FSP PSE AOL]) AND ([FSP Attainment Import].[FSP CLL AS1] = Master.[FSP CLL AS1]) AND ([FSP Attainment Import].[FSP CLL AS2] = Master.[FSP CLL AS2]) AND ([FSP Attainment Import].[FSP CLL AS3] = Master.[FSP CLL AS3]) AND ([FSP Attainment Import].[FSP CLL AS4] = Master.[FSP CLL AS4]) AND ([FSP Attainment Import].[FSP CLL AOL] = Master.[FSP CLL AOL]) AND ([FSP Attainment Import].[FSP KUW AOL] = Master.[FSP KUW AOL]) AND ([FSP Attainment Import].[FSP PHY AOL] = Master.[FSP PHY AOL]) AND ([FSP Attainment Import].[FSP CRE AOL] = Master.[FSP CRE AOL]) AND ([FSP Attainment Import].[FSP TOT] = Master.[FSP TOT]) AND ([FSP Attainment Import].[Achieved NI 72] = Master.[Achieved NI 72])
-
WHERE (((Master.Forename) Is Null) AND ((Master.Surname) Is Null) AND ((Master.UPN) Is Null) AND ((Master.[CLA SCHOOL]) Is Null) AND ((Master.[Local Authority]) Is Null) AND ((Master.[DfE Number]) Is Null) AND ((Master.Gender) Is Null) AND ((Master.DOB) Is Null) AND ((Master.Year) Is Null) AND ((Master.DOA) Is Null) AND ((Master.PEP) Is Null) AND ((Master.Exclusions) Is Null) AND ((Master.Attendance) Is Null) AND ((Master.SEN) Is Null) AND ((Master.Ethnicity) Is Null) AND ((Master.FSM) Is Null) AND ((Master.[FSP PSE AS1]) Is Null) AND ((Master.[FSP PSE AS2]) Is Null) AND ((Master.[FSP PSE AS3]) Is Null) AND ((Master.[FSP PSE AOL]) Is Null) AND ((Master.[FSP CLL AS1]) Is Null) AND ((Master.[FSP CLL AS2]) Is Null) AND ((Master.[FSP CLL AS3]) Is Null) AND ((Master.[FSP CLL AS4]) Is Null) AND ((Master.[FSP CLL AOL]) Is Null) AND ((Master.[FSP KUW AOL]) Is Null) AND ((Master.[FSP PHY AOL]) Is Null) AND ((Master.[FSP CRE AOL]) Is Null) AND ((Master.[FSP TOT]) Is Null) AND ((Master.[Achieved NI 72]) Is Null));
Can anyone help? I am fairly new to SQL
Thanks
Ste
3 1946 ck9663 2,878
Recognized Expert Specialist
First, try to create a primary key on your tables.
Second, use NOT EXISTS to check for existence of that key on your Master before you insert it.
Good Luck!!!
~~ CK
Would this mean I don’t have to have all the 'Is Nulls' in my code?
I have been trying to use the 'Not Exists' in the criteria of my Children ID (which I cant make a primary key because when the data comes back into the database the new pupils won't have had one applied yet and so it will be blank, however it is still my primary key in the 'Master' table) I keep getting this error:
'The expression you have entered contains invalid syntax'
However there are no commas or special characters in the query.
Any ideas?
Ste
Hi CK
I think I have figured out what I was doing wrong! Now it selects the one new pupil, which is great! However it is still not appending the new data to the 'Master' table. Any ideas? Here is the new code - INSERT INTO Master ( Forename, Surname, UPN, [CLA School], [Local Authority], [DfE Number], Gender, DOB, [Year], DOA, PEP, Exclusions, Attendance, SEN, FSM, Ethnicity, [FSP PSE AS1], [FSP PSE AS2], [FSP PSE AS3], [FSP PSE AOL], [FSP CLL AS1], [FSP CLL AS2], [FSP CLL AS3], [FSP CLL AS4], [FSP CLL AOL], [FSP KUW AOL], [FSP PHY AOL], [FSP CRE AOL], [FSP TOT], [Achieved NI 72] )
-
SELECT [FSP Attainment Import].Forename, [FSP Attainment Import].Surname, [FSP Attainment Import].UPN, [FSP Attainment Import].[CLA SCHOOL], [FSP Attainment Import].[Local Authority], [FSP Attainment Import].[DfE Number], [FSP Attainment Import].Gender, [FSP Attainment Import].DOB, [FSP Attainment Import].Year, [FSP Attainment Import].DOA, [FSP Attainment Import].PEP, [FSP Attainment Import].Exclusions, [FSP Attainment Import].Attendance, [FSP Attainment Import].SEN, [FSP Attainment Import].FSM, [FSP Attainment Import].Ethnicity, [FSP Attainment Import].[FSP PSE AS1], [FSP Attainment Import].[FSP PSE AS2], [FSP Attainment Import].[FSP PSE AS3], [FSP Attainment Import].[FSP PSE AOL], [FSP Attainment Import].[FSP CLL AS1], [FSP Attainment Import].[FSP CLL AS2], [FSP Attainment Import].[FSP CLL AS3], [FSP Attainment Import].[FSP CLL AS4], [FSP Attainment Import].[FSP CLL AOL], [FSP Attainment Import].[FSP KUW AOL], [FSP Attainment Import].[FSP PHY AOL], [FSP Attainment Import].[FSP CRE AOL], [FSP Attainment Import].[FSP TOT], [FSP Attainment Import].[Achieved NI 72]
-
FROM [FSP Attainment Import] LEFT JOIN Master ON [FSP Attainment Import].ChildLookedAfterID = Master.ChildLookedAfterID
-
WHERE (((Not Exists (SELECT * FROM [FSP Attainment Import] WHERE [FSP Attainment Import].ChildLookedAfterID = Master.ChildLookedAfterID))=True));
Thanks
Ste
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: JMCN |
last post by:
hi
i need some advice on whether if it would be better to use an append
query or an update query. here is the situation, i have linked another
database table to my current database. then i created a query for the
linked database but eventually i need to have this query to be
constantly updated and append to another table in the current
database. i hope i'm not too vague but i have no idea if i should use
an update or an append query....
|
by: JMCN |
last post by:
hi
i have a general question regarding append queries in access 97. each
week i need to update my table(tblonlinereg) with new or modified
records. firstly, i import the text file into my database and then i
create a basic append query that appends the new records to the
table(tblonlinereg). this works great if the field is
greater than the last record appended ( where: tempID > 198 ) but how
would i update/append the modifications...
|
by: |
last post by:
I found similiar issues in MS-KB but nothing that helped
me; got the Windows and Office updates from the MS website
but that hasn't changed the behavior of this problem, and
I don't see anything relevant in the newsgroup; help will
be gratefully received.
Windows 2000 system, I'm logged in with admin rights, using
Office 2003. I have two databases on my local drive, in
the same subdirectory. In the database HAMCHEESE, I create
an...
|
by: anita |
last post by:
I am sorry if this sounds as a silly problem.
I have table 1 with 10 records and table 2 with 20 records.
I want to append 20 records from Table 2 to table 1.
But when I run the append query, the 20 records gets appended below
each and every record in table 1, Which results in total of 10*20=200 records,
ideally it should be only 10 + 20 = 30 records.
Can anybody pls help me out with this problem.
Also can somebody suggest me a website...
|
by: pmacdiddie |
last post by:
I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.
The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.
| |
by: ielamrani |
last post by:
Hi,
An append query is giving me the following error:
qryPar can't append all the records in the append query
qryPar set 0 field(s) to null due to at type conversion failure, and it did not add 4626 records due to key violations, 0 records to lock violations, and 0 records due to validation rule violations.
Do you run the query....etc?
I could not see where the problem is. Please let me know if you have an answer. Thanks in advance.
...
|
by: hr833 |
last post by:
hi.. i'm using a append query to filter some new records that must be updated in the lookup table.
In this lookup table it consist of the part number and the type of the product. the part number is the primary key.
New records will be entered and the product type will be lookup through the part number in the lookup table. However to ensure that the user remembers to update the lookup table with the latest part number, i'll using an append...
|
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 not work in Access.
Why not just download the free SQL Server Express 2008 onto you PC and
try running your insert there? Then you can use Access for forms and
reports and also use all the latest SQL features of SQL Server.
|
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 the append button, open the table up and 12 records are sitting in there (three sets of the same record ID number and contents).
Do I need to set a "where" condition and if so, how do I set a "where" condition?
|
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 data spans across 3 related tables - scripts, tests and data. One script can have many tests, and one test can have many data entries. When the records are appended, they're also assigned new primary keys, which means the relationships needed to be...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |