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 1939 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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |
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. ...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |