Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Update or Append Query help

    I imported data from Excel and made a table from it. Now I need to populate two other tables with the data I imported from Excel. One is the parent table and the other is the child. Is there a way to append or update the two tables with the data I imported from the Excel spreadsheet? I noticed that with the append query I can only append to one table. Any help would be appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by bobbio
    I imported data from Excel and made a table from it. Now I need to populate two other tables with the data I imported from Excel. One is the parent table and the other is the child. Is there a way to append or update the two tables with the data I imported from the Excel spreadsheet? I noticed that with the append query I can only append to one table. Any help would be appreciated.
    You make an append query for each table. But you need to have the field names the same in the tables or tables you have made from Excel.

    If you are bringing something regulary from Excel then a good thing to do is to make a query on the table made from the Excel import and use this query to make the field names the same as the tables you wish to append to.

    For example, if you let the import run and form its own field names then you will get Field1, Field2, Field3 etc. Now let's say that Field1 corresponds to LastName in your table and Field2 corresponds to FirstName in your table etc.

    The in your query based on the table created from the import from Excel you create new fields as like:

    LastName:[Field1]
    FirstName:[Field2]

    and so on. The you can append this query to your existing tables.

    Mike

  3. #3
    Join Date
    Jun 2004
    Posts
    21
    Thank you for the quick response Mike.
    How would I set up the two append queries if one table is the child table of the other. For example, lets just say my parent table has 3 fields: PersonID(PK), LastName and FirstName. My child table has 3 fields: DonorID (PK), PersonID (FK) and Notes.

    The table that I just imported from Excel has the fields LastName, FirstName and Notes. Two of the fields are in the parent table and one of them is in the child table, although the child table needs to know the PK. See my problem?

    Thanks again.

    Quote Originally Posted by Mike375
    You make an append query for each table. But you need to have the field names the same in the tables or tables you have made from Excel.

    If you are bringing something regulary from Excel then a good thing to do is to make a query on the table made from the Excel import and use this query to make the field names the same as the tables you wish to append to.

    For example, if you let the import run and form its own field names then you will get Field1, Field2, Field3 etc. Now let's say that Field1 corresponds to LastName in your table and Field2 corresponds to FirstName in your table etc.

    The in your query based on the table created from the import from Excel you create new fields as like:

    LastName:[Field1]
    FirstName:[Field2]

    and so on. The you can append this query to your existing tables.

    Mike

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    How would I set up the two append queries if one table is the child table of the other. For example, lets just say my parent table has 3 fields: PersonID(PK), LastName and FirstName. My child table has 3 fields: DonorID (PK), PersonID (FK) and Notes.

    The table that I just imported from Excel has the fields LastName, FirstName and Notes. Two of the fields are in the parent table and one of them is in the child table, although the child table needs to know the PK. See my problem?


    So you create one table from your Ecel import and this table contains the data for both of your existing tables?? You have LastName, FirstName and Notes from Excel and onl Notes is common to both tables.

    So we have from Excel

    LastName.........FirstName......Notes
    Data...............Data..............Data

    Your existing tables

    PersonID(PK)....LastName.......FirstName

    and

    DonorID (PK)....PersonID (FK)..Notes.

    and from Excel

    LastName.........FirstName......Notes
    Data...............Data..............Data

    What do want to get from "where" from the Excel based table to "where" in your existing tables.

    It is sounding as if you might needs a Select (Or MakeTable) query to separate some data out from the Excel created table

    Mike

  5. #5
    Join Date
    Jun 2004
    Posts
    21

    Your existing tables

    PersonID(PK)....LastName.......FirstName

    and

    DonorID (PK)....PersonID (FK)..Notes.

    and from Excel

    LastName.........FirstName......Notes
    Data...............Data..............Data

    What do want to get from "where" from the Excel based table to "where" in your existing tables.


    I want to be able to append the LastName and FirstName to the first table (it's an existing table already populated with data) so a PersonID could be assigned. Then I want to be able to use that ID and append the 'Notes' field to the other table with the Notes field (But it needs a PersonID). I guess I'm just puzzled on the joins in the query to be able to retreive the LastName and FirstName that I just appended and not retrieve any of the previously existing data which may have people with the same Last Name and First Name. Thanks for your replies Mike, I appreciate it.

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I am probably missing something here but I would add an autonumber field to the table you have created from Excel so it becomes

    LastName.........FirstName......Notes.........Auto numberFieldName
    Data...............Data..............Data......... .Numbers

    Then make one append query appending the Notes and Autonumber field to this table

    DonorID (PK)....PersonID (FK)..Notes.

    and then make an append query to append LastName, FirstName and AutonumberFieldName to this table

    PersonID(PK)....LastName.......FirstName

    You will need to give the new autonumber field in the Excel created table the same name as the PK fields in the other tables.

    Obviously have a copy of the tables beforre you append

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •