troubleshooting Question

Converting access table to representative sub tables

Avatar of Chris Bottomley
Chris BottomleyFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft OfficeMicrosoft AccessMicrosoft Applications
26 Comments3 Solutions362 ViewsLast Modified:
I have a flatfile table in my database and need to keep working in this form until my desired changes are complete.  I believe the table needs to be split into owner details with a link to the property table.  Neither of these two tables exist and noting I want to keep the original table for the forseeable future but want to develop the upgrade then in order for me to be able to test using live data I want to  split the source table into the sub tables on demand so that the sub tables can always be redefined from the live data.

The orig table for example has fields:
Name ... appears as new parent record
Address ... appears as new parent record
Entry Date ... appears as new parent record
PetAge ... appears as new child record
PetName ... appears as new child record
PetDOB ... appears as new child record

The parent record however will need to be linked to the child record but before doing so do I need it? ... so this question is about:

When creating the new tables how do I, (Can I?)  ensure that only one record exists per data set, (name, address, entry date for example) ... A parent may have two entries for two pets but the parent table will only require one such parent entry.  Please consider any mechanisms available to minimise duplication, (case / spacing differences for example).

Sub splitSrcTable()
Dim arrTables As Variant
Dim tbl As Variant

    arrTables = Array("tblParent", "tblChild")
    For Each tbl In arrTables
        If TableExists(CStr(tbl)) Then CurrentDb.Execute "drop table " & tbl
' Populate new Parent Table
    CurrentDb.Execute "Select " & _
        "[name] as [name1], " & _
        "[address] as [address2], " & _
        "[entry date] as [entrydate3] " & _
        "INTO " & "tblParent" & " from table1"
' Populate new Child Table
    CurrentDb.Execute "Select " & _
        "[petage] as [Age], " & _
        "[petName] as [Name], " & _
        "[petDOB] as [DOB] " & _
        "INTO " & "tblChild" & " from table1"
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 26 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros