Append Query Assistance

L

Lori F.

I have to update a database periodically. A table is
converted to an Excel spreadsheet, the spreadsheet is
divided into parts to email to their authors for updating,
the updated responses come back to me, I combine the
spreadsheets into one, convert it back to Access, append
it to the original table, and delete the old records. NBD.

I have one late spreadsheet that I'm trying to get into
the Access table. I have tried copying and pasting, paste
appending, and finally converting the 4 rows to a new
table in the same database as the original. However, I
cannot merge the two tables. The structures are exactly
the same and there is no autonumbering or calculating in
any of the fields. It's all straight text and numbers. I
have tried copying and pasting the 4 records from the
smaller table into the larger one (as a group and one at a
time) and paste appending (it ended up pasting records
that were copied weeks ago).

I finally tried an append query. But, when I'm about to
run the query, it says it's going to append 100 records
and I can't figure out why. Main table has 25 records.

Anyone have any ideas?

Thanks,
Lori
 
S

Steve Schapel

Lori,

Hmmm. 25*4=100. Can you post back with copy/paste of the SQL view
(Select SQL from the View menu in query design) of the Append Query you
are trying to use, and someone may be able to spot the problem.
 
J

John Vinson

I finally tried an append query. But, when I'm about to
run the query, it says it's going to append 100 records
and I can't figure out why. Main table has 25 records.
Click to expand...

Please post the SQL view of the query.
 
L

Lori F.

:) Okay, so I was a Lit Major...

Here's the SQL code:

