"Chris Burnette" wrote:

> 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

"John Vinson" wrote:

> On Thu, 3 Nov 2005 10:29:06 -0800, "Chris Burnette"
> <ChrisBurne***@discussions.microsoft.com> wrote:
>
> >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]   
>

"Chris Burnette" wrote:

> 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
>
> "John Vinson" wrote:
>
> > On Thu, 3 Nov 2005 10:29:06 -0800, "Chris Burnette"
> > <ChrisBurne***@discussions.microsoft.com> wrote:
> >
> > >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]   
> >

"John Vinson" wrote:

> On Fri, 4 Nov 2005 07:00:06 -0800, "Chris Burnette"
> <ChrisBurne***@discussions.microsoft.com> wrote:
>
> >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]   
>

"Chris Burnette" wrote:

> 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 ?
>
> "John Vinson" wrote:
>
> > On Fri, 4 Nov 2005 07:00:06 -0800, "Chris Burnette"
> > <ChrisBurne***@discussions.microsoft.com> wrote:
> >
> > >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]   
> >

>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])));

"John Vinson" wrote:

> On Fri, 4 Nov 2005 11:21:05 -0800, "Chris Burnette"
> <ChrisBurne***@discussions.microsoft.com> wrote:
>
> >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]   
>