append query criteria (Access97)

Topic

New Reply

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.

Reply | Quote

Replies

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

HTH

Peter

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote