Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Query (2000)

    I am having difficulty using the append query wizard to insert data into my relational database. I would like to know how to use the INSERT INTO statement with the INNER JOIN statement to append data from one table to two tables that are joined with a primary and foreign key. I would like to know if this is possible to with a single query. The append query wizard only allows data to be appened from one table to another table, not two tables.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000)

    You'll have to explain further. Where is the data coming from that you're trying to append, and why are you trying to append it to two tables at once? Why don't you post the SQL you're trying to use so we can see what's wrong with it.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    The data is coming from a Excel spreadsheet, I imported it into a table in Access. It is typical contact information, business name, address, phone number, etc. I am trying to append the data to two tables in my database, the tables are in a one to many relationship. The name of the business is on the one side, and the other contact information is on the many side. I designed the database so that one business, could have multiple contacts. As I said in the previous post, the query wizard only allows me to append data to one table, below is the sql that does not work because the wizard is only INSERT INTO tblContactInformation, and BusinessName is a field in tblBusinessName

    INSERT INTO tblContactInformation ( BusinessName, FirstName, LastName, Address, City, State, ZipCode, BusinessPhone, Notes )
    SELECT PromoList.[Company Name], PromoList.Fname, PromoList.Lname, PromoList.Add1, PromoList.City, PromoList.St, PromoList.ZIP, PromoList.Wphone, PromoList.AltPhone
    FROM PromoList, tblBusinessName INNER JOIN tblContactInformation ON tblBusinessName.NameID = tblContactInformation.NameID;

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000)

    Did you try the wizard for analyzing tables? You will need to split your flat table up into more that two tables, or you'll still have at least one flat table to work with. If you insist on doing it manually (and no, the query wizard isn't the way to do it), read on.

    An inner join requires records on both side of the join. You don't have a record on the many side until you've create the record on the one side, so I'm not surprised it isn't working. What I would suggest instead is that you add a few fields to your flat table. Add a BusinessID field, an AddressID field, a PhoneID field, etc., all of them Longs but without any values in them. Then instead of a contact table that holds everything but the business name, create a table for each of the pieces of information that is discreet. For example, you might need more than one phone number for a contact/business, so create a Phone table that can hold as many numbers as you need for any given business. You'll need the phone number, a field that tells you the type of phone number it is (i.e., office, home, direct, mobile, fax, etc.) and a field for the foreign key that links it to a particular business or contact. Since a business could conceivably have multiple contacts and some of those could share the same phone numbers, you may need some join tables in there too. Again, I recommend you use the table analyzer and let it get you started toward a relational design.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    I am aware that my database is not fully normalized, however the existing table structure works fine for this project. I am still unsure how to import or append data into my database. I realize that importing data can be a complicated programming task. Is it possible to use the INSERT INTO statement to import new data into a relational database where there are tables, like in my previous example that are in a one to many relationship. You said that in a relational database an inner join requires that a record is created on the one side before the many side, does this rule not allow the use of INSERT INTO? Do you have any suggestions for importing data from another Access database or from an excel spreadsheet into more than one table in my existing database. Thanks,Ezra

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000)

    Why insist on importing into two tables at once? You might have had some luck with that in Access 97, but Access 2000 balks at queries that ran perfectly well in earlier versions. You didn't indicate whether you have referential integrity enforced between your tables, which could help populate the foreign key in your many-side table, but even that doesn't happen until you insert data into the many-side. I've never wanted to do this, so I can't tell you whether it will work.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    I am only trying to import one table. I want to import it into my database which has numerous tables in different relationships. The data that I want to import, Business name, address, phone number, etc. need to be imported into two tables that are in a one to many relationship

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Query (2000)

    You missed a key word: into. I asked why you needed to import into two tables at once. You're making extra work for yourself by trying to make it easy.
    Charlotte

Posting Permissions

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