Append Query Multiplies Records Thousands of Times? Access 2000

A

Andy C

I'm trying to append a table of 157 records to a much larger table in the
same D/B. In design view I matched the fields and set up as an append query.
When I run the query it duplicates each record 9271 times........... I've
tried designating a primary key, linking similar fields between the tables,
no change. I'm a new user so I'm not sure what is going on here.

Thanks for any help,
Andy C
 
J

Jeff Boyce

Andy

Post the SQL of your append query. It will help folks diagnose what might
be happening.

9271 duplicates?! that took a lot of counting... <g>

Jeff Boyce
<Office/Access MVP>
 
J

John Spencer

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
 
K

KARL DEWEY

Post the SQL for your append query.
Open the query in design view, click on menu VIEW - SQL View and copy.
Paste in a reply to this post.
 
A

Andy C

Hi Karl:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));
 
A

Andy C

Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));
 
A

Andy C

Jeff:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));
 
J

John Spencer

As I suspected you have no join between the AllProps Table and the Sep/Nov Sales
table. So you are getting a cross product of the tables. That is if there are
10 records in AllProps and 20 records in Sep/Nov Sales you are goint to get 200
(10*20) records returned.

I think that all you really need to do is remove the AllProps Table from the
Select statement since you are not really using it to identify any records.

INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name],
[Sep/Nov Sales].[Primary Owner Full Name],
[Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov Sales].[Mailing City],
[Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov Sales].[Square Footage],
[Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov Sales].[Number of Bedrooms],
[Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov Sales].[Number of Units],
[Sep/Nov Sales].[Sale Amount],
[Sep/Nov Sales].[Full or Partial],
[Sep/Nov Sales].[Sale Date],
[Sep/Nov Sales].[Sale Document Number],
[Sep/Nov Sales].[Multiple/Portion],
[Sep/Nov Sales].[Assessed Value],
[Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov Sales].APN,
[Sep/Nov Sales].[Year Built],
[Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [Sep/Nov Sales]
WHERE [Sep/Nov Sales].APN=8624008013

Andy said:
Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));

John Spencer said:
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
Click to expand...
Click to expand...
 
A

Andy C

John:
Thanks for the tip. Please excuse my ignorance but could you elaborate a
little on how I would remove the Allprops table form the select statement.
Unfortunately I have no in-house tech support here and as an infrequent user
some Access operations are "less than intuitive".
Regards,
Andy

John Spencer said:
As I suspected you have no join between the AllProps Table and the Sep/Nov Sales
table. So you are getting a cross product of the tables. That is if there are
10 records in AllProps and 20 records in Sep/Nov Sales you are goint to get 200
(10*20) records returned.

I think that all you really need to do is remove the AllProps Table from the
Select statement since you are not really using it to identify any records.

INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name],
[Sep/Nov Sales].[Primary Owner Full Name],
[Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov Sales].[Mailing City],
[Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov Sales].[Square Footage],
[Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov Sales].[Number of Bedrooms],
[Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov Sales].[Number of Units],
[Sep/Nov Sales].[Sale Amount],
[Sep/Nov Sales].[Full or Partial],
[Sep/Nov Sales].[Sale Date],
[Sep/Nov Sales].[Sale Document Number],
[Sep/Nov Sales].[Multiple/Portion],
[Sep/Nov Sales].[Assessed Value],
[Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov Sales].APN,
[Sep/Nov Sales].[Year Built],
[Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [Sep/Nov Sales]
WHERE [Sep/Nov Sales].APN=8624008013

Andy said:
Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));

John Spencer said:
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
I'm trying to append a table of 157 records to a much larger table in the
same D/B. In design view I matched the fields and set up as an append
query.
When I run the query it duplicates each record 9271 times........... I've
tried designating a primary key, linking similar fields between the
tables,
no change. I'm a new user so I'm not sure what is going on here.

Thanks for any help,
Andy C
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

The query I posted did that. Did you try the posted modification?

Try opening your query in design view and look for the line that reads

FROM [ALLPROPS Table], [Sep/Nov Sales]

Delete "[ALLPROPS Table]," from that line so you end up with

FROM [Sep/Nov Sales]

Now try running the query and see if that works.

Andy said:
John:
Thanks for the tip. Please excuse my ignorance but could you elaborate a
little on how I would remove the Allprops table form the select statement.
Unfortunately I have no in-house tech support here and as an infrequent user
some Access operations are "less than intuitive".
Regards,
Andy

