Error running append query?

[Guest]

I am trying to write a query to append a set of 10 fields to a table when the
Title field in both tables is the same. I was originally trying to use VBA
to do this, but it seemed easier to just use a query.

I think I have it about figured out, however when I try to run it I get a
dialog box asking me for EntireSpreadsheet_local.Title. The idea is not to
use this as a parameter, but to have the query check the values (text
strings) in this field against the same field in another table to see if they
are equal. I would like this operation to be case-insensitive if at all
possible, although I'm not sure how to accomplish this with a SQL statement.

If anyone has any idea why I am getting this error, I would appreciate it.

My code is below:

INSERT INTO EntireSpreadsheet_local ( EntireSpreadsheet_local.ManuscriptRec,
EntireSpreadsheet_local.Method, EntireSpreadsheet_local.Accepted,
EntireSpreadsheet_local.Owner, EntireSpreadsheet_local.[Contract Returned],
EntireSpreadsheet_local.AcqProvisions, EntireSpreadsheet_local.[1st Book
Release date], EntireSpreadsheet_local.[Query Received],
EntireSpreadsheet_local.AcqNotes, EntireSpreadsheet_local.[W-9 requested] )

SELECT QueriesACC.ManuscriptRec, QueriesACC.Method, QueriesACC.Accepted,
QueriesACC.Owner, QueriesACC.[Contract Returned], QueriesACC.AcqProvisions,
QueriesACC.[1st Book Release date], QueriesACC.[Query Received],
QueriesACC.AcqNotes, QueriesACC.[W-9 requested]

FROM QueriesACC
WHERE QueriesACC.Title = EntireSpreadsheet_local.Title OR QueriesACC.Title =
(EntireSpreadsheet_local.Title & ": " & EntireSpreadsheet_local.Subtitle) OR
QueriesACC.Title = (EntireSpreadsheet_local.Title & " : " &
EntireSpreadsheet_local.Subtitle);

Thanks,

Chris

 

[Guest]

Ok, I figured out that I was getting that error because I didn't have
EntireSpreadsheet_local in the FROM statement.

I am now getting an error saying that the INSERT INTO statement contains the
following unknown field name: "EntireSpreadsheet_local.ManuscriptRec". I
checked to make sure that this field was spelled correctly in the table, and
sure enough it was.

Any ideas?

 

[TC]

Show us the query.

TC

 

[John Vinson]

I am trying to write a query to append a set of 10 fields to a table when the
Title field in both tables is the same. I was originally trying to use VBA
to do this, but it seemed easier to just use a query.

What puzzles me about this is that - even after you join the two
tables - it seems you're appending records into
EntireSpreadsheet_local only if they already exist in
EntireSpreadsheet_local. Is that your intent?

John W. Vinson[MVP]

 

[Guest]

Well, sort of. What is happening is that I have 2 tables which are
effectively the same table, except for these 10 columns which I need to
append. So, in a sense I'm trying to merge the tables, which is why I'm only
appending data to rows where data already exists in EntireSpreadsheet_local.
I know Excel has a merge function, but I don't think I can really use it for
this, although I could be wrong.

Perhaps what's getting you confused is the fact that I created these 10
columns in EntireSpreadsheet_local, despite the fact that they only have
values in QueriesACC. That may not have been necessary, but I didn't know
how else to tell the query where to put the data that was being appended.


Maybe using an append query isn't the right way to do this, as I'm trying to
add data to the ends of the rows (trying to append columns), as opposed to
appending entirely new rows.

If you guys know of a better way to do this than using an append query, I'd
love to hear it because I'm not really sure of the best way.

TC - the code for my query is in the original post. The only change is that
my FROM statement now reads FROM QueriesACC, EntireSpreadsheet_local, instead
of just FROM QueriesACC.

Thanks,

Chris

 

[Guest]

Ok, I think I figured it out. I changed the query from an APPEND query to an
UPDATE query and it ran without error (almost). The only problem now is that
it ran out of temporary disk space when I tried to run it.

I can probably figure out how to increase the temporary disk space to allow
it to complete the query, although if anyone knows please feel free to post.

Thanks again,

Chris

 

[John Vinson]

Maybe using an append query isn't the right way to do this, as I'm trying to
add data to the ends of the rows (trying to append columns), as opposed to
appending entirely new rows.

If you guys know of a better way to do this than using an append query, I'd
love to hear it because I'm not really sure of the best way.

You're right - it isn't.

The purpose of an Append query is to add new records to an existing
table. You want to change the content of existing records.

An Update query is the appropriate technique. Join the two tables;
update each blank field to [QueriesACC].[fieldname] (including the
square brackets - if you leave them off it will assume you want to
update to the text string "QueriesACC.fieldname").

John W. Vinson[MVP]

 

