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

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

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 )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

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.

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

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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