Solved

Take a Flat file and make it into 2 realated tables

Posted on 2015-02-16
7
34 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:Scott Pletcher
Scott Pletcher 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 45
macro modification Column C 14 31
Creating An Intelligent Dropdown, Part Deux! 9 28
My SQL as Backend for Access 3 18
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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