Avatar of Chris Bottomley
Chris Bottomley
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Converting access table to representative sub tables

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).

Chris
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
    Next
' 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

Open in new window

Microsoft AccessMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
Chris Bottomley

8/22/2022 - Mon
rabarlow

Hi Chris,

I would probably structure the queries as

1) (Creating parent database) - note the use of DISTINCT to ensure no duplicates

SELECT DISTINCT Name as ParentName, Address, EntryDate into tblParent from table1

2) (Creating child database)

Select PetAge, PetName, PetDOB, Name as ParentName into tblChild from table1

Then use ParentName as the Key Field in ParentDatabase and create a relationship if required.
Chris Bottomley

ASKER
DistInct noted but have tried it on test data and the distinct seems to be based on name address and date whereas I would expect logically to apply it to address and for address to have a number of names ... Or for name and address to be the reference data I.e date to be ignored in connection with distinct

Chris
Scott McDaniel (EE MVE )

Sounds as if you first need to determine exactly what constitutes a unique record. If you wish for Address to be the "top level table", then you need to build a structure like this:

tblAddress
    V
    V
tblPeople
   V
   V
tblPets

Assuming a "Pet" belongs to a "People", and a "People" belongs to an Address.

However, given that people change their address all the time (and that Person1 living at AddressA may change their address, but Person2 who also lives at AddressA will NOT), it makes sense generally to store Address data with the Person (unless you need to store multiple addresses with the same person - for example a Shipping Address and a Billing Address).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Bottomley

ASKER
Seems name and address is the reference datum then ... I assume a change of name or address can be accommodated within the linkage I.e record 1 is Fred with address home.  If Fred moves to away it is still the same record for pet purposes so a separate reference point will be required to the parent table.

Assuming so then the question is still how can the name address group be made distinct when copying records from the original table?

Chris
rabarlow

How about

SELECT Name as ParentName, Address, First(EntryDate) into tblParent from table1 GROUP BY table1.Name, table1.Address

Ralph
SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rabarlow

Or

SELECT Name as ParentName, Address, Min(EntryDate) into tblParent from table1 GROUP BY table1.Name, table1.Address

[THis version uses the earliest EntryDate]
Ralph
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER
Rabat ow ... Your entry assumes I know the earliest date I won't necessarily

LSM, ... can I call you that :o)

Seems to make sense and will test later but ... If i add a new record can I simply reference that new record by an identifier ... First record as I'd 1, second record  id 2 etc. For ease instead of the and on name and address.

Just trying to think tidy!

Chris
Scott McDaniel (EE MVE )

As there is no guarantee that Record#1 in Table1 will be Record #1 in tblParent, I'd encourage you to JOIN on those fields to insure that you are dealing with the correct data.

And you can call me anything, long as ya don't call me late for dinner!
rabarlow

Chris,

You don't need to know the earliest date, the min function does all that for you.

Ralph
Your help has saved me hundreds of hours of internet surfing.
fblack61
Chris Bottomley

ASKER
I wasn't thinking so much in those terms but was trying to avoid tainting the request with little bits of knowledge ... However

I was imagining that when creating the record in parent that a numerical I'd could be assigned to it as the key and that the key could then be returned and used for a set of actions on the database at that point

Chris
Chris Bottomley

ASKER
Continuing on the theme of a little bit of knowledge ... I seem to recall datums other than the key can be used to enforce uniqueness I.e the modified insertion clause of your post and the I'd merely becomes a shortcut to avoiding name and addresss constructs during a specific set of operations on related tables ... I.e it is what could be used to insert the link to parent in the child table.

Name and address could also be used here but an I'd seems easier even if a join would be required to return the full detail when needed for example in a report.

Chris
Scott McDaniel (EE MVE )

You could do that, but you can't do so using SELECT INTO. If you want to do that, build a table with the structure you want, and then INSERT into that table. You can set an AutoNumber value on that table, and Access will assign a new value to the incoming data as needed. Note that an AutoNumber fields is NOT guaranteed to be sequential, just unique.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER
Ralph

Point taken, (I just assumed it was meant as having to type it therein ... Stupid since I use min in excel) ... I am still concerned in that for the simple table there are just the three items but in the long term there will be many so I would like something less demanding.

