Import tables have same fields

[Guest]

I am currently setting up a Access 2000 database that will track
land/property use. I have imported an Excel data sheet into the database that
I have split into two tables (about 1700 records) as follows:

OWNER TABLE
FieldName OwnerID (Primary key- autonumber)
FieldName Lastname
FieldName Firstname
FieldName Street
FieldName City
FieldName State
FieldName Zipcode
FieldName Telephone

PROPERTY TABLE
FieldName PropertyID (Primary key- autonumber)
FieldName OwnerID (foriegn key to OwnerID - OWNER Table)
FieldName CurrentOwnerLastname
FieldName CurrentOwnerFirstName
FieldName PhysicalAddress
FieldName TaxMapLotNumber
FieldName DeedBookPageNumber
FieldName Acres

What I would like to do is to retain the property.CurrentOwnerLastName and
property.CurrentOwnerFirstName field values in existing property table but
get rid of the fields after (for obvious normalization reasons). I want to
have a form setup with a combo/lookup box to assign/reassign owners to
existing or newly created properties in the property table. The reason for
this was to keep existing ownership assignment values in the property table
without having to do it manually. Maybe there is a different way to
manipulate the Excel sheet before I import it into the two tables. Thought
this would be easier !! I hope my explanation makes since ?

Thanks for any replies

 

[John Vinson]

Maybe there is a different way to
manipulate the Excel sheet before I import it into the two tables.

I'd suggest importing the wide-flat Excel sheet (or just linking to
it), and then running Append queries to populate your normalized
(empty, to start with) tables. You can use a DISTINCT (Unique Values
property) query to select only one instance of each owner.

John W. Vinson[MVP]

 

[Guest]

Thanks for your suggestion. I will look into it and report back
--
Thanks for any replies

ksr

 

[Larry Linson]

What I would like to do is to retain the
property.CurrentOwnerLastName and
property.CurrentOwnerFirstName field
values in existing property table but
get rid of the fields after (for obvious
normalization reasons).

If I understand you, you want to keep the Last and First Names of the owner
in both the Owner and the Property Tables, even though you have a foreign
key in the Property Table that will let you access the owner's first and
last names in the Owner Table. Because you can use Queries with Joins in
Access, there isn't a need to do that unless there is a difference in
definition (that I did not understand).

I don't understand what you mean about getting rid of the fields that
follow... are you talking about the original spreadsheet that we can't see?

And, are you certain that there will never be a situation where one property
may have multiple owners, as well as a given owner having multiple
properties? In the "real world" of property ownership, this would probably
be a many-to-many relationship. Many-to-many relationships are implemented
in Access with a third Table -- yours would have foreign keys to each of the
Owner and Property Tables.

I want to have a form setup with a
combo/lookup box to assign/reassign
owners to existing or newly created
properties in the property table. The
reason for this was to keep existing
ownership assignment values in the
property table without having to do
it manually.

This is certainly an effective way to do what you want... the Combo Box will
have a Row Source of the Owner Table, and display the name but store only
the key value.

Maybe there is a different way to
manipulate the Excel sheet before I
import it into the two tables. Thought
this would be easier !! I hope my
explanation makes since ?

Because spreadsheets (including Excel) do not have the relational capability
of Access, I suspect you will find it easier to do the manipulation _after_
you import the data.

Larry Linson
Microsoft Access MVP

 

[Guest]

--
Thanks for your time

ksr

If I understand you, you want to keep the Last and First Names of the owner
in both the Owner and the Property Tables, even though you have a foreign
key in the Property Table that will let you access the owner's first and
last names in the Owner Table. Because you can use Queries with Joins in
Access, there isn't a need to do that unless there is a difference in
definition (that I did not understand).

***Thanks for clarifying. Yes, that is what I am trying to achieve.

I don't understand what you mean about getting rid of the fields that
follow... are you talking about the original spreadsheet that we can't see?

***Please disregard what I said because you have explained what needs to be
done in your previous reply

And, are you certain that there will never be a situation where one property
may have multiple owners, as well as a given owner having multiple
properties? In the "real world" of property ownership, this would probably
be a many-to-many relationship. Many-to-many relationships are implemented
in Access with a third Table -- yours would have foreign keys to each of the
Owner and Property Tables.

*** Most of the properties I looked at have single owners or organizations
that own them and some owners have multiple properties. I agree that this is
a distinct possibility.

This is certainly an effective way to do what you want... the Combo Box will
have a Row Source of the Owner Table, and display the name but store only
the key value.
***Yes


Because spreadsheets (including Excel) do not have the relational capability
of Access, I suspect you will find it easier to do the manipulation _after_
you import the data.

*** Agreed

 

[Guest]

Need someone (for a fee) who is interested in getting the tables and the many
to many relationships setup to get the "ball rolling"so to speak.

Have tried but a bit rusty I guess as well as too many interruptions to
concentrate long enough. Help !!

If interested I can setup a remote session to take a look at what I have so
far

 

[Guest]

I don't understand what you mean about getting rid of the fields that
follow... are you talking about the original spreadsheet that we can't see?

*** Since last reply I have imported the large flat Excel file and performed
append querys from the master table to populate to two new empty normalized
tables (named owner and property) with same field names. Also setup a many to
many relationship between the both tables. So far so good but it seems like
the only thing missing is the records relationship between both tables
(owner(s) owns property(s) etc.). I hope I am on the right track and just
missing one step.

***As the story goes, for reference I gave the the table wizard a try on the
master file to see if the record relationships match properly through the
datasheet view of the new tables. Seems like it worked but other fields I did
not want were added to the tables as well. This seems like another way to do
what I want?

 

[Larry Linson]

"ksr" wrote

I don't understand what you mean about getting rid of the fields that
follow... are you talking about the original spreadsheet that we can't
see?

*** Since last reply I have imported the large
flat Excel file and performed append querys
from the master table to populate to two new
empty normalized tables (named owner and
property) with same field names. Also setup
a many to many relationship between the both
tables.

If you did that with a "junction" or "intersection" Table, that is, with a
foreign key to both Properties and Owners Tables, then I don't understand
your next statement, and a Table as I just described is, AFAIK, the only way
to establish a many-to-many relationship.

So far so good but it seems like the only thing
missing is the records relationship between both
tables (owner(s) owns property(s) etc.). I hope I
am on the right track and just missing one step.

If you have the Table I described above, you can retrieve the Properties
owned by a particular Owner as follows:

In the Query Builder, add the Owners, Property-Owner Junction, and
Properties Tables. If it does not automatically create the Join lines, click
and drag the key fields from Owners to the Property-Owner Junction and drop
it on the foreign key field to create the Join. Do the same for the
Properties key and Property-Owner Junction.

Click each Join line to highlight it, then Right-click it and choose Join
Properties. Set "All Records from Owners and only those in Property-Owner
Junction that match". Then do the same for the Property-Owner Junction and
set "All Records from Property-Owner Junction and only those in Properties
that match." Drag down into the grid all the Fields of interest, but
specifically the Field you want to use to select which owner.

You can run the Query to see all the matches, but if you enter just one
OwnerID in the Criteria Line and run it, you should see all the Properties
owned by that Owner.

***As the story goes, for reference I gave the
the table wizard a try on the master file to see
if the record relationships match properly through
the datasheet view of the new tables. Seems like
it worked but other fields I did not want were
added to the tables as well. This seems like
another way to do what I want?

I have never used the Table Wizard, as I am clear on what I need to do to
"model the real-world situation" with my databases, so I can't comment on it
"adding other fields." Actually, without knowing details, I couldn't offer
any useful comments, in any case.

Larry Linson
Microsoft Access MVP

 

[Guest]

--
Thanks for your time

ksr

"ksr" wrote



If you did that with a "junction" or "intersection" Table, that is, with a
foreign key to both Properties and Owners Tables, then I don't understand
your next statement, and a Table as I just described is, AFAIK, the only way
to establish a many-to-many relationship.

Larry thanks for your interest in my situation! For clarification I did
setup a junction table called PropertyOwners with OwnerID and PropertyID as
foriegn keys with both keys combining as a primary key.

If you have the Table I described above, you can retrieve the Properties
owned by a particular Owner as follows:

In the Query Builder, add the Owners, Property-Owner Junction, and
Properties Tables. If it does not automatically create the Join lines, click
and drag the key fields from Owners to the Property-Owner Junction and drop
it on the foreign key field to create the Join. Do the same for the
Properties key and Property-Owner Junction.

Click each Join line to highlight it, then Right-click it and choose Join
Properties. Set "All Records from Owners and only those in Property-Owner
Junction that match". Then do the same for the Property-Owner Junction and
set "All Records from Property-Owner Junction and only those in Properties
that match." Drag down into the grid all the Fields of interest, but
specifically the Field you want to use to select which owner.

You can run the Query to see all the matches, but if you enter just one
OwnerID in the Criteria Line and run it, you should see all the Properties
owned by that Owner.

I will check this out and report back.
Will this work when I create forms to display, reassign and update these
tables ?
Thanks kindly ksr

 

[Guest]

--
Thanks for your time

ksr

"ksr" wrote



If you did that with a "junction" or "intersection" Table, that is, with a
foreign key to both Properties and Owners Tables, then I don't understand
your next statement, and a Table as I just described is, AFAIK, the only way
to establish a many-to-many relationship.


If you have the Table I described above, you can retrieve the Properties
owned by a particular Owner as follows:

