Access 2002 design help!! Mr. Ferguson or Mr. Boyce

[Boomer]

This is from a previous post.......Lets back up all of
these fields came from 3 different forms. This is how it
works we get a call in from someone about a private or
commercial site that has waste and trash on it. The
fields I have written out are all questions on the 3
forms. I divided them into the 9 tables trying to get
them lined out. It didn't work so here are all the fields
from the 3 forms again how do I put them into tables so
this will be a relational database ? I went back to the
pencil and paper and Iam getting confused ???

1. Location/Name= This is the name of the road or a
persons name
2. Incident # = the number that it assign as they
are called in
3. Directions from the court house= name of road
where site is
4. Latitude and Longitude this is a question
5. Dump Size = (ft) Width x (ft)Length x (ft)
Depth = (ft)to the 3rd power volume How do I make this a
field?
6. How Many Single Axle Dump truck loads ?
7. Is there a well within 500 ft. of an open dump ?
(this is a yes no box)
8. Date Dumpsite Identified
9. Date to Clean
10. List any local schools or historic sites
11. Responsible Party (yes/no)
12. Enforcement actions Underway (Yes/No)
13. County
14. Nearest road

Contents (All Yes/No Boxes) Do I make this a lookup box
for all this?
1. Household
2. CD/D Building Material
3. CD/D Concrete
4. Brush/Landscape
5. Appliances
6. Tires
7. Hazardous Material
8. Other / List


Accessibility is another question with Y/N Boxes make
this lookups ?

1. Paved
2. Gravel/dirt
3. steep slope
4. trees surrounding site
5. water surrounding site
6. other/ List

Type of setting is another question with these
selections Y/N Boxes .

1. Public Property
2. flood plain/floodway
3. open field
4. sink hole
5. cliff
6. hillside
7. woods
8. creek/river
9. old quarry pit
10. roadside
11. other/list

Digital Photos Table I think I understand how to do
this? I found a picture example within the Northwind
Database VB code under event procedure...i will work with
it. may have questions later

Equipment Cost is another field with these questions for
the surveyor
1. Equipment Type has multiple listings under it
like bulldozer 8 different kinds also dump trucks,
backhoes just depends on what is needed to cleanup sites
2. Max Hourly Rate = how much we charge for our
equipment use
3. actual hourly rate= same as max
4. hours used.
5. total= a formula of actual x hours used

Disposal Fee = when the site is cleaned and the waste is
hauled off and dumped at the main dump site (which by the
we have a contract with them) .
1. Facility Name and location = where we dump it at
2. Tons estimated = how much the load is on scales
3. Per ton cost =
4. total

Labor Cost = this part of the form is where we figure our
labor cost for the job site.
1. Employee Title= this is made up of 4 different
titles do I put this in a lookup box?
2. estimated work hours
3. hourly wage
4. total

Miscellaneous Supplies
1. Specific Item
2. quantity
3. per unit cost
4. total


Please help me with any advice as to line these out...
Thanks John

 

[Tim Ferguson]

It didn't work so here are all the fields
from the 3 forms again how do I put them into tables so
this will be a relational database ? I went back to the
pencil and paper and Iam getting confused ???

I think I remember something about this before, and suggested some of the
key entities you would be likely to need. Some thoughts on this post:

It seems that you are starting from your existing forms. Although you will
need to take some notice of them in due course, it is nearly always a bad
place to start from. As I indicated last time, you need to be thinking
about the "things" you are involved in and how they relate to each other.

1. Location/Name= This is the name of the road or a
persons name

Two different things: presumably the road belongs to the Dumpsite, the name
belongs to a Person.

2. Incident # = the number that it assign as they
are called in

This belongs to an Incident, surely?

3. Directions from the court house= name of road
where site is

Presumably this belongs to a Dumpsite. How is this different from (1)?

etc etc...

Digital Photos Table I think I understand how to do
this? I found a picture example within the Northwind

Not a very good idea. Pictures are very poorly stored in Access and rapidly
lead to oversized mdb files. See Dev's site for advice on how to display
pictures in a database <http://www.mvps.org/access>.

etc. etc.

One good way to start modelling is to create a piece of paper (or a Post-It
note) for each entity and write the attributes you want to keep on each of
them; and then start drawing lines between them for how they interact. For
example, you would have a line between Dumpsite and Contract, and then one
from Contracts to FeesPaid, and Contracts to LabourCosts and so on. Take a
long time over this: about 75% to 90% of your overall development time is
common, and don't go near a keyboard or start Access until you have all
this straight.

I have to say that this does not look like a trivial project, especially
for a beginner. Have you considered the costs/ benefits of external paid
help? No, I am not touting for business as I have a real job to go to! We
are always happy to support specific questions here, but there is going to
have to be a _lot_ of information gathering in order to understand your
business scenario well enough to design it at a distance.

Hope that helps


Tim F