Append Queries in 2007

[HT]

I'm wondering if I am understanding something correctly...

Here's the situation:
I want to create an append query to append all fields of one table into
another. The fields in these tables match. I have criteria so search only a
certain date range of records, but whatever it finds, all of those fields
will append to this other table. and again, I am positive that the fields are
identical in the two tables.

When I try to do this, I get an error that it won't do this because the
INSERT INTO statement containts an unknown field name, because the field name
in the Append To: row is in square brackets.

I have been told that what this means is that all of my fields in my append
query need to have the fields reselected in the Append To: row so the square
brackets are removed. This makes no sense to me! When I choose the Append
Query, Access is the one that put the brackets aruond the fields in the first
place! now, I have 20 fields to work with here; do I really have to click in
each field of that Append To: row and reselect the field so it displays
without the brackets and allows me to append the records? Surely there is
something more automated than this, isn't there??!!

thanks so much for any insight -

 

[Beetle]

Can you post the SQL of the query?

 

[John Spencer MVP]

First question is do all the fields in the source table have the same name as
a field in destination table.

INSERT INTO TableA
SELECT * FROM TableB
WHERE SomeField Between #2009-01-01# and #2009-05-31#

If the field names are different then you will need to specifically list the
fields to be populated and the corresponding source fields.

The above query will update all the fields in TableA that have a matching
field in TableB. HOWEVER, there can be additional fields in TableA that don't
exist in TableB. If there is a field in TableB that does not exist in TableA,
you will get an error message.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[HT]

Here it is...

INSERT INTO [archive test] ( [Order ID], [Employee ID], [Customer ID],
[Order Date], [Shipped Date], [Shipper ID], [Ship Name], [Ship Address],
[Ship City], [Ship State/Province], [Ship ZIP/Postal Code], [Ship
Country/Region], [Shipping Fee], Taxes, [Payment Type], [Paid Date], Notes,
[Tax Rate], [Tax Status], [Status ID] )
SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID],
Orders.[Order Date], Orders.[Shipped Date], Orders.[Shipper ID], Orders.[Ship
Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship
State/Province], Orders.[Ship ZIP/Postal Code], Orders.[Ship Country/Region],
Orders.[Shipping Fee], Orders.Taxes, Orders.[Payment Type], Orders.[Paid
Date], Orders.Notes, Orders.[Tax Rate], Orders.[Tax Status], Orders.[Status
ID]
FROM Orders
WHERE (((Orders.[Order Date]) Between #4/1/2006# And #4/30/2006#));


thanks -
Heather

 

[HT]

Hi John,

I am absolutely positive that they are. Here's what I did from beginning to
finish. (I am experimenting in a practice database.)

First I made a make table query. Take all fields from the Orders table and
locate all records (e.g.) between 1-1-06 and 1-31-06, make a new table called
Archive Test and copy those records in. I ran the query and it did indeed
create this new table and copied those records in.

I kept the query open after running it and looking at the new table to see
if it worked.

then I close the new table and returned to my Make Table query. From there,
I changed the query to an Append Query so I could use the same example but
this time take (again) all of those same fields from that same Orders table
and now append some new records to my Arhive Test table. The new records had
an Order Date criteria of Between 2-1-06 And 2-28-06. When I VIEW the
results, it shows me the new records it finds. But when I click to RUN the
append query, I get the error message. But do you see my confusion? it's the
same example; all I did was change the date criteria and make it an Append
query.

But when I change it to an Append query type, all of the fields in the
Append To: row change to have square brackets and that's where the problem
seems to be. If I remove all of those square brackets (pain...) then the
query works. So it seems like the problem is the square brackets, but why
would Access add those to the field names when I switch to an Append query if
it doesn't end up wanting them to be there?

Thanks for your help -
Heather

 

[John Spencer MVP]

Actually, Access requires the square brackets since you field names have
spaces in them. So that should not make the query fail. And looking at your
posted query, you should be able to use the following query to get the result
you are looking for

INSERT INTO [archive test]
SELECT *
FROM Orders
WHERE Orders.[Order Date] Between #4/1/2006# And #4/30/2006#

If that fails then there is something else going on. I don't have any idea
what that is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County