INSERT INTO [Progress Report] ( [Match], Capability,
[Capability Component], [Action (# / description)], [Lead
Agency], [Collaborative Agencies], POC, [Date of last WG
meeting], [Date of next WG meeting], [Milestone 1], [MS1
Date], [MS1 Status], [MS1 Comments], MS2, [MS2 Date], [MS2
Status], [MS2 Comments], MS3, [MS3 Date], [MS3 Status],
[MS3 Comments], MS4, [MS4 Date], [MS4 Status], [MS4
Comments], MS5, [MS5 Date], [MS5 Status], [MS5 Comments],
MS6, [MS6 Date], [MS6 Status], [MS6 Comments], MS7, [MS7
Date], [MS7 Status], [MS7 Comments], MS8, [MS8 Date], [MS8
Status], [MS8 Comments], MS9, [MS9 Date], [MS9 Status],
[MS9 Comments], MS10, [MS10 Date], [MS10 Status], [MS10
Comments], MS11, [MS11 Date], [MS11 Status], [MS11
Comments], MS12, [MS12 Date], [MS12 Status], [MS12
Comments], [IOC Date], [IOC Status], [FOC Date], [FOC
Status], [IOC & FOC Comments], [Funding Allocated],
[Funding Expended], [Funding Status], [Funding Comments],
[Overall Rating], [Lowest Rating] )
SELECT heineman.Match, heineman.Capability, heineman.
[Capability Component], heineman.[Action (# /
description)], heineman.[Lead Agency], heineman.
[Collaborative Agencies], heineman.POC, heineman.[Date of
last WG meeting], heineman.[Date of next WG meeting],
heineman.[Milestone 1], heineman.[MS1 Date], heineman.[MS1
Status], heineman.[MS1 Comments], heineman.MS2, heineman.
[MS2 Date], heineman.[MS2 Status], heineman.[MS2
Comments], heineman.MS3, heineman.[MS3 Date], heineman.
[MS3 Status], heineman.[MS3 Comments], heineman.MS4,
heineman.[MS4 Date], heineman.[MS4 Status], heineman.[MS4
Comments], heineman.MS5, heineman.[MS5 Date], heineman.
[MS5 Status], heineman.[MS5 Comments], heineman.MS6,
heineman.[MS6 Date], heineman.[MS6 Status], heineman.[MS6
Comments], heineman.MS7, heineman.[MS7 Date], heineman.
[MS7 Status], heineman.[MS7 Comments], heineman.MS8,
heineman.[MS8 Date], heineman.[MS8 Status], heineman.[MS8
Comments], heineman.MS9, heineman.[MS9 Date], heineman.
[MS9 Status], heineman.[MS9 Comments], heineman.MS10,
heineman.[MS10 Date], heineman.[MS10 Status], heineman.
[MS10 Comments], heineman.MS11, heineman.[MS11 Date],
heineman.[MS11 Status], heineman.[MS11 Comments],
heineman.MS12, heineman.[MS12 Date], heineman.[MS12
Status], heineman.[MS12 Comments], heineman.[IOC Date],
heineman.[IOC Status], heineman.[FOC Date], heineman.[FOC
Status], heineman.[IOC & FOC Comments], heineman.[Funding
Allocated], heineman.[Funding Expended], heineman.[Funding
Status], heineman.[Funding Comments], heineman.[Overall
Rating], heineman.[Lowest Rating]
FROM heineman, [Progress Report]
WHERE (((heineman.Capability)="JNTC"));


FYI - Progress Report is the name of the Main table and
Heineman is the name of the table with the 4 records.

Thanks for your help!

Lori
 
L

Lori F.

John,

Here's the SQL:

(Progress Report is the name of the main table (25
records) and Heineman is the name of the smaller table (4
records)).

INSERT INTO [Progress Report] ( [Match], Capability,
[Capability Component], [Action (# / description)], [Lead
Agency], [Collaborative Agencies], POC, [Date of last WG
meeting], [Date of next WG meeting], [Milestone 1], [MS1
Date], [MS1 Status], [MS1 Comments], MS2, [MS2 Date], [MS2
Status], [MS2 Comments], MS3, [MS3 Date], [MS3 Status],
[MS3 Comments], MS4, [MS4 Date], [MS4 Status], [MS4
Comments], MS5, [MS5 Date], [MS5 Status], [MS5 Comments],
MS6, [MS6 Date], [MS6 Status], [MS6 Comments], MS7, [MS7
Date], [MS7 Status], [MS7 Comments], MS8, [MS8 Date], [MS8
Status], [MS8 Comments], MS9, [MS9 Date], [MS9 Status],
[MS9 Comments], MS10, [MS10 Date], [MS10 Status], [MS10
Comments], MS11, [MS11 Date], [MS11 Status], [MS11
Comments], MS12, [MS12 Date], [MS12 Status], [MS12
Comments], [IOC Date], [IOC Status], [FOC Date], [FOC
Status], [IOC & FOC Comments], [Funding Allocated],
[Funding Expended], [Funding Status], [Funding Comments],
[Overall Rating], [Lowest Rating] )
SELECT heineman.Match, heineman.Capability, heineman.
[Capability Component], heineman.[Action (# /
description)], heineman.[Lead Agency], heineman.
[Collaborative Agencies], heineman.POC, heineman.[Date of
last WG meeting], heineman.[Date of next WG meeting],
heineman.[Milestone 1], heineman.[MS1 Date], heineman.[MS1
Status], heineman.[MS1 Comments], heineman.MS2, heineman.
[MS2 Date], heineman.[MS2 Status], heineman.[MS2
Comments], heineman.MS3, heineman.[MS3 Date], heineman.
[MS3 Status], heineman.[MS3 Comments], heineman.MS4,
heineman.[MS4 Date], heineman.[MS4 Status], heineman.[MS4
Comments], heineman.MS5, heineman.[MS5 Date], heineman.
[MS5 Status], heineman.[MS5 Comments], heineman.MS6,
heineman.[MS6 Date], heineman.[MS6 Status], heineman.[MS6
Comments], heineman.MS7, heineman.[MS7 Date], heineman.
[MS7 Status], heineman.[MS7 Comments], heineman.MS8,
heineman.[MS8 Date], heineman.[MS8 Status], heineman.[MS8
Comments], heineman.MS9, heineman.[MS9 Date], heineman.
[MS9 Status], heineman.[MS9 Comments], heineman.MS10,
heineman.[MS10 Date], heineman.[MS10 Status], heineman.
[MS10 Comments], heineman.MS11, heineman.[MS11 Date],
heineman.[MS11 Status], heineman.[MS11 Comments],
heineman.MS12, heineman.[MS12 Date], heineman.[MS12
Status], heineman.[MS12 Comments], heineman.[IOC Date],
heineman.[IOC Status], heineman.[FOC Date], heineman.[FOC
Status], heineman.[IOC & FOC Comments], heineman.[Funding
Allocated], heineman.[Funding Expended], heineman.[Funding
Status], heineman.[Funding Comments], heineman.[Overall
Rating], heineman.[Lowest Rating]
FROM heineman, [Progress Report]
WHERE (((heineman.Capability)="JNTC"));
 
S

Steve Schapel

Lori,

Change the bit that says...
FROM heineman, [Progress Report]
.... to...
FROM heineman

--
Steve Schapel, Microsoft Access MVP

:) Okay, so I was a Lit Major...

Here's the SQL code:

INSERT INTO [Progress Report] ( [Match], Capability,
[Capability Component], [Action (# / description)], [Lead
Agency], [Collaborative Agencies], POC, [Date of last WG
meeting], [Date of next WG meeting], [Milestone 1], [MS1
Date], [MS1 Status], [MS1 Comments], MS2, [MS2 Date], [MS2
Status], [MS2 Comments], MS3, [MS3 Date], [MS3 Status],
[MS3 Comments], MS4, [MS4 Date], [MS4 Status], [MS4
Comments], MS5, [MS5 Date], [MS5 Status], [MS5 Comments],
MS6, [MS6 Date], [MS6 Status], [MS6 Comments], MS7, [MS7
Date], [MS7 Status], [MS7 Comments], MS8, [MS8 Date], [MS8
Status], [MS8 Comments], MS9, [MS9 Date], [MS9 Status],
[MS9 Comments], MS10, [MS10 Date], [MS10 Status], [MS10
Comments], MS11, [MS11 Date], [MS11 Status], [MS11
Comments], MS12, [MS12 Date], [MS12 Status], [MS12
Comments], [IOC Date], [IOC Status], [FOC Date], [FOC
Status], [IOC & FOC Comments], [Funding Allocated],
[Funding Expended], [Funding Status], [Funding Comments],
[Overall Rating], [Lowest Rating] )
SELECT heineman.Match, heineman.Capability, heineman.
[Capability Component], heineman.[Action (# /
description)], heineman.[Lead Agency], heineman.
[Collaborative Agencies], heineman.POC, heineman.[Date of
last WG meeting], heineman.[Date of next WG meeting],
heineman.[Milestone 1], heineman.[MS1 Date], heineman.[MS1
Status], heineman.[MS1 Comments], heineman.MS2, heineman.
[MS2 Date], heineman.[MS2 Status], heineman.[MS2
Comments], heineman.MS3, heineman.[MS3 Date], heineman.
[MS3 Status], heineman.[MS3 Comments], heineman.MS4,
heineman.[MS4 Date], heineman.[MS4 Status], heineman.[MS4
Comments], heineman.MS5, heineman.[MS5 Date], heineman.
[MS5 Status], heineman.[MS5 Comments], heineman.MS6,
heineman.[MS6 Date], heineman.[MS6 Status], heineman.[MS6
Comments], heineman.MS7, heineman.[MS7 Date], heineman.
[MS7 Status], heineman.[MS7 Comments], heineman.MS8,
heineman.[MS8 Date], heineman.[MS8 Status], heineman.[MS8
Comments], heineman.MS9, heineman.[MS9 Date], heineman.
[MS9 Status], heineman.[MS9 Comments], heineman.MS10,
heineman.[MS10 Date], heineman.[MS10 Status], heineman.
[MS10 Comments], heineman.MS11, heineman.[MS11 Date],
heineman.[MS11 Status], heineman.[MS11 Comments],
heineman.MS12, heineman.[MS12 Date], heineman.[MS12
Status], heineman.[MS12 Comments], heineman.[IOC Date],
heineman.[IOC Status], heineman.[FOC Date], heineman.[FOC
Status], heineman.[IOC & FOC Comments], heineman.[Funding
Allocated], heineman.[Funding Expended], heineman.[Funding
Status], heineman.[Funding Comments], heineman.[Overall
Rating], heineman.[Lowest Rating]
FROM heineman, [Progress Report]
WHERE (((heineman.Capability)="JNTC"));


FYI - Progress Report is the name of the Main table and
Heineman is the name of the table with the 4 records.

Thanks for your help!

Lori
Click to expand...
 
J

John Vinson

FROM heineman, [Progress Report]
Click to expand...

That's the problem.

You want to append records *from heineman*. You're appending records
from a query joining heineman to Progress Report. Just remove Progress
Report from the query - it will be there *only* as the target of the
Append. If you edit this line of the SQL to

FROM heineman

you'll be fine.
 
L

Lori

John,

You were absolutely right - worked like a charm. Thank you
so much for your help!

Lori
-----Original Message-----
FROM heineman, [Progress Report]
Click to expand...

That's the problem.

You want to append records *from heineman*. You're appending records
from a query joining heineman to Progress Report. Just remove Progress
Report from the query - it will be there *only* as the target of the
Append. If you edit this line of the SQL to

FROM heineman

you'll be fine.


.
Click to expand...
 
L

Lori F.

Steve,

You were absolutely right - it worked perfectly!

Thank you so much for your help!

Lori
-----Original Message-----
Lori,

Change the bit that says...
FROM heineman, [Progress Report]
.... to...
FROM heineman

--
Steve Schapel, Microsoft Access MVP

:) Okay, so I was a Lit Major...

Here's the SQL code:

INSERT INTO [Progress Report] ( [Match], Capability,
[Capability Component], [Action (# / description)], [Lead
Agency], [Collaborative Agencies], POC, [Date of last WG
meeting], [Date of next WG meeting], [Milestone 1], [MS1
Date], [MS1 Status], [MS1 Comments], MS2, [MS2 Date], [MS2
Status], [MS2 Comments], MS3, [MS3 Date], [MS3 Status],
[MS3 Comments], MS4, [MS4 Date], [MS4 Status], [MS4
Comments], MS5, [MS5 Date], [MS5 Status], [MS5 Comments],
MS6, [MS6 Date], [MS6 Status], [MS6 Comments], MS7, [MS7
Date], [MS7 Status], [MS7 Comments], MS8, [MS8 Date], [MS8
Status], [MS8 Comments], MS9, [MS9 Date], [MS9 Status],
[MS9 Comments], MS10, [MS10 Date], [MS10 Status], [MS10
Comments], MS11, [MS11 Date], [MS11 Status], [MS11
Comments], MS12, [MS12 Date], [MS12 Status], [MS12
Comments], [IOC Date], [IOC Status], [FOC Date], [FOC
Status], [IOC & FOC Comments], [Funding Allocated],
[Funding Expended], [Funding Status], [Funding Comments],
[Overall Rating], [Lowest Rating] )
SELECT heineman.Match, heineman.Capability, heineman.
[Capability Component], heineman.[Action (# /
description)], heineman.[Lead Agency], heineman.
[Collaborative Agencies], heineman.POC, heineman.[Date of
last WG meeting], heineman.[Date of next WG meeting],
heineman.[Milestone 1], heineman.[MS1 Date], heineman. [MS1
Status], heineman.[MS1 Comments], heineman.MS2, heineman.
[MS2 Date], heineman.[MS2 Status], heineman.[MS2
Comments], heineman.MS3, heineman.[MS3 Date], heineman.
[MS3 Status], heineman.[MS3 Comments], heineman.MS4,
heineman.[MS4 Date], heineman.[MS4 Status], heineman. [MS4
Comments], heineman.MS5, heineman.[MS5 Date], heineman.
[MS5 Status], heineman.[MS5 Comments], heineman.MS6,
heineman.[MS6 Date], heineman.[MS6 Status], heineman. [MS6
Comments], heineman.MS7, heineman.[MS7 Date], heineman.
[MS7 Status], heineman.[MS7 Comments], heineman.MS8,
heineman.[MS8 Date], heineman.[MS8 Status], heineman. [MS8
Comments], heineman.MS9, heineman.[MS9 Date], heineman.
[MS9 Status], heineman.[MS9 Comments], heineman.MS10,
heineman.[MS10 Date], heineman.[MS10 Status], heineman.
[MS10 Comments], heineman.MS11, heineman.[MS11 Date],
heineman.[MS11 Status], heineman.[MS11 Comments],
heineman.MS12, heineman.[MS12 Date], heineman.[MS12
Status], heineman.[MS12 Comments], heineman.[IOC Date],
heineman.[IOC Status], heineman.[FOC Date], heineman. [FOC
Status], heineman.[IOC & FOC Comments], heineman. [Funding
Allocated], heineman.[Funding Expended], heineman. [Funding
Status], heineman.[Funding Comments], heineman.[Overall
Rating], heineman.[Lowest Rating]
FROM heineman, [Progress Report]
WHERE (((heineman.Capability)="JNTC"));


FYI - Progress Report is the name of the Main table and
Heineman is the name of the table with the 4 records.

Thanks for your help!

Lori
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
Adding data to MS Access from csv file 0
Embed the same Excel multiple times 0
Make one final list from four sources 1
How to paste this down? 3
Invalid Argument - Append Query 1
Append Query is not working 1
Preventing duplicate entries with an Append Query 3

Top