Breaking up an Excel Spread Sheet into Tables

[Guest]

I am trying to make a database for a friend that has all his records in an
excel sheet. However he has Owner1, Owner 2, etc.

Obviously this is not efficent and I would one table with the records, and
an additional table called owners. Some records have only one owner, while
others have up to five.

I have already imported this data in to access in one table, What query
would I run to break the information into the two tables mentioned above?

Thanks!

 

[Bob Quintal]

I am trying to make a database for a friend that has all his
records in an excel sheet. However he has Owner1, Owner 2, etc.

Obviously this is not efficent and I would one table with the
records, and an additional table called owners. Some records have
only one owner, while others have up to five.

I have already imported this data in to access in one table, What
query would I run to break the information into the two tables
mentioned above?

Thanks!

First determine if there is an existing field or combination of
fields in the table that uniquely identifies each row. If there is,
identify it/them as the primary key for the table. Proceed to step 3

step 2: If there is no candidate primary key, modify the table to
add an autonumber field, and identify it as primary key.

Create the Owners Table that contains two fields, the owner field
and a foreign key to link the owner to the correct row in the main
table.

Now create an append query that adds the two values into the new
table.

Run it, then modify it to do the second owner field instead of the
first, with the criteria that the owner field is not null.

Run again and modify the owner column until you've done all the
owner columns.

Check results carefully. and if ok, modify the main table - delete
the owner# columns.

 

[Pieter Wijnen]

The Current table structure would make it easier to give an answer
The technique is not difficult <g>

Pieter

 

[Guest]

The Table With the imported data has the following fields
ID, Cert Year, Tax#, Exempt Value, Taxable Value, Owner 1, Owner 2, Owner 3,
Owner 4, Owner 5

 

[Pieter Wijnen]

sound advise before you proceed, get rid of the spaces etc before you
proceed (use the caption property)
the example uses
ID, CertYear, Tax, Exempt, Taxable, Owner1, Owner2, Owner3, Owner4, Owner5

Public Function SplitOwner() As Boolean
Dim Db As DAO.Database
Dim QdM As DAO.QueryDef, QdS As DAO.QueryDef
Dim Rs As DAO.Recordset
Dim Prm As DAO.Parameter
Dim Fld As DAO.Field

Set Db = Access.CurrentDb
Set QdM = Db.CreateQueryDef(VBA.vbNullString, VBA.vbNullString)
QdM.SQL = "Parameters pID Long, pCertYear Long,pTax Double, pExempt As
Double, pTaxable As Double;" & VBA.vbCrLf & _
"INSERT INTO MainTable (ID, Tax, CertYear, Exempt,
Taxable) VALUES (pID, pTax, pCertYear, pExempt, pTaxable)"
Set QdS = Db.CreateQueryDef(VBA.vbNullString, VBA.vbNullString)
QdS.SQL = "Parameters pID Long, pOwner Text;" & VBA.vbCrLf & _
"INSERT INTO SubTable (ID, Owner) VALUES (pID,
pOwner)"
Set Rs = Db.OpenRecordset("SELECT * FROM ImportTable", DAO.
DbOpenSnapshot)
While Not Rs.EOF
For Each Prm In QdM.Parameters
Prm.Value = Rs.Fields(VBA.Mid(Prm.Name,2)).Value
Next
Qdm.Execute, DAO.DbSeeChanges
QdS.Parameters("pID").Value = Rs.Fields("ID").Value
For Each Fld In Rs.Fields
If VBA.InStr(Fld.Name,"Owner") > 0 And Not VBA.IsNull(Fld.Value) Then
QdS.Parameters("pOwner").Value = Rs.Fields("ID").Value
QdS.Execute, DAO.DbSeeChanges
End If
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
End Function

Barring the usual typos, neccessary changes (table names, Data types) etc,
this should work

Pieter

 

[Guest]

I can't comment on exactly how the query would look, but how about this table
structure:

Properties
-PropID(PkID no dups), Cert Year, Tax#, ExmpVal, TaxVal

Owners
-OwnerID(PkID dups ok), PropID(FkID), FirstName, LastName

Addresses
-PropID(FkID), OwnerID(FkID dups ok), Address, City, State, Zip

 

[John W. Vinson]

The Table With the imported data has the following fields
ID, Cert Year, Tax#, Exempt Value, Taxable Value, Owner 1, Owner 2, Owner 3,
Owner 4, Owner 5

I'd use (at least) three tables:

Properties
ID
CertYear
TaxNo <don't use # in fieldnames, it's a date delimiter>
TaxValue
<other info about the property itself, e.g. address>

Owners
OwnerID
LastName
FirstName
Address
<other biographical info>

PropertyOwners
PropertyID <link to Properties>
OwnerID <link to Owners>
<other information about THIS owner of THIS property, e.g. percentage
ownership, terms of ownership such as community property, etc.>

Note that you need THREE tables not two, as one property may have multiple
owners, but also each owner might own more than one property!

You would need to then run a query migrating data from the spreadhseet into
PropertyOwners: first create a UNION query like

SELECT PropID, [Owner 1] FROM wideflat
WHERE [Owner 1] IS NOT NULL
UNION ALL
SELECT PropID, [Owner 2] FROM wideflat
WHERE [Owner 2] IS NOT NULL
UNION ALL
<etc. through all 5>

Base an Append query on this query to populate PropertyOwners.

John W. Vinson [MVP]

 

[Pieter Wijnen]

Agreed

Pieter

The Table With the imported data has the following fields
ID, Cert Year, Tax#, Exempt Value, Taxable Value, Owner 1, Owner 2, Owner
3,
Owner 4, Owner 5

I'd use (at least) three tables:

Properties
ID
CertYear
TaxNo <don't use # in fieldnames, it's a date delimiter>
TaxValue
<other info about the property itself, e.g. address>

Owners
OwnerID
LastName
FirstName
Address
<other biographical info>

PropertyOwners
PropertyID <link to Properties>
OwnerID <link to Owners>
<other information about THIS owner of THIS property, e.g. percentage
ownership, terms of ownership such as community property, etc.>

Note that you need THREE tables not two, as one property may have multiple
owners, but also each owner might own more than one property!

You would need to then run a query migrating data from the spreadhseet
into
PropertyOwners: first create a UNION query like

SELECT PropID, [Owner 1] FROM wideflat
WHERE [Owner 1] IS NOT NULL
UNION ALL
SELECT PropID, [Owner 2] FROM wideflat
WHERE [Owner 2] IS NOT NULL
UNION ALL
<etc. through all 5>

Base an Append query on this query to populate PropertyOwners.

John W. Vinson [MVP]