[John Vinson]

Ok, I think I figured it out. I changed the query from an APPEND query to an
UPDATE query and it ran without error (almost). The only problem now is that
it ran out of temporary disk space when I tried to run it.

Please post the SQL of this query. That shouldn't be happening!

John W. Vinson[MVP]

 

[Guest]

John, I think the problem was that I had inadvertently set the WHERE
statement to read WHERE EntireSpreadsheet_local.Title =
EntireSpreadsheet_local.Title, which will ALWAYS be true. I changed it to
read WHERE QueriesACC.Title = EntireSpreadsheet_local.Title.

I think it should work, however now when I go to import a file into Access
from Excel my CPU usage jumps to 100% and after a while I get the message
that the program is not responding. I'm not sure if it's in any way related,
but I can't really think of anything else that would be causing it.

Btw my code now looks like this:

UPDATE QueriesACC, EntireSpreadsheet_local SET
EntireSpreadsheet_local.ManuscriptRec = [QueriesACC].[ManuscriptRec],
EntireSpreadsheet_local.Accepted = [QueriesACC].[Accepted],
EntireSpreadsheet_local.Owner = [QueriesACC].[Owner],
EntireSpreadsheet_local.ContractReturned = [QueriesACC].[ContractReturned],
EntireSpreadsheet_local.AcqProvisions = [QueriesACC].[AcqProvisions],
EntireSpreadsheet_local.FirstBookReleaseDate =
[QueriesACC].[FirstBookReleaseDate], EntireSpreadsheet_local.QueryReceived =
[QueriesACC].[QueryReceived], EntireSpreadsheet_local.Followup =
[QueriesACC].[Followup], EntireSpreadsheet_local.AcqNotes =
[QueriesACC].[AcqNotes], EntireSpreadsheet_local.[W-9Requested] =
[QueriesACC].[W-9Requested], EntireSpreadsheet_local.Method =
[QueriesACC].[Method]

WHERE ((([QueriesACC].[Title])=[EntireSpreadsheet_local].[Title] Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & ": " &
[EntireSpreadsheet_local].[Subtitle]) Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & " : " &
[EntireSpreadsheet_local].[Subtitle])));

Any ideas ?

 

[Guest]

It seems that the (not responding) error only happens with one particular
Excel file which uses calculated columns. This is still somewhat of a
problem, however, as using calculated columns is so far the only way I know
of getting data into Access without import errors, as Access has a nasty
habit of changing data types on you even if you specifically declare them in
Excel.

John, I think the problem was that I had inadvertently set the WHERE
statement to read WHERE EntireSpreadsheet_local.Title =
EntireSpreadsheet_local.Title, which will ALWAYS be true. I changed it to
read WHERE QueriesACC.Title = EntireSpreadsheet_local.Title.

I think it should work, however now when I go to import a file into Access
from Excel my CPU usage jumps to 100% and after a while I get the message
that the program is not responding. I'm not sure if it's in any way related,
but I can't really think of anything else that would be causing it.

Btw my code now looks like this:

UPDATE QueriesACC, EntireSpreadsheet_local SET
EntireSpreadsheet_local.ManuscriptRec = [QueriesACC].[ManuscriptRec],
EntireSpreadsheet_local.Accepted = [QueriesACC].[Accepted],
EntireSpreadsheet_local.Owner = [QueriesACC].[Owner],
EntireSpreadsheet_local.ContractReturned = [QueriesACC].[ContractReturned],
EntireSpreadsheet_local.AcqProvisions = [QueriesACC].[AcqProvisions],
EntireSpreadsheet_local.FirstBookReleaseDate =
[QueriesACC].[FirstBookReleaseDate], EntireSpreadsheet_local.QueryReceived =
[QueriesACC].[QueryReceived], EntireSpreadsheet_local.Followup =
[QueriesACC].[Followup], EntireSpreadsheet_local.AcqNotes =
[QueriesACC].[AcqNotes], EntireSpreadsheet_local.[W-9Requested] =
[QueriesACC].[W-9Requested], EntireSpreadsheet_local.Method =
[QueriesACC].[Method]

WHERE ((([QueriesACC].[Title])=[EntireSpreadsheet_local].[Title] Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & ": " &
[EntireSpreadsheet_local].[Subtitle]) Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & " : " &
[EntireSpreadsheet_local].[Subtitle])));

Any ideas ?

Please post the SQL of this query. That shouldn't be happening!

John W. Vinson[MVP]

 

[John Vinson]

John, I think the problem was that I had inadvertently set the WHERE
statement to read WHERE EntireSpreadsheet_local.Title =
EntireSpreadsheet_local.Title, which will ALWAYS be true. I changed it to
read WHERE QueriesACC.Title = EntireSpreadsheet_local.Title.

