highways maintenance db

[nd908]

Help!!!

I'm using 2007

i need to get something put together to record highways maintenance. I'm
struggling with how many tables I'll need.
The information I need to record is: Road name, location (outside number 23
for example), fault, required action,priority (yes/no), date passed to
highways, date completed, time to complete in working days, time to complete
in total days.

I was hoping to be able to do all this in one table if possible.

any help appreciated

 

[Jeanette Cunningham]

nd908,
You need a table to hold the road name and a table to hold the location and
a table to hold the assessments of fault details.
Each road can have many locations.
Each location can be assessed for faults many times.
Each fault will have action required, dates and times etc.

Roads are related one to many to locations.
Locations are located one to many fault assessments.

These 3 tables below would all be related to the table for fault
assessments.
I would also create a table with a list of typical faults which can be the
recordsource for a combo of fault types.
Another table for actions which can be the recordsource of a combo of
actions.
Similarly for a list of priorities.
The table for fault assessments would have an ID from the table of what's
wrong, an ID from the table of actions and an ID from the table of
priorities.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[nd908]

Hi Jeanette

thanks for the help, I'll give it a go.

 

[Wayne-I-M]

On the face of it all the information you "need" to do the job could go into
1 table

The items you mentioned all refer to the specific task (so one table)

ie. tblTask

taskID = autonumber
Address1 = text (ie. New York)
Address2 = text (Road Name)
Location = text (ie. Next to the big building)
Fault = text (ie. big hole in road)
Action = text (ie. fill in hole)
Priority = Yes/No
DateStart = Time/Date (ie the date you start work)
DateEnd = Time/Date (ie the date you end the work)
DatePassedd = Time/Date (ie the date the task with passed as being OK)

You don't need
Time to complete in working days
Time to complete in total days.
As both of these can be done in a query on on a report/form

BUT

This is not the end of it. The post you made refered to a specific task but
(as Jeanette refered to) this task will need to be linked to other items
(tables)
Who is going to do the task
tblWorkers

How are they going to do the task
tblFacilties

This is not to mention payments, logisitics (transport, other agencies -
police, fire, - and the UK anyway Health and Safety - etc),Equipment (is the
equipment being used elsewhere)

Etc
Etc

So the basic recording of information could be done in one table but to
create a "workable" application you are going to either spend a lot of time
or get someone else to make it for you. Sorry about that

 

[scubadiver]

On the back of Wayne's response, it really depends on how complicated you
want the database to be. Ask yourself what you really need to get out of it
and work backwards.

 

[nd908]

thanks for replying, wayne

all i really need to do is keep a track on how long each job takes. I dont
need to know who does the work, or which resources they use.

I'll need to be able to call up street names to see what work has been done
there in my reports, but thats pretty much it.

i dont need to list any payment methods or external organisations.

Basically this is just for my own records, so i can see what work has been
ordered and how long it took to complete.......

fancy doing it for me, wayne? ;-)

 

[nd908]

as simple as possible

 

[Wayne-I-M]

fancy doing it for me, wayne? ;-)

Nope

******************

There are some people on this forum who would look at it for you though. If
you post your requirements then someone will get back to you. Most DB's can
be created from a distance so you don't have to live near them.

Mind you - if it's only for your own information - a single table DB would
be a good way to start learning access so you may to give it try and see what
happens ??

 

[Larry Daugherty]

Your goal of a single table for your application built in MS Access is
fundamentally flawed: Access embodies a whole bunch of RAD
functionality bundled with a Relational Database Management System.

An RDBMS is excellent for data management. It is assumed that there
will be a well organized schema (Tables and Relationships) on which
higher level elements (Queries, Forms and Reports) will rely.

Fundamental to arriving at a good schema is the analysis of the
proposed application and the identification of the entities in play
within it. Each entity is then given its own table (tblRaod for
example). Every instance of that entity belongs in that single table.
So the rule is that you let the application determine the number of
tables.

If you arbitrarily limit the number of tables in your application then
you'll set yourself up for continually typing in repetitive data with
the risks of typing errors. Further,you'll limit or make very
difficult the achievement of higher level things you might do to make
your application more useful.

Jeanette suggested some tables to get you going in the right
direction. There is an unstated assumption that you will also build
Forms and Reports (and their underlying Queries) to complete your
application. By starting on the correct path you'll develop an
application that will be extremely useful and worthy of enhancement
over the years. For example, you might want to create Reports that
show: Jobs entered between any two dates, Jobs completed between any
two dates, Jobs of a given type between any two dates, Average time
from Job Reported to Job Scheduled, same for Job Completed. Trending,
....

If you are determined to go with a single table solution then it is
unlikely that Access is the best tool for you. Why bother with the
overhead of Access if you intend to use it like a spreadsheet? My
suspicion is that you intend to manage your data directly in the
tables. That is the worst possible use of Access. If you build a
Form on that single table and use it for data entry and management you
can gain some of the protection available if you'll research and use
it.

But, if you hope and plan to stay with a single table and to manage
the data at a low level then I earnestly suggest that you use Excel.
You can easily create a data form based on your data and use it or you
can enter data directly in the single table. The allowable size of
Excel has been extended so you'll have plenty of room.

HTH

 

[nd908]

Hi Larry

I'm not determined to make the db consist of one table only. I just want to
keep it as simple as possible, I'm very new to access and to be honest, your
reply went over my head a little.

I already use excel for this, i just thought access would make it a little
'tidier' and being able to run the reports will save me a lot of time
currently spent auto filtering etc.

It's not turning out as simple as I'd hoped it would be...

 

[Larry Daugherty]

Access has a fairly long and steep learning curve. My suggestion to
you about using Excel wasn't a put down, it was an earnest suggestion
given what seemed to be your priorities.

Access is most definitely a far better tool than Excel for data
management. If there is much data management in your job or your life
that isn't supported with good tools then the investment in learning
to properly use Access is worthwhile.

The biggest issues that most people face in coming to Access from
Excel are: 1) Access and Excel are not simply flawed versions of
each other. While they share VBA and a lot of Microsoft's user
interface and usability features, they are *different*. 2) Access
works best when *Normalization* and other *Relational* concepts are
understood and practiced.

To get a solution that is easy to use yet provides all of the
functionality you need requires that you do a good job in your design
and implementation. It won't happen by accident nor is it readily
done piecewise as is usually the case in Excel data solutions. The
bulk of the work designing and deploying an Access application comes
up front. Thereafter an untrained user can use it without having to
know the internal workings.

As to "simpler", it's the application that determines the level of
complexity. The solution has to match and handle the complexity of
the target. Since you're learning a new (Relational) way of dealing
with entities as well as trying to learn a new technology it will all
seem complex and frustrating. MVP Crystal (strive4peace) provides a
free tutorial on her website that's useful for newbies. You can find
the URL in her signature line in her posts.

The Northwind application that comes with retail Access can give you
some clues. It's much more complicated than your proposed application
but the concepts exposed are similar. By the way, the programming
practices in Northwind aren't always the best.

Good luck.

HTH

 

[Wayne-I-M]

Hi Larry

Everything you say is tottaly correct "but" in this case (any many others)
the 1st attempt at creating a database with access will, of course, not be
ideal. Most people on this forum would never consider creating the
application in one table with a couple of querys and maby a report or 2.
Thats not what access is "for" - so we keep getting told. The fact tht
access "can" do much more is not a good reason to force it to.

Basically this is just for my own records, so i can see what work has been

ordered and how long it took to complete.......

Is a very telling bit of the post


If someone is trying to create a single table DB - fine, this is an
excellent start on the learning curve.

I really think that your answer was slightly over the top in that it has
almost certainly talked someone out of even starting on the learning curve -
if not this person then maybe other people reading your post.

I think that the answer (in this case) would be to create a single table
containing the details of the specific task. If they end up typeing the same
name of a road more that once - again thats fine - but this is the start of
the learning curve.

If it were you or another expert creating the DB then you would do it
differently, maybe even 2 tables , but in this case .....

 

[nd908]

Nope

how do you sleep at night?

 

[nd908]

thanks to everyone for the help.

I'm going to start with one table and develop it once i know a bit more.
It's only for my own records so it doesn't need to be the best db ever
designed.

thanks everyone who repled

 

[Mike Barnard]

Hi Jeanette

thanks for the help, I'll give it a go.

Hey, 908. Your email address bounces. Can you contact me please? the
address is in the headers, you just need to delete .trousers. (It
seemed funny at the time!)

I'm doing something similar to you.