Solved

Take a Flat file and make it into 2 realated tables

Posted on 2015-02-16
7
31 Views
Last Modified: 2016-05-22
I have a Flat file with 10k records.  It has a unique ID per row.   I need to break it into 2 tables relating to each other.
The table has names and addresses. The names have unique ID's as well and addresses can be many.  I would like to have Name table and Address table and keep them linked.  This is just an example.

Unique ID----Name ID(unique as well)   Addresses
1                     200-Chuck                            11 W 5th
2                     200-Chuck                            35  N St
3                     100-Bill                                  11 W 5th
4                     300-John                               22 E 7th
5                     300-John                               35 N St
0
Comment
Question by:Joe Vaughan
  • 3
  • 2
7 Comments
 

Author Comment

by:Joe Vaughan
ID: 40611976
The Name ID is a separate field as well as the name.  (looks attached in one field in my example..it's 2 separate fields)
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40611989
I would first import the entire dataset to a temporary "staging" table. You can use TransferText to do this:

DoCmd.TransferText acImportDelim, "Specification Name", "Staging Table Name", "Path To Your Textfile"

https://msdn.microsoft.com/en-us/library/office/ff835958.aspx

From there, you can use different methods, depending on whether your "live" tables already exist. To insert all distinct NameID values from tmpStaging to a table named Customers:

CurrentProject.Connection.Execute "INSERT INTO Customers(Name_ID) (SELECT DISTINCT [Name ID] FROM tmpStaging LEFT OUTER JOIN Customers ON tmpStaging.[Name ID]Customer.Name_ID WHERE Customer.Name_ID IS NULL)"

From there, you'd have to loop through all the DISTINCT values in tmpStaging and move those to your live Address table:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT DISTINCT [Name ID] FROM tmpStaging")

Do Until rst.EOF
  Dim rstAdd as DAO.Recordset
  Set rstAdd = Currentdb.OpenRecordset("SELECT * FROM tmpStaging WHERE [Name ID]='" & rst("Name ID") & "'")
  Do Until rst2.EOF
    CurrentProject.Connection.Execute "INSERT INTO Address(Name_ID, Address) VALUES('" & rst("Name ID") & "','" & rst2("Address") & "'")
    rst2.MoveNext
  loop
  rst.MoveNext
Loop
0
 

Author Comment

by:Joe Vaughan
ID: 40611993
Wow...I remember VBA from the old days when I was doing a lot of access stuff.   Not sure I'm getting this.  Maybe you answered above.  But I'll repeat this just in case.  I actually have the table in the database.  I have unique Name ID's with the names.(was already in the flat file when I imported).  Did a Distinct and came away with a simple table.  I can get unique addresses...not a problem.  But can't seem to figure out what I need to do to get the appropriate information in the related table...Nametbl----->Related Tbl<-------Address table.   Make Sense or am I not understanding your solution?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 84
ID: 40612118
If you already have the table, then ignore the TrasnferText stuff - you don't need it.

If you already have a Table where the Distinct Names are stored (i.e. your "simple table") then ignore the part where you would insert the Distinct ID values (you've already done that).

From there - do you have an Address table? If not, then you could create one, and use an INSERT method to insert the values in your imported table to there, and also relate them to the NameTbl. Unless an Address can be related to more than one record in NameTbl, then you don't need the <Related Tbl>, all you need is a field in the AddressTbl to store the ID value from NameTbl.

If you could explain a bit further about your setup now, we could provide a bit more focused help.
0
 

Author Comment

by:Joe Vaughan
ID: 40612774
There can be many addresses for one Name ID in the Nametbl.  In my example, I'm trying to get this as a result.
Nametbl- should have NameID 200, Name Chuck  ------ the address should be unique as well with only 3 addresses as seen below.  The related table should have 2 entries of 200 with 2 Address IDs corresponding to 11 W 5th and 35  N St.  One to many Nametbl to Related Table and Address to Related table. ( my flat file was this below (excel) I can bring it in to access etc...just can't figure out how to make it work like I need it to.)

Unique ID----Name ID(unique as well)   Addresses
 1                     200-Chuck                            11 W 5th
 2                     200-Chuck                            35  N St
 3                     100-Bill                                  11 W 5th
 4                     300-John                               22 E 7th
 5                     300-John                               35 N St

Result should look like this. Need to create an address key that will tie together the information in the related table.  I'm placing a fictitious number for address so you can see what I would like.

Nametbl   -------------------------------------> Rel Name ID/Rel address ID)  <----------------------Address tbl ID     Address
-------------------------------    ------------------------------------------------------------------------       ------------------------------------
Name ID   Name                                    200       Address ID->>>>>>1>>>>>>>>>>>..                    1          11 W 5th
200            Chuck                                    200      Address ID->>>>>>2   >>>>>>>>>>                      2          35  N St
100           Bill                                           100     Address ID->>>>>>3   >>>>>>>>>>                       3         22 E 7th
300           John                                        300      Address ID->>>>>>2   >>>>>>>>>>
                                                                  300      Address ID->>>>>>3   >>>>>>>>>>
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 40614685
Easiest would be to add an identity column on the address table.  Then,  as you're doing now, insert the DISTINCT names and addresses into their respective tables.  

Lastly, one final pass thru the main table inserting into the related (intersection) table, like so:

INSERT INTO dbo.related (
    Name_ID, Address_ID
    )
SELECT fi.Name_ID, da.address_ID
FROM flat_import fi
INNER JOIN distinct_addresses da ON
    da.address = fi.address
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now