Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    append query criteria (Access97)

    i need to move records from one table to another within the same database from a one table structure to a 2 table structure.

    tblcombinedinfo is imported from another database, it contains a FName, LName, Email, DistAdd1, DistAdd2, DistAdd3, DistFax, DistPh, GOAdd1, GOPh, GOFax. There is no autonumber (it wouldn't match the new tables values anyway) therefore the only way to indentify these rows would be to use the LName, FName combination as a primary key.

    the 4 DistAdd fields and the Dist phone and fax need to be appended to tblLegDistrictAdd. tblLegDistrictAdd was created upon the decision to split the name fields from the address fields and link by an autonumber in order to accommodate the possibility of more than one address. so the new tables are as follows:

    tblLegislators
    LegID - autonum
    FName - text
    LName - text
    GORoomNo - text
    GOPh - number
    GOFax - number
    GOBldgID - number SELECT DISTINCTROW [tblBuilding].[BldgID], [tblBuilding].[Bldg] FROM [tblBuilding];

    tblLegDistrictAdd
    DistAddID - autonum
    LegID - number SELECT DISTINCTROW [tblLegislators].[LegID], [tblLegislators].[LName], [tblLegislators].[FName] FROM [tblLegislators] ORDER BY [LName];
    DistAdd1-DistAdd4, DistAddPh, DistAddFax (phone and fax are number fields with imput masks)


    I cannot write an append query that is netting me the proper results and i think it has to do with the last name and first name needing to both be the unique identifier. All the fields in tblcombinedinfo are textfields which means that the phone and fax numbers are probably stored as text with the dashes included. I appreciate any help with this one. and if that's not enough, problem #2 is below.

    There are some other things that need to be done with this data too. the GOAdd1 in tblcombinedinfo needs to be appended to tblLegislators.GORoomNo but only the first portion of the value stored in that field. (originally GOAdd1 had both a room number 3 digits long and a bldg number 3 letters long the numbers and letters are seperated by a space.) The new Field is GORoomNo and is simply the room number and the building is in another field as a lookup. I do not need to append the building.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query criteria (Access97)

    Have a look at Tools>Analyze>Table, you can probably use this Wizard to split the table for you.


    HTH

    Peter

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query criteria (Access97)

    unfortunately, the table splitter can only work if there wasn't already data in the destination table - tblLegislators. This table has established relationships with other tables and several junction tables based upon the LegID field that is not included in the tblcombinedinfo. remember that this tblcombindedinfo is data from another database entirely, imported so it can be incorporated into this db that needs the same information, only this "same information" is presented in an entirely different structure. I'm going to play with the splitter some more to see if i can get around it... thanks Bat17

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: append query criteria (Access97)

    Have you already created the table that will contain the address info? If so the splitter won't help you, and in any event it's primary purpose is to take a database and move the tables to a back-end database and then create links to the tables in the front-end database. (There are lots of advantages to doing that which I won't go into here.)

    You can create the address table in one of two ways. The first is to run a make-table query which takes a subset of the data from the table you imported and build the entire table in one shot. However you do need to keep the association between legislator and address, so you probably want to create an autonumber field for the Legislator table first, and then store that number as a link in the address table when you run the make table-query. The second way is to define the table structure to your own satisfaction, and then run an append query that take the selected fields and stuffs them into the new table structure. You will still want to create a unique identifier to store in the address table. In the long run, you want to have an autonumber field for the address table too, so that Access will treat it as updateable, but that can be added after the table is populated. (You can't use the Legislator ID as the primary key since you plan to allow multiple addresses for a legislator.) Hope this helps clarify the process.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query criteria (Access97)

    The two tables from the original post represent the new structure that data has to be incorporated into.

    tblLegislators is already filled with data (the most signifcant of which is the LName, FName, and LegID fields).

    the LName and FName fields in tblcombinedinfo has identical data to the corresponding fields in tblLegislators.

    therefore i need to know how to write an append query that will append the DistAdd1, DistAdd2, DistAdd3, DistAdd4, DistPh, DistFax from tblecombinedinfo to tblLegislatorsDistAdd AND append LegID from tblLegislators to tblLegislatorsDistAdd WHERE tblcombinedinfo AND tblLegislators have both matching FName and LName fields.

    OR perhaps, i should just append the address info into tblLegislatorsDistAdd and try a hand at manual reassociation of the addresses. <img src=/S/puke.gif border=0 alt=puke width=60 height=15>

Posting Permissions

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