John Spencer said:
As I suspected you have no join between the AllProps Table and the Sep/Nov Sales
table. So you are getting a cross product of the tables. That is if there are
10 records in AllProps and 20 records in Sep/Nov Sales you are goint to get 200
(10*20) records returned.

I think that all you really need to do is remove the AllProps Table from the
Select statement since you are not really using it to identify any records.

INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name],
[Sep/Nov Sales].[Primary Owner Full Name],
[Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov Sales].[Mailing City],
[Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov Sales].[Square Footage],
[Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov Sales].[Number of Bedrooms],
[Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov Sales].[Number of Units],
[Sep/Nov Sales].[Sale Amount],
[Sep/Nov Sales].[Full or Partial],
[Sep/Nov Sales].[Sale Date],
[Sep/Nov Sales].[Sale Document Number],
[Sep/Nov Sales].[Multiple/Portion],
[Sep/Nov Sales].[Assessed Value],
[Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov Sales].APN,
[Sep/Nov Sales].[Year Built],
[Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [Sep/Nov Sales]
WHERE [Sep/Nov Sales].APN=8624008013

Andy said:
Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));

:

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
I'm trying to append a table of 157 records to a much larger table in the
same D/B. In design view I matched the fields and set up as an append
query.
When I run the query it duplicates each record 9271 times........... I've
tried designating a primary key, linking similar fields between the
tables,
no change. I'm a new user so I'm not sure what is going on here.

Thanks for any help,
Andy C
Click to expand...
Click to expand...
Click to expand...
 
A

Andy C

It works!
Thanks so much for your help John. This thing has been frustrating me for
days.
Just so I'm clear, how should I avoid making the same error in the future?

Cheers,
Andy

John Spencer said:
The query I posted did that. Did you try the posted modification?

Try opening your query in design view and look for the line that reads

FROM [ALLPROPS Table], [Sep/Nov Sales]

Delete "[ALLPROPS Table]," from that line so you end up with

FROM [Sep/Nov Sales]

Now try running the query and see if that works.

Andy said:
John:
Thanks for the tip. Please excuse my ignorance but could you elaborate a
little on how I would remove the Allprops table form the select statement.
Unfortunately I have no in-house tech support here and as an infrequent user
some Access operations are "less than intuitive".
Regards,
Andy

John Spencer said:
As I suspected you have no join between the AllProps Table and the Sep/Nov Sales
table. So you are getting a cross product of the tables. That is if there are
10 records in AllProps and 20 records in Sep/Nov Sales you are goint to get 200
(10*20) records returned.

I think that all you really need to do is remove the AllProps Table from the
Select statement since you are not really using it to identify any records.

INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name],
[Sep/Nov Sales].[Primary Owner Full Name],
[Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov Sales].[Mailing City],
[Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov Sales].[Square Footage],
[Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov Sales].[Number of Bedrooms],
[Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov Sales].[Number of Units],
[Sep/Nov Sales].[Sale Amount],
[Sep/Nov Sales].[Full or Partial],
[Sep/Nov Sales].[Sale Date],
[Sep/Nov Sales].[Sale Document Number],
[Sep/Nov Sales].[Multiple/Portion],
[Sep/Nov Sales].[Assessed Value],
[Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov Sales].APN,
[Sep/Nov Sales].[Year Built],
[Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [Sep/Nov Sales]
WHERE [Sep/Nov Sales].APN=8624008013

Andy C wrote:

Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [# Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address], [Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code], [Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms], [Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms], [Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)], [Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));

:

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
I'm trying to append a table of 157 records to a much larger table in the
same D/B. In design view I matched the fields and set up as an append
query.
When I run the query it duplicates each record 9271 times........... I've
tried designating a primary key, linking similar fields between the
tables,
no change. I'm a new user so I'm not sure what is going on here.

Thanks for any help,
Andy C
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Normally you don't include the destination table (you are appending records
to) in the SELECT SQL you are appending from. So if you are using the grid,
in most cases you should not see the destination table in the top area of
the grid. There are exceptions to this such as you are going to use the
destination table as a means to filter the source table records.

One method to handle this with the query grid is to build a SELECT Query
that shows you the records you intend to import into the destination table.
When that works correctly, open the Select query in design view and change
its type (Menu: Query: Apppend query) to an APPEND query. When you do,
Access will ask you for the name of the destination table. At this point,
you can select the destination and define which fields get which data.

Andy C said:
It works!
Thanks so much for your help John. This thing has been frustrating me for
days.
Just so I'm clear, how should I avoid making the same error in the future?

Cheers,
Andy

John Spencer said:
The query I posted did that. Did you try the posted modification?

Try opening your query in design view and look for the line that reads

FROM [ALLPROPS Table], [Sep/Nov Sales]

Delete "[ALLPROPS Table]," from that line so you end up with

FROM [Sep/Nov Sales]

Now try running the query and see if that works.

Andy said:
John:
Thanks for the tip. Please excuse my ignorance but could you elaborate
a
little on how I would remove the Allprops table form the select
statement.
Unfortunately I have no in-house tech support here and as an infrequent
user
some Access operations are "less than intuitive".
Regards,
Andy

:

As I suspected you have no join between the AllProps Table and the
Sep/Nov Sales
table. So you are getting a cross product of the tables. That is if
there are
10 records in AllProps and 20 records in Sep/Nov Sales you are goint
to get 200
(10*20) records returned.

I think that all you really need to do is remove the AllProps Table
from the
Select statement since you are not really using it to identify any
records.

INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1], [MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [#
Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use
Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name],
[Sep/Nov Sales].[Primary Owner Full Name],
[Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov Sales].[Mailing City],
[Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov Sales].[Square Footage],
[Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov Sales].[Number of Bedrooms],
[Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov Sales].[Number of Units],
[Sep/Nov Sales].[Sale Amount],
[Sep/Nov Sales].[Full or Partial],
[Sep/Nov Sales].[Sale Date],
[Sep/Nov Sales].[Sale Document Number],
[Sep/Nov Sales].[Multiple/Portion],
[Sep/Nov Sales].[Assessed Value],
[Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov Sales].APN,
[Sep/Nov Sales].[Year Built],
[Sep/Nov Sales].[Use Code],
[Sep/Nov Sales].[Mailing State]
FROM [Sep/Nov Sales]
WHERE [Sep/Nov Sales].APN=8624008013

Andy C wrote:

Hi John:
Here's the SQL:
INSERT INTO [ALLPROPS Table] ( [Site Address], [Owner Names1],
[MStr
Address1], MCity, MZip, [Sq Ft], [Room Count], [# Bedrooms], [#
Bathrooms],
[# Units], [Sale Price], [Full/Partial], [Sale Date], [Document #],
[Mult/Port], [Assed Value], [Lot Size], [APN #], [Year Built], [Use
Code],
MState )
SELECT [Sep/Nov Sales].[Site Full Street Name], [Sep/Nov
Sales].[Primary
Owner Full Name], [Sep/Nov Sales].[Mailing Full Street Address],
[Sep/Nov
Sales].[Mailing City], [Sep/Nov Sales].[Mailing Full Zip Code],
[Sep/Nov
Sales].[Square Footage], [Sep/Nov Sales].[Total Number of Rooms],
[Sep/Nov
Sales].[Number of Bedrooms], [Sep/Nov Sales].[Number of Bathrooms],
[Sep/Nov
Sales].[Number of Units], [Sep/Nov Sales].[Sale Amount], [Sep/Nov
Sales].[Full or Partial], [Sep/Nov Sales].[Sale Date], [Sep/Nov
Sales].[Sale
Document Number], [Sep/Nov Sales].[Multiple/Portion], [Sep/Nov
Sales].[Assessed Value], [Sep/Nov Sales].[Lot Size (SqFt)],
[Sep/Nov
Sales].APN, [Sep/Nov Sales].[Year Built], [Sep/Nov Sales].[Use
Code],
[Sep/Nov Sales].[Mailing State]
FROM [ALLPROPS Table], [Sep/Nov Sales]
WHERE ((([Sep/Nov Sales].APN)=8624008013));

:

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It sounds as if you have missed a join in your query.
I'm trying to append a table of 157 records to a much larger
table in the
same D/B. In design view I matched the fields and set up as an
append
query.
When I run the query it duplicates each record 9271
times........... I've
tried designating a primary key, linking similar fields between
the
tables,
no change. I'm a new user so I'm not sure what is going on
here.

Thanks for any help,
Andy C
Click to expand...
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
Append only new records/entire record makes unique identifier 4
Append query without duplicate rows 5
Access 2002 Append Query Problems 2
Append Query is not working 1
Unable to append due to key violations 2
Append Query - key violation - no duplicates, records exist in par 4
appending new records to a table with a compound primary key 11

Top