In the Query Builder, add the Owners, Property-Owner Junction, and
Properties Tables. If it does not automatically create the Join lines, click
and drag the key fields from Owners to the Property-Owner Junction and drop
it on the foreign key field to create the Join. Do the same for the
Properties key and Property-Owner Junction.

Click each Join line to highlight it, then Right-click it and choose Join
Properties. Set "All Records from Owners and only those in Property-Owner
Junction that match". Then do the same for the Property-Owner Junction and
set "All Records from Property-Owner Junction and only those in Properties
that match." Drag down into the grid all the Fields of interest, but
specifically the Field you want to use to select which owner.

You can run the Query to see all the matches, but if you enter just one
OwnerID in the Criteria Line and run it, you should see all the Properties
owned by that Owner.

***Larry, Update !! I might have performed the append query for the tables
wrong because I performed the query you described and now have 1700 records
showing owner fields and values but no property values just property fields.
I will redo the append queries to see if I find something and let you know.
Thanks

 

[Guest]

*** Since last reply I have imported the large
Larry, For clarification purposes I have setup (Tools- Relationships) the
above
many-to-many relationship between the both tables using a junction table
called "PropertyOwnership" with with a foreign key to both Properties and
Owners Tables - both being primary keys.

In the Query Builder, add the Owners, Property-Owner Junction, and
Properties Tables. If it does not automatically create the Join lines, click
and drag the key fields from Owners to the Property-Owner Junction and drop
it on the foreign key field to create the Join. Do the same for the
Properties key and Property-Owner Junction.

Click each Join line to highlight it, then Right-click it and choose Join
Properties. Set "All Records from Owners and only those in Property-Owner
Junction that match". Then do the same for the Property-Owner Junction and
set "All Records from Property-Owner Junction and only those in Properties
that match." Drag down into the grid all the Fields of interest, but
specifically the Field you want to use to select which owner.

You can run the Query to see all the matches, but if you enter just one
OwnerID in the Criteria Line and run it, you should see all the Properties
owned by that Owner.

When I run this query above I only see all owner records with no property
records associated with them. I have repeated the append and query
procedures above a couple of times but still seem to be getting no where.

In hope that I have given you the correct information in regards to what i
am trying to do I will give you the basic "5000 foot view" .

I want to be able to edit/add existing properties with their new/existing
owners and edit/add existing owners and their new/existing owners properties.
This will be the basis of other tables I will adding to track shoreland
zones, building types, plumbing permits etc.

Thanks for any imput ypu may have --ksr

 

[Guest]

It appears that I have resolved the issue by using the table wizard to break
up the imported master table into three tables (Owner, Property,
JunctionOwnerProperty) and recreating the query. Now I see the results that I
want as as well as looking at the the Owner and Property tables in datasheet
view and see the related records of existing data in the subdatasheet. I am
not sure how to test this 100% but at least for now I think I am gaining.

Thanks for your time and heading me in the right direction !

ksr

 

[Guest]

Sorry for the confusion but I have been refering to table analyser and not
table wizard

 

[Larry Linson]

Larry, For clarification purposes I have setup
(Tools- Relationships) the above many-to-many
relationship between the both tables using a
junction table called "PropertyOwnership" with
with a foreign key to both Properties and
Owners Tables - both being primary keys.

And, I am sure, either created a Form to use to populate it, or used a
Query, or populated the junction Table some way.

When I run this query above I only see
all owner records with no property
records associated with them. I have
repeated the append and query
procedures above a couple of times
but still seem to be getting no where.

To debug this problem, start with another Query. Add only the Owners and
PropertyOwnership Tables, join them, and drag down only the Field that
uniquely identifies the Record (PK) from Owners and the corresponding Field
from PropertyOwnership. Make the Join as I described, "All records from
Owners and only those that match from PropertyOwnership" but add in the
criteria of the foreign key from Property Ownership "Not Null" and see how
many Records are returned. Then try it with a criteria of "Null" and see
how many.

If those seem reasonable, try the same kind of thing with just
PropertyOwnership and Properties.

If you get Records returned on both these tries, and they are not "mutually
exclusive," then I should think the Query I suggested would also return
Records.

In hope that I have given you the correct
information in regards to what i am trying to
do I will give you the basic "5000 foot view" .

I want to be able to edit/add existing properties
with their new/existing owners and edit/add
existing owners and their new/existing owners
properties. This will be the basis of other tables
I will adding to track shoreland zones, building
types, plumbing permits etc.

Thanks for any imput ypu may have --ksr

You are welcome. I fear, however, that I haven't been much help so far. And,
I am going to be "otherwise occupied" for the next few days, so my seeing
and responding to posts here may be "spotty," at best. Perhaps someone else
will jump in and be able to assist.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry, I was missing the process of populating the juntion table. With that
resolved I am on to other aspects of this project. This discussion group has
been a wondeful resource. Till next time thanks again and hope you do well !!
ksr