I also suspect min will assume the records are sorted in date order ... Something else that would concern me!

Chris
rabarlow

Chris,

Min will find the earliest date for you.  If you used FIRST instead, that would just use the first date it came across for that name/address combination.

Ralph
Chris Bottomley

ASKER
LSM

Currently using the snippet below ... falls over on the update with "Syntax error in update statement"

Chris
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
    Next
' Populate new Parent Table
    CurrentDb.Execute "Select DISTINCT " & _
        "[id], " & _
        "[name] as [name1], " & _
        "[address] as [address2] " & _
        "INTO " & "tblParent" & " from table1"
    CurrentDb.Execute "UPDATE tblParent JOIN table1 on ((tblparent.name1 = table1.name) AND (tblparent.address2 = table1.address)) SET tblparent.[entrydate] = table1.[entry date]"
' Populate new Child Table
    CurrentDb.Execute "Select " & _
        "[petage] as [Age], " & _
        "[petName] as [Name], " & _
        "[petDOB] as [DOB] " & _
        "INTO " & "tblChild" & " from table1"
    
End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER

I would admit I thought your earlier post flawed so did what I thought was meant.  I can see something in the later post but it is now failing on the select ... I tried " followed by two ' as well as two " but still no luck with the syntax and doubling up on dbl quotes doesn't help either.

Chris
Chris Bottomley

ASKER
Apologies missed a comma, failing again on the update so checking for silly mistakes ... Like commas!
Chris Bottomley

ASKER
Cannot see see any error and I even modified the data to remove deliberate duplicate name and address combinations

Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER
Checking the tblparent at the point of fail, shows a blank field entrydate therein as distinctly different by design to the entry date in the table1.

Chris
Scott McDaniel (EE MVE )

I don't follow what you mean ... are you saying that tbleParent is being created with a blank entry date? If so, that's what we would expect.

If it's being created but is failing on the UPDATE, the we'll concentrate on that.
Chris Bottomley

ASKER
You understand correctly, (i was making teh statement to try to confirm the select statement was working 'differently and presumably correctly ... tick) ... casting about the syntax of update might be wrong ... I have modified to try something that looks right according to some other posts ... but it fails saying expect three parameters.

The original from your post still throws up syntax error.

Chris
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
    Next
' Populate new Parent Table
    CurrentDb.Execute "Select DISTINCT " & _
        "[name] as [name1], " & _
        "[address] as [address2], " & _
        "'' as [entrydate] " & _
        "INTO " & "tblParent" & " from table1"
    CurrentDb.Execute "UPDATE tblParent SET tblparent.[entrydate] = table1.[entry date] WHERE ((tblparent.name1 = table1.name) AND (tblparent.address2 = table1.address))"
'    CurrentDb.Execute "UPDATE tblParent JOIN table1 on ((tblparent.name1 = table1.name) AND (tblparent.address2 = table1.address)) SET tblparent.[entrydate] = table1.[entry date]"
' Populate new Child Table
    CurrentDb.Execute "Select " & _
        "[petage] as [Age], " & _
        "[petName] as [Name], " & _
        "[petDOB] as [DOB] " & _
        "INTO " & "tblChild" & " from table1"
    
End Sub

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER
It was inner join that did the job

Chris
Chris Bottomley

ASKER
Code as used for the solution.

Chris
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
    Next
' Populate new Parent Table
    CurrentDb.Execute "Select DISTINCT " & _
        "[name] as [name1], " & _
        "[address] as [address2], " & _
        "'' as [entrydate] " & _
        "INTO " & "tblParent" & " from table1"
    CurrentDb.Execute "UPDATE tblParent INNER JOIN table1 on ((tblparent.name1 = table1.name) AND (tblparent.address2 = table1.address)) SET tblparent.[entrydate] = table1.[entry date]"
' Populate new Child Table
    CurrentDb.Execute "Select " & _
        "[petage] as [Age], " & _
        "[petName] as [Name], " & _
        "[petDOB] as [DOB] " & _
        "INTO " & "tblChild" & " from table1"
    
End Sub

Open in new window