I think it should work, however now when I go to import a file into Access
from Excel my CPU usage jumps to 100% and after a while I get the message
that the program is not responding. I'm not sure if it's in any way related,
but I can't really think of anything else that would be causing it.

Btw my code now looks like this:

UPDATE QueriesACC, EntireSpreadsheet_local SET
EntireSpreadsheet_local.ManuscriptRec = [QueriesACC].[ManuscriptRec],
EntireSpreadsheet_local.Accepted = [QueriesACC].[Accepted],
EntireSpreadsheet_local.Owner = [QueriesACC].[Owner],
EntireSpreadsheet_local.ContractReturned = [QueriesACC].[ContractReturned],
EntireSpreadsheet_local.AcqProvisions = [QueriesACC].[AcqProvisions],
EntireSpreadsheet_local.FirstBookReleaseDate =
[QueriesACC].[FirstBookReleaseDate], EntireSpreadsheet_local.QueryReceived =
[QueriesACC].[QueryReceived], EntireSpreadsheet_local.Followup =
[QueriesACC].[Followup], EntireSpreadsheet_local.AcqNotes =
[QueriesACC].[AcqNotes], EntireSpreadsheet_local.[W-9Requested] =
[QueriesACC].[W-9Requested], EntireSpreadsheet_local.Method =
[QueriesACC].[Method]

WHERE ((([QueriesACC].[Title])=[EntireSpreadsheet_local].[Title] Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & ": " &
[EntireSpreadsheet_local].[Subtitle]) Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & " : " &
[EntireSpreadsheet_local].[Subtitle])));

A couple of questions:

- Which table is in Excel, and which in Access?
- Is either table indexed? If so, on what fields?

You may need to just import EntireSpreadshet_Local into Access; be
sure there is an Index on QueriesACC.Title. Is there any way you can
get rid of the subtitle ambiguity prior to running the update query?
That's bound to slow things down!

John W. Vinson[MVP]

 

[Guest]

- Which table is in Excel, and which in Access?

Both tables have to be imported into Access from Excel.

- Is either table indexed? If so, on what fields?

The only index I have is on the PK of EntireSpreadsheet_local, which is just
an Access autonumber field. That said, I will definitely index Title on both
tables.

Is there any way you can
get rid of the subtitle ambiguity prior to running the update query?

The only way I can think of to do that would be to concatenate the Title and
Subtitle fields prior to updating. I can certainly do it, it just creates a
little more work that I was hoping to accomplish with my update query.

Thanks,

Chris

John, I think the problem was that I had inadvertently set the WHERE
statement to read WHERE EntireSpreadsheet_local.Title =
EntireSpreadsheet_local.Title, which will ALWAYS be true. I changed it to
read WHERE QueriesACC.Title = EntireSpreadsheet_local.Title.

I think it should work, however now when I go to import a file into Access
from Excel my CPU usage jumps to 100% and after a while I get the message
that the program is not responding. I'm not sure if it's in any way related,
but I can't really think of anything else that would be causing it.

Btw my code now looks like this:

UPDATE QueriesACC, EntireSpreadsheet_local SET
EntireSpreadsheet_local.ManuscriptRec = [QueriesACC].[ManuscriptRec],
EntireSpreadsheet_local.Accepted = [QueriesACC].[Accepted],
EntireSpreadsheet_local.Owner = [QueriesACC].[Owner],
EntireSpreadsheet_local.ContractReturned = [QueriesACC].[ContractReturned],
EntireSpreadsheet_local.AcqProvisions = [QueriesACC].[AcqProvisions],
EntireSpreadsheet_local.FirstBookReleaseDate =
[QueriesACC].[FirstBookReleaseDate], EntireSpreadsheet_local.QueryReceived =
[QueriesACC].[QueryReceived], EntireSpreadsheet_local.Followup =
[QueriesACC].[Followup], EntireSpreadsheet_local.AcqNotes =
[QueriesACC].[AcqNotes], EntireSpreadsheet_local.[W-9Requested] =
[QueriesACC].[W-9Requested], EntireSpreadsheet_local.Method =
[QueriesACC].[Method]

WHERE ((([QueriesACC].[Title])=[EntireSpreadsheet_local].[Title] Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & ": " &
[EntireSpreadsheet_local].[Subtitle]) Or
([QueriesACC].[Title])=([EntireSpreadsheet_local].[Title] & " : " &
[EntireSpreadsheet_local].[Subtitle])));

A couple of questions:

- Which table is in Excel, and which in Access?
- Is either table indexed? If so, on what fields?

You may need to just import EntireSpreadshet_Local into Access; be
sure there is an Index on QueriesACC.Title. Is there any way you can
get rid of the subtitle ambiguity prior to running the update query?
That's bound to slow things down!

John W. Vinson[MVP]