Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 10th, 2006, 01:37 PM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Splitting Table into more efficient structure

Aloha,

I have data from an external source that needs to be converted into an Access 2003 database. I know about the import functions, my issue is that the format of the data lends itself to be multiple tables:

Sample:

Lastname
firstname1
birthday1
gender1
firstname2
birthday2
gender2
firstname3
birthday3
gender3
firstname4
birthday4
gender4
address

I would like to create two tables as follows and import the data to these tables:

Table1
Lastname
Address

Table2
Firstname
Birthdate
Gender

I don't see an easy way to do that - the wizard does not help.

Any suggestions? This is a common type of conversion but I am drawing a blank.

Mahalo,

MauiSpud
 
Old May 10th, 2006, 10:05 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Welcome to P2p, Maui!
Please confirm-does this data represent a "family" with the Lastname field the family surname and each set of entries afterward representing a member of the family? (ie: 0firstname + birthday + gender)= 1 person?

Loralee

 
Old May 10th, 2006, 11:33 PM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Loralee
 Welcome to P2p, Maui!
Please confirm-does this data represent a "family" with the Lastname field the family surname and each set of entries afterward representing a member of the family? (ie: 0firstname + birthday + gender)= 1 person?

Loralee

Thanks Loralee.

Yes, it does. I bought this data in that form. I know there is a way to do it easily - I am just drawing a blank.
 
Old May 11th, 2006, 06:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   You may want to do this kind of ETL (extract, transform, load) in Excel instead of Access.

   I am not sure why you would use a Family name as a PK. Will there only EVER be one instance of a family name? For example, couldn't there be two seperate "Smith" families?

   Anyway, if you push this data into Excel once, then insert a LastName column between "gender1" and "firstname2", "gender2" and firstname3", and "gender3" and "firstname4", then cut and paste the data from each of these groups into the first 4 columns, remove any blank records, and you will have your table. Just import it back into the database.

   This assumes that your Lastname look up is a text field.

HTH


mmcdonal
 
Old May 11th, 2006, 06:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, forgot to mention, after you insert the 3 lastname fields, copy and paste the lastnames from column 1 into them.



mmcdonal
 
Old May 12th, 2006, 01:38 AM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

MMC,

Thanks for the idea - the problem is is that I have 54,000 records to process. I am not using family name as the PK. There are other fields that I didn't mention.

 
Old May 12th, 2006, 06:13 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default


The other fields you are not mentioning are very important to know in order to come up with a solution. I am sure we could automate this with a complete list of fields.



mmcdonal
 
Old May 12th, 2006, 07:35 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Without more data, I would suggest doing the following:

Make a new table with the data structure you want for the FirstName table, and make a second table with the lastName structure.

The LastName table should have a numeric PK, but you may have a text PK, it looks like.

Create a series of Append queries.

Add new rows to the LastName table first. Create a query like:

SELECT DISTINCT LastName.tblYourTable, otherfields?
FROM tblYourTable

Use the query designer, select Query > Append, and send this data to the lastname table.

Then you will have to figure out hot to make the relation between the firstname table and the lastname table. If it is text, then no problem:

Create another append query that selects Lastname, firstname1 As Firstname, bday1 As bday, etc. and run that into the FirstName table.

Then create another append query pulling just the firstname2 data etc, where firstname2 <>"", etc.

Then just run all these queries. This is for a text FK. If it is numeric, then we may have to code it.

Is this helping?



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
Splitting columns and inserting to a Table praveennk84 SQL Server 2000 3 December 1st, 2006 08:16 AM
Copy table structure sagarbhargava SQL Server 2000 3 November 16th, 2006 03:16 AM
Splitting table data patingsadagat SQL Server 2000 7 June 2nd, 2006 03:09 AM
change table structure Duncan SQL Server 2000 1 March 3rd, 2005 04:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.