Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Error running append query?

Discussion in 'Information Technology' started by Chris Burnette, Nov 4, 2005.

  1. Chris Burnette

    Chris Burnette
    Expand Collapse
    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
     
  2. Loading...


  3. Chris Burnette

    Chris Burnette
    Expand Collapse
    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?

    "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
     
  4. TC

    TC
    Expand Collapse
    Guest

    Show us the query.

    TC
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 3 Nov 2005 10:29:06 -0800, "Chris Burnette"
    <ChrisBurnette@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]
     
  6. Chris Burnette

    Chris Burnette
    Expand Collapse
    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

    "John Vinson" wrote:

    > On Thu, 3 Nov 2005 10:29:06 -0800, "Chris Burnette"
    > <ChrisBurnette@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]
    >
     
  7. Chris Burnette

    Chris Burnette
    Expand Collapse
    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

    "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"
    > > <ChrisBurnette@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]
    > >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 4 Nov 2005 05:59:03 -0800, "Chris Burnette"
    <ChrisBurnette@discussions.microsoft.com> wrote:

    >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]
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 4 Nov 2005 07:00:06 -0800, "Chris Burnette"
    <ChrisBurnette@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]
     
  10. Chris Burnette

    Chris Burnette
    Expand Collapse
    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 ?

    "John Vinson" wrote:

    > On Fri, 4 Nov 2005 07:00:06 -0800, "Chris Burnette"
    > <ChrisBurnette@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]
    >
     
  11. Chris Burnette

    Chris Burnette
    Expand Collapse
    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.

    "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"
    > > <ChrisBurnette@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]
    > >
     
  12. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 4 Nov 2005 11:21:05 -0800, "Chris Burnette"
    <ChrisBurnette@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]
     
  13. Chris Burnette

    Chris Burnette
    Expand Collapse
    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 Vinson" wrote:

    > On Fri, 4 Nov 2005 11:21:05 -0800, "Chris Burnette"
    > <ChrisBurnette@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]
    >
     

Share This Page