Re: Append Query question!

#1 (**permalink**) 28-Jul-2006, 08:17 AM

I put that into the criteria area of my append query and got syntax errors
that I could not correct as I am new to this. Is there a misplaced
paranthesis somewhere?

"Brendan Reynolds" wrote:

> Here's an example that uses two copies of the Orders table from the
> Northwind database ...
>
> INSERT INTO Orders2 ( OrderID, CustomerID, EmployeeID, OrderDate,
> RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10984-re-append-query-question.html
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry )
> SELECT Orders1.OrderID, Orders1.CustomerID, Orders1.EmployeeID,
> Orders1.OrderDate, Orders1.RequiredDate, Orders1.ShippedDate,
> Orders1.ShipVia, Orders1.Freight, Orders1.ShipName, Orders1.ShipAddress,
> Orders1.ShipCity, Orders1.ShipRegion, Orders1.ShipPostalCode,
> Orders1.ShipCountry
> FROM Orders1 WHERE Orders1.OrderID NOT IN (SELECT Orders2.OrderID FROM
> Orders2);
>
> --
> Brendan Reynolds
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10984
> Access MVP
>
>
> "JOM" wrote in message
> news:13FAADA8-A28D-4634-9508-912681A4DF2D@microsoft.com...
> > Is it possible to do append query to unique records. As in If one forgets
> > and presses the append query more than once, there is a possiblility that
> > the
> > same records would be appended more that once. What I would like to do is
> > one runs the append query, don't append the same records or have duplicate
> > records in the appended table...
>
>
>

#2 (**permalink**) 28-Jul-2006, 08:17 AM

What exactly did you put in the criteria area of your query? In the example
I posted, the criteria is everything between 'WHERE' and the semi-colon,
exclusive, that is to say not including the word 'WHERE' or the semi-colon.
And of course you would need to change the table and field names to your
actual table and field names.

--
Brendan Reynolds
Access MVP

"Mark Cline" Cline@discussions.microsoft.com> wrote in message
news:F1C70878-1244-4CBF-A1BD-2AE72EB29B6F@microsoft.com...
>I put that into the criteria area of my append query and got syntax errors
> that I could not correct as I am new to this. Is there a misplaced
> paranthesis somewhere?
>
> "Brendan Reynolds" wrote:
>
>> Here's an example that uses two copies of the Orders table from the
>> Northwind database ...
>>
>> INSERT INTO Orders2 ( OrderID, CustomerID, EmployeeID, OrderDate,
>> RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
>> ShipCity, ShipRegion, ShipPostalCode, ShipCountry )
>> SELECT Orders1.OrderID, Orders1.CustomerID, Orders1.EmployeeID,
>> Orders1.OrderDate, Orders1.RequiredDate, Orders1.ShippedDate,
>> Orders1.ShipVia, Orders1.Freight, Orders1.ShipName, Orders1.ShipAddress,
>> Orders1.ShipCity, Orders1.ShipRegion, Orders1.ShipPostalCode,
>> Orders1.ShipCountry
>> FROM Orders1 WHERE Orders1.OrderID NOT IN (SELECT Orders2.OrderID FROM
>> Orders2);
>>
>> --
>> Brendan Reynolds
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10984
>> Access MVP
>>
>>
>> "JOM" wrote in message
>> news:13FAADA8-A28D-4634-9508-912681A4DF2D@microsoft.com...
>> > Is it possible to do append query to unique records. As in If one
>> > forgets
>> > and presses the append query more than once, there is a possiblility
>> > that
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10984
>> > the
>> > same records would be appended more that once. What I would like to do
>> > is
>> > one runs the append query, don't append the same records or have
>> > duplicate
>> > records in the appended table...
>>
>>
>>

#3 (**permalink**) 28-Jul-2006, 08:17 AM

Correction - if you were typing that into the criteria row in query design
view, you would also leave out the name of the column, so the criteria would
look like ...

NOT IN (SELECT Orders2.OrderID FROM Orders2)

.... not forgetting to replace 'Orders2' with the name of your target table
(the table to which you are appending) and 'OrderID' with the name of a
suitable column (the primary key column or another column with a unique
index).

--
Brendan Reynolds
Access MVP

"Brendan Reynolds" wrote in message
news:%23rfaU4uiGHA.456@TK2MSFTNGP05.phx.gbl...
> What exactly did you put in the criteria area of your query? In the
> example I posted, the criteria is everything between 'WHERE' and the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10984
> semi-colon, exclusive, that is to say not including the word 'WHERE' or
> the semi-colon. And of course you would need to change the table and field
> names to your actual table and field names.
>
> --
> Brendan Reynolds
> Access MVP
>
>
> "Mark Cline" Cline@discussions.microsoft.com> wrote in message
> news:F1C70878-1244-4CBF-A1BD-2AE72EB29B6F@microsoft.com...
>>I put that into the criteria area of my append query and got syntax errors
>> that I could not correct as I am new to this. Is there a misplaced
>> paranthesis somewhere?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10984
>>
>> "Brendan Reynolds" wrote:
>>
>>> Here's an example that uses two copies of the Orders table from the
>>> Northwind database ...
>>>
>>> INSERT INTO Orders2 ( OrderID, CustomerID, EmployeeID, OrderDate,
>>> RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
>>> ShipCity, ShipRegion, ShipPostalCode, ShipCountry )
>>> SELECT Orders1.OrderID, Orders1.CustomerID, Orders1.EmployeeID,
>>> Orders1.OrderDate, Orders1.RequiredDate, Orders1.ShippedDate,
>>> Orders1.ShipVia, Orders1.Freight, Orders1.ShipName, Orders1.ShipAddress,
>>> Orders1.ShipCity, Orders1.ShipRegion, Orders1.ShipPostalCode,
>>> Orders1.ShipCountry
>>> FROM Orders1 WHERE Orders1.OrderID NOT IN (SELECT Orders2.OrderID FROM
>>> Orders2);
>>>
>>> --
>>> Brendan Reynolds
>>> Access MVP
>>>
>>>
>>> "JOM" wrote in message
>>> news:13FAADA8-A28D-4634-9508-912681A4DF2D@microsoft.com...
>>> > Is it possible to do append query to unique records. As in If one
>>> > forgets
>>> > and presses the append query more than once, there is a possiblility
>>> > that
>>> > the
>>> > same records would be appended more that once. What I would like to
>>> > do is
>>> > one runs the append query, don't append the same records or have
>>> > duplicate
>>> > records in the appended table...
>>>
>>>
>>>
>
>