Combining table columns in a query & database normalization

  • Thread starter Thread starter Matthew Brandt
  • Start date Start date
M

Matthew Brandt

Access newbie~

Hello all, conceptually I understand normalization however applying it
is another matter. A quick overview of the project that I'm putting
together: I'm creating a database that tracks properties owned by
customers. The overall goal is to create a query that sorts/groups
property owners by particular areas on a map.

The essential variables for this are:

- ID (customer Id #)
- ParcelNum (Parcel number; a method of giving the map coordinates for
a specific piece of property...5423-260, 3632-021, 2634-124...etc)
- MapCo (map coordinates; township, range,& section)

The parcel number is the essential grouping field. The problem that
I've run up against is that some customers own multiple properties
whereas others may only own one (ParcelNum). I could setup fields for:

- ParcelNum1
- ParcelNum2
- ParcelNum3

However if queried to create a report, the grouping would be tiered:

- 2634-124 (ParcelNum1)
- 3632-0213 (ParcelNum2)
- 5423-260 (ParcelNum3)

What I would like to be able to do is have a query show all of the
parcel numbers in ascending order irrespective of which field it
originated from in the table:

- 2634-124
- 3632-021
- 5423-260

I'm sure I'm missing something simple. A nudge in the right
direction along with any database terminology to add to my knowledge
base would be happily appreciated.

~ Matthew Brandt
 
Use three tables. A Customer table, Parcel table, and a junction table. The
Customer and Parcel tables would have a primary key filed. The junction
table will have foreign key to match both the Customer and Parcel table
primary keys.
There will be a one-to-many relations from both the Customer and Parcel
table and the corresponding junction table foreign key.
 
The parcel number is the essential grouping field. The problem that
I've run up against is that some customers own multiple properties
whereas others may only own one (ParcelNum). I could setup fields for:

- ParcelNum1
- ParcelNum2
- ParcelNum3
Click to expand...

That's the Wrong Way to Go. "Fields are expensive, records are cheap"!

If each person can own multiple Parcels, then you need two tables in a
one-to-many relationship: a table of Customers (with no parcel
information), and a table of Parcels, with a CustomerID field as a
link.

If a given parcel can have joint ownership by more than one customer,
you need yet another table...

John W. Vinson[MVP]
 
John,

Can you show me a break out of what you mean? The two table approach; a
tblCustomers (using their ID number as the key) and a tbleparcels makes
sense. You're correct in guessing that multiple customers can own the
same piece of property.

How would I setup my 3rd table to represent this? Do you have a link to
a resource that you could recommend?

~ Matthew Brandt
 
Let me share what I've come up with so far. I stumbled upon this list
of data relationship types
(http://databases.about.com/cs/tutorials/a/accessgup7.htm)

-One-to-one relationships occur when there is exactly one record in
TableA that corresponds to exactly one record in TableB.

-One-to-many relationships occur when each record in TableA may have
many linked records in TableB but each record in TableB may have only
one corresponding record in TableA.

-Many-to-many relationships occur when each record in TableA may have
many linked records in TableB and vice-versa.

I am working with a many-to-many relationship model. I post again as I
continue working through this.

~ Matthew Brandt
 
As Karl Dewey wrote, you need three tables:

tbl_Customers
CustomerID (PK)
CustomerInfo

tbl_Parcels
ParcelID (PK)
ParcelInfo

tbl_ParcelAllotments
ParcelAllotmentID (PK)
CustomerID (FK)
ParcelID (FK)
ParcelAllotmentInfo

Use the relationships window to draw relationship lines between the
CustomerID's and the ParcelID's. This will automatically set up the
one-to-many relationship that you need. You will then make a query based
upon all three tables, and then a form based on that query. The form will
probably be a form/subform, and you will probably show the customer in the
form, and then the parcels in the subform (that will be the parcels belonging
to that particular customer).
 
John,

Can you show me a break out of what you mean? The two table approach; a
tblCustomers (using their ID number as the key) and a tbleparcels makes
sense. You're correct in guessing that multiple customers can own the
same piece of property.

How would I setup my 3rd table to represent this? Do you have a link to
a resource that you could recommend?

~ Matthew Brandt
Click to expand...

See Karl's and mnature's excellent replies.

John W. Vinson[MVP]
 
I plugged the format into my db design that you all suggested and it
worked great. The concept of creating a many-to-many db design was
confusing. Once I was able to play with the correct linking structure
it made sense. Thank you all, I would by you a beer if I could.

Cheers,

Matthew Brandt
 
Back
Top