Setting up building permit form ?

[ksr]

I am currently setting up a Access 2000 database that will track
property in regards to building and plumbing permits, shoreland zones,
subdivisons etc.. I have the following major tables setup as follows:

Owner Table
*Field*
Name OwnerID (Primary key- autonumber)
Lastname
Firstname
Street
City
State
Zipcode
Telephone

Property Table
*Field*
PropertyID (Primary key- autonumber)
OwnerID (foriegn key to OwnerID - OWNER Table)
CurrentOwnerLastname
CurrentOwnerFirstName
PhysicalAddress
TaxMapLotNumber
DeedBookPageNumber
Acres

(Juntion table for the many to many relationship between owner and property
created at the table level)
LINKProperty_Owner Table
*Field*
PropertyID (composite foreign primary key)
OwnerID (composite foreign primary key)

BuildingPermit Table
*Fields*
BuildPermitID (Primary key- autonumber)
PropertyID (Foreign key to Property Table PropertyID
DateFiled
ApplicantLastName
ApplicantFirstName
ApplicantAddress
ApplicantCityTown
ApplicantZipcode
OwnerLastName
OwnerFirstName
OwnerAddress
OwnerCityTown
OwnerZipcode
TypeOfStructure
PlumbingPermit
OwnerOccupied
DeededRightOfWay
DistanceFromPropertyLines
etc...etc

BuildingPermitDetails Table
*Fields*
BuildingPermitDetailsID (primary key autonumber)
TypeOfApplication
ApplicantID (foreign key to Applicant table-ApplicantID)
ActionTaken
DateOfAction
etc...etc

PlumbingPermit Table
*Fields*
PermitNumber (primary key text value)
ApplicantID (foreign key to Applicant table-ApplicantID)
TypeOfSystem
DateOfAction
SepticTankSize
SepticFieldSize
SepticSystemAge
ActionTaken
DateOfAction

Applicants Table (sometimes different than owners- ex. family members)
*Fields*

ApplicantID (Primary key- autonumber - Parent key to
PlumbingPermit BuildingPermitDetails tables corresponding keys)

Lastname
Firstname
Street
City
State
Zipcode
Telephone

I have setup forms for owners and properties based on their corresponding
tables for update/add/re-assign owners to properties. This seems to be
working fine.

What I would like to do next is to setup a building permit form that the
user would be able to select/add applicants, owners and properties along with
its building permit fields and details (re:- building permit details) I am
not sure how to approach setting this building permit form in regards to
using all the information. I have looked into designing the form based on a
query and utilizing subforms but still getting no where and need some
direction or hints.

Any ideas would be helpfull and much appreciated as always.

 

[Larry Daugherty]

I suggest that you have a table that holds the Business name entity.
That could be a developer and would most usually be a business but
could also be a tax court or any other legal entity that may hold
property.

There should be a table Person that will hold all of the common
information about every human being within the scope of your
application no matter their role.

Most people starting out create business applications have difficulty
separating customers/businesses from people/contacts. In most
business applications they're different. One of my customers was Ray
Marchand Oil Company. My chief contact there was my friend Ray
Marchand.

Your Owner table isn't needed. You have the Owner attribute covered
in the Property table.

In the Property table the only thing you need to capture about the
Owner is the Primary Key in tblBusiness as the Foreign Key. With that
information you can get everything else you need whenever you need it.

You don't need LINKProperty_Owner Table.

tblBuildingPermit leaves me guessing that an applicant is a Person so
the PrimaryKey of the proper Person would go here. For Owner, get the
Primary Key of the proper Owner and put it here. No other info about
Applicant or Owner seems to be required.

Your structure looks a bit confused in the permit details so I won't
address it.

I suggest that you re-design per the above recommendations, try that
much and see where you stand.

A good warning flag is that if you find yourself entering the same
data repetitively (more than once!) in an application you should stop
and think.

There are a couple of other good newsgroups for getting started: not
too surprisingly one of them is:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

If you haven't yet visited www.mvps.org/access you should do so early
on. It's chock full of Access lore and keeps growing.

HTH

 

[ksr]

Larry,

Thanks for your time and response . I need to go back to my regular work
tommorrow but will go over your suggestions in further detail tommorrow night.

The only reason I used the LINKProperty_Owner Table is that I might have the
possibility of multiple owners to one property and multiple properties to one
owner
so I was establishing a many to many realtionship ?
--
Thanks for your time

ksr

I suggest that you have a table that holds the Business name entity.
That could be a developer and would most usually be a business but
could also be a tax court or any other legal entity that may hold
property.

There should be a table Person that will hold all of the common
information about every human being within the scope of your
application no matter their role.

Most people starting out create business applications have difficulty
separating customers/businesses from people/contacts. In most
business applications they're different. One of my customers was Ray
Marchand Oil Company. My chief contact there was my friend Ray
Marchand.

Your Owner table isn't needed. You have the Owner attribute covered
in the Property table.

In the Property table the only thing you need to capture about the
Owner is the Primary Key in tblBusiness as the Foreign Key. With that
information you can get everything else you need whenever you need it.

You don't need LINKProperty_Owner Table.

tblBuildingPermit leaves me guessing that an applicant is a Person so
the PrimaryKey of the proper Person would go here. For Owner, get the
Primary Key of the proper Owner and put it here. No other info about
Applicant or Owner seems to be required.

Your structure looks a bit confused in the permit details so I won't
address it.

I suggest that you re-design per the above recommendations, try that
much and see where you stand.

A good warning flag is that if you find yourself entering the same
data repetitively (more than once!) in an application you should stop
and think.

There are a couple of other good newsgroups for getting started: not
too surprisingly one of them is:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

If you haven't yet visited www.mvps.org/access you should do so early
on. It's chock full of Access lore and keeps growing.

HTH

 

[Larry Daugherty]

That may be the better way.

--
-Larry-
--

Larry,

Thanks for your time and response . I need to go back to my regular work
tommorrow but will go over your suggestions in further detail tommorrow night.

The only reason I used the LINKProperty_Owner Table is that I might have the
possibility of multiple owners to one property and multiple properties to one
owner
so I was establishing a many to many realtionship ?

 

[Arvin Meyer [MVP]]

You only need to store the OwnerID and ApplicantID in the Permit table.
Storing addresses and phone numbers in the Permit table is redundant
(therefore unnormalized). In cases where there are multiple unincorporated
owners, use a separate many-to-many table with the PermitID and an OwnerID
as a compound Primary Key.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[ksr]

Arvin,

Thanks for your time.

If I understand you correctly on what is being suggested I would combine the
tblOwners and tblApplicants I have now into one table called tblOwnership and
have a lookup list/table attached with the following classifications.

applicant
owner

tblPersons would still continue to have a many-to-many-relationship to
tblProperty via
tblLINKProperty_Owner Table. This would satisfy the possibilty of multiple
owners to one property and visa versa.

The building permit table I was thinking is like a workorder because it
relates to activities happening to the property (aka house building, septic
system, new garage, addition, decks, does it create a sub-division etc..) .
So I was thinking that the PropertyID is the key field that ties the
tblBuildingPermit to tbleProperty.

As far as using a separate many-to-many table with the PermitID and an
OwnerID as a compound Primary Key I am not sure how this would work in this
scenario.

I wish there was an example db that would provide a similar the realtionship
picture for me like a rental car business etc.

Any suggestions are always appreciated.
--
Thanks for your time

ksr

You only need to store the OwnerID and ApplicantID in the Permit table.
Storing addresses and phone numbers in the Permit table is redundant
(therefore unnormalized). In cases where there are multiple unincorporated
owners, use a separate many-to-many table with the PermitID and an OwnerID
as a compound Primary Key.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

No, don't combine your existing structure, add to it. There is a
many-to-many between owners and property, and a many-to-many between
applicants and property. Additionally, an owner can be an applicant.

Most construction has a general contractor and sub-contractors. In small
jobs, the sub or owner can be the permit holder, but usually the general
contractor is the permit applicant. In the homebuilding industry, the
general contractor is usually the owner and permit applicant. In the
commercial construction industry, the owner is rarely the contractor, and is
often the permit holder.

Having built databases for 3 of this country's largest 15 homebuilders, and
several sub-contractors, both very large, and small, as well as commercial
contractors, I have quite a bit of experience in this area. Different
companies have different philosophies, which definitely affects the way
databases are written. Feel free to ask specific questions in this forum
about how to handle specific tasks.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

Thanks for your time.

If I understand you correctly on what is being suggested I would combine
the
tblOwners and tblApplicants I have now into one table called tblOwnership
and
have a lookup list/table attached with the following classifications.

applicant
owner

tblPersons would still continue to have a many-to-many-relationship to
tblProperty via
tblLINKProperty_Owner Table. This would satisfy the possibilty of multiple
owners to one property and visa versa.

The building permit table I was thinking is like a workorder because it
relates to activities happening to the property (aka house building,
septic
system, new garage, addition, decks, does it create a sub-division etc..)
.
So I was thinking that the PropertyID is the key field that ties the
tblBuildingPermit to tbleProperty.

As far as using a separate many-to-many table with the PermitID and an
OwnerID as a compound Primary Key I am not sure how this would work in
this
scenario.

I wish there was an example db that would provide a similar the
realtionship
picture for me like a rental car business etc.

Any suggestions are always appreciated.
--
Thanks for your time

ksr

 

[ksr]

Arvin,

Is there a way I can send you the relationships view that I now have so you
can see clearly what I have instead of typing pages of pages of text to
explain?

I hope this is not asking to much ?
--
Thanks for your time

ksr

 

[Arvin Meyer [MVP]]

Compact your database, then zip it up. Make sure it is less than 5 MB after
zipping. Email it to:

newsgroup at accessmvp.com

I'll have a look and try and make a suggestion or 2.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[ksr]

Alvin,

I just emailed the db to (e-mail address removed) your attention

--
Thanks for your time

ksr

 

[Arvin Meyer [MVP]]

Karl,

I purposely didn't use a readable email address to avoid it being mined by
spammers. If it starts getting spam. we'll know why, and I'll have to
destroy it.

As to your database. I see a few things which can be improved:

1. Former owners. All people belong in the same table. In this case, you
could have a many-side table for all former owners (i.e. keep a history of
ownership), or if it is only important to keep the last owner, just add a
second combo box to look at the owner's table, and store the OwnerID and the
FormerOwnerID in 2 fields of the property table.

2. Driveways and structures are part of a property. Since there can easily
be more than 1 on a property, I can see the necessity for the additional
tables. But you don't have a property key in the many-side tables.

3. You are using Lookup fields. For reasons not to, see:

http://www.mvps.org/access/lookupfields.htm

Use a query instead. A simple single combo box in a subform will provide you
with the data you need for your link (man-to-many) table.

4. Watch your field names and spelling. It will make your fields and labels
more professional looking.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[ksr]

Arvin,

I very much appreciate your input!

I am finding Access to be somewhat addictive, fun,challenging and but
definitely humbling.

I will email you my contact information using your Datastrat email
so that I might somehow return the favor to you someday if you ever need
help with Novell systems (my specialty).

Best regards,

Karl



--
Thanks for your time

ksr

 

[ksr]

Arvin,

Sorry my mistake. I just realized what I did in regards to the email address.
I am now aware and will be careful in the future. I hope this does not cause
any problems for this forum.
My appologies

karl