Splitting a Table

[ITbeast]

Howdy,

I am importing data into an Access table. Since the source of the data sucks, it just dumps all the info into a flat file. What I need to do to correctly compile my reports is to seperate the table into three normalized tables. The main dump table consists of address and date information which should go into one table (easy enough, i'm just using an append query for that) and first and last name information. This second part is where I'm having trouble. In the record there could be three possible last names and twelve possible first names for each last name! So record has 36 fields for possible names. What I did was create a lname table and a fname table, but I'm not sure how to split the inital table using one or more append queries.

Any ideas?

 

[PHV]

Have a look at UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjlogan]

ITBeast - It would help to know how this table with all the fields is used; e.g. why three last name fields? Could it be that up to three people are being listed for a given record? If had more insight into what's going on, might be able to comment directly on issue.
jjlogan

 

[ITbeast]

jjlogan - Ok, this is a kinda weird setup, that's why i didn't get into too much detail at first.

this is for a census project and the data is being collected on pda's, then at the end of the day exported to mdb file. so the initial table (dump_table) has a bazillion fields in it. so it has three fields for last names because we figured there could be up to three last names for a given house. what i wanted to do was after the data was imported to split it into smaller tabels that my queries and reports run off of.

so my setup is as follows:
dump_table:
form_id char PK
date
precinct
street
house_num
unit_num
primelast (first last name)
primeres1 (first first name)
.
.
.primeres12

and so on with a seclast then secres1, and a thirdlast with a thridres1


i want to split it up into:
lname_table:
form_id FK to dump_table
last_name
name_id PK FK to fname_table


fname_table:
form_id FK to dump_table
name_id FK to lname_table
first_name



house_table:
form_id FK to dump_table
date
street
address
unit



any help with this would be awesome!!

 

[jjlogan]

ITBeast,
Thanks for updating.
What purposes would there be for a lname table (last names) and a fname table? Are you trying to do statistics on last names and/or first names, or some other census thing on last name data? If you are making a last name table just so you can link or join that table to a first name table, I would consider a simpler table arrangement as follows:
Address table (each record represents one unique address x Unit). A second table that represents People (FK = home table PK; PK = AutoNumber; other fields are Last Name, First Name, and any other census data you captured on each person like age, occupation, etc.)Then join the two tables on Address key (PK and FK) and you should be able to get any reports needed.
Jjlogan

 

[jjlogan]

ITBeast,
You asked about updating the new table(s). I gave my view on design of new tables, but also had a comment / recommendation on how to update the new table. If the logic to determine which last and first names go together in the new table is quite complex, then I would use ADO recordset updating in a Module. I'd create a new Module sub procedure, Dim recordsets as ADO recordsets, base the recordsets on a query in your Access project (using one recordset to represent the old table and one recordset to rep the new table), then go record-by-record thru old data and using VBA logic determine what field data needs to be copied over. Before going to the next record in old, update the next record in new data recordset. Loop thru all records representing old. When recordset is updated, the table is also updated. Jeff