My database is all messed up

[Tomtheappraiser]

Ok, I work for the local assessors office, where computers are considered a
futuristic device. Literally, all work is done with a paper and pencil. So
when I wnet to work there, I bvrought my laptop and began creating a database
to help my productivity. I had NEVER used access before and tried to build a
database off the help files alone. But I could never wrap my head around
realtionships, so I ignored them.

Now, my database has run out of available fields that I could create on the
one table that serves my dtabase, along with the 8,000 records in it.

So I tried taking the online courses and they don't help at all. Maybe
someone here can help me.

My main table (I know it should have all kinds of seprate tables) has a
primary key that is an auto number ID. The table contains the records of all
the commercial properties in my district. This includes physical data
(building and lot size, number of apartment, retail or office units), sales
data (sale price, sale date, price per square foot) , financial data (leases,
loan terms, etc) and other pertinant information about the properites.
Now, one would think that a parcel number would be the unqiue identifier,
but properties are combined, divided and sold, so there are multiple records
with the same parcel number. So I stuck with the autonumber ID.

Now I need to create a form for recoding building permits. But since I ran
out the maxinum number of fields avaiable in my main table I needed to creat
a table just for fields for building permits. Which forces me to learn to use
relationships. I tired creating a table with "Permit Number" being the
primary key. My new table included the fields "project type", "estimated
cost", "Change in Value" (Yes/No), and "amount of change in value"
(Currency), "Date of Permit".

So on my form I created I needed the following fields from my main table:
"Parcel Number", "Address" , "Square footage".

I created a relationship of one to many with the main table as the parent
and the BP table as the child. Iused the ID field from the parent to connect
to the child table. (adding an ID field with auto number in the BP table).

But now when I go into enter date into the form. All of my data from the
main table is there, but I can't enter data into the fields from the BP
table. and the Permit Number field has "?Name" in the data box.

Can someone help me fix this? I think by doing this one thing it will help
me understand this much better. I intend to divide all of my data from my
main table into the correct tables at a later date as soon as I get a grip on
this relationship stuff. But, for right now assume I know NOTHING about
access in your explanation. I need step by step help.

 

[Jeff Boyce]

Based on your description, you need to (first) return to paper and pencil.
No, really!

If you have had no experience with relational database design or
normalization, get it before you proceed. The likely reason you "ran out of
available fields" is because you tried to treat Access like a big
spreadsheet. You won't get the best use of Access' relationally-oriented
features and functions if you feed it 'sheet data.

Do an on-line search for "normalization" and spend some time with paper and
pencil diagramming out the "entities" and "relationships" inherent in your
situation.

There are TWO rather steep learning curves in getting the most out of
Access. The first is wrapping your head around normalization. The second
is learning how Access works.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

Ok, I work for the local assessors office, where computers are considered a
futuristic device. Literally, all work is done with a paper and pencil. So
when I wnet to work there, I bvrought my laptop and began creating a database
to help my productivity. I had NEVER used access before and tried to build a
database off the help files alone. But I could never wrap my head around
realtionships, so I ignored them.

Well...

that's like saying "I could never wrap my head around changing gears on my
manual-shift car so I just drive in first gear".

I fully agree with Jeff; you really need to step back, learn how Access works,
and then work WITH the program rather than struggling against it. You'll find
it works much much better. Check out some of the resources at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

It is a steep learning curve and requires changing the way you think about
some things, but once you get the concepts it will be VERY useful.

You may want to see if you can scare up a bit of budget to hire professional
help; when you do, make sure it's a professional who will help you learn the
concepts and understand the internal structure of the database, not one who
will just present you with a black-box and a nebulous promise of future
support.

John W. Vinson [MVP]

 

[John W. Vinson]

So on my form I created I needed the following fields from my main table:
"Parcel Number", "Address" , "Square footage".

I created a relationship of one to many with the main table as the parent
and the BP table as the child. Iused the ID field from the parent to connect
to the child table. (adding an ID field with auto number in the BP table).

I'll still stick with my previous answer, but... to fix this specific problem,
change the ID field for the BP table from Autonumber to Long Integer. You
*CANNOT* join one autonumber to another autonumber, it just doesn't work that
way. You can use a Form based on the main table with a Subform based on BP,
joining the autonumber ID to the child table long integer ID.

John W. Vinson [MVP]

 

[Fred]

I think that you described your main specific mistake but that the other guys
missed the core of it. The ID (for linking) in your BP table has to be the
ID of the property that it is for, not some new ID number for the permit.

Fred

 

[Pat Hartman]

So you are starting the first of many 1-m relationships. there is one piece
of property but there may be more than one permit associated with it.
The permit table needs its own autonumber primary key. The permit table
contains a long integer column that will contain the key value of the record
to which it points. You presently are trying to establish a 1-1
relationship with potentially multiple active permits for a single piece of
property.

As your second step on the road to normalization, pull out the sales data.
A piece of property may be sold multiple times and so you need a separate
table to record information relating to the sale of a piece of property.

As others have already advised, sit back and sharpen your pencil.
Personally, I like using a white board since you have lots of room and you
can erase things and move them around. If I don't have a white board, I use
sticky notes. You need to separate in your mind what is an entity (thing
about which have information) and what are attributes (pieces of information
about a thing). The easy one in your case is a piece of property.
Attributes are - address, parcel number, appraised value, etc. You will
also find that some attributes may occur more than once like owners. A
piece of property may have co-owners and you can't predict how many they
will be. Those owners have their own attributes such as address and phone
number. Then there are transactions such as sales and assessments. Each of
those would be in a separate table since they occur many times over time.
Of course, if you don't need history, you can keep some things in the table
for example, there is only one current sale price. However, there could be
multiple current assessments.

You have a lit to think about - have fun.