Append query

[foxygrandma]

This is a pretty simple question I believe. I am just running into a small problem.

I imported two Excel sheets into Access. They have the same fields and field types and I simply want to combine them into one. Actually just attach the second one to the bottom of the first one. I imagine it's a simple append query to get the job done, but for some reason every time I try it runs for a couple minutes and then says "Ran out of disk space." What do I do?

[nspader]

Please provide the method in which you are running it right now. It is possible it is in an endless loop. If you can provide the code youa re using, then we can all possibly help you further.

Thanks

Nick

[foxygrandma]

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 1HSearchResults , 2HSearchResults ;

[foxygrandma]

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 1HSearchResults , 2HSearchResults ;

If I try:

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 2HSearchResults ;

Then it throws a different error.

"Microsoft Access can't append all the records in the append query. It didn't add 49,576 record(s) to the table due to key violations. Do you want to add them anyway?"

I am trying to append around 51,000 records.

[FishVal]

If I try:

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 2HSearchResults ;

Then it throws a different error.

"Microsoft Access can't append all the records in the append query. It didn't add 49,576 record(s) to the table due to key violations. Do you want to add them anyway?"

I am trying to append around 51,000 records.

Hello.

[code=sql]
INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 1HSearchResults , 2HSearchResults ;
[/code]

The query above will try to insert cartesian product of both tables - no wonder this could exhaust resources.

The second query looks correct. The reason for the error is that you try to insert records containing values that are breaking table rules (unique indexes, constraints etc.). If you want to add all records, then you should change table rules. If not, then just do nothing - Access will add all valid data omitting the rest.

Regards,
Fish

[foxygrandma]

I figured it out. Had to remove the primary key by deleting the ID columns. Worked like a charm after that.