Design Assistance Needed

[J. McGonigal]

I have been given the task of replacing our old program for report writing.
Unfortunatly my knowledge in Access is limted to none. If anyone is willing
to provide assistance it would be greatly appreciated.

A screenshot of what needs to be replaced can be found here:
http://home.comcast.net/~jmcdougal02/SOC.JPG

All of this information must be entered into whatever new database I come up
with. The following "fields" need to have information already entered and
allow a user to select the correct entry:

Incident code, Area, Camera #, Camera location (need to somehow be linked so
when a # is selected the location is also filled in), color, state, make, and
assist by (which would be an employee list of names).

The other information will be filled out by the report writer, except for
the CCTVN which will just be an autonumber.

Can anyone provide assistance in laying this project out? I aplogize if any
of my terminology was wrong, but like I said my knowledge in this area is
very limited.

Thanks,
J. McGonigal

 

[nanne]

I can help you for a small fee

 

[J. McGonigal]

How much are we talking? I am currently a student so I can't afford much, and
the job won't give me any extra for this project.

Anyone else out there can help?

 

[Ken Snell \(MVP\)]

If you are very unfamiliar with ACCESS, and perhaps with relational
databases in general, you need to first become more familiar with how to
design a database in terms of identifying which tables you'll need and which
fields you'll need in them.

In a database, you begin by identifying all the "things" that need to be
represented (the tables), and then identifying theh "properties" that each
"thing" will need (the fields).

From your initial description, you will need a table for your cameras and
their locations:

tblCameras
CameraID (primary key)
CameraLocation
(etc.)

You will want to break up into separate data items each aspect of a
location, for example. You might be specifying a building, a floor, a
direction (e.g., northeast), etc.

You then will need a table of "operators":

tblOperators
OperatorID (primary key)
OperatorFirstName
OperatorLastName
(etc.)

You then will need a table to store the incidents that you are "reporting":

tblIncidents
IncidentID
CameraID
OperatorID
IncidentDateTime
IncidentReason
(etc.)

In the above table, CameraID is a foreign key to tblCameras, and OperatorID
is a foreign key to tblOperators. This allows you to access the information
about each entity through queries because the "join" uses the foreign and
primary keys to allow you to "look up" information without having to store
it more than once in the table.

You also may need tables to record details about the incident where there
can be more than one entry:

tblIncidentsCars
IncCarID (primary key)
IncidentID
CarType
(etc.)

tblIncidentsPersons
IncPersonsID (primary key)
IncidentID
PersonType
(etc.)

And I'm sure that there'll be more tables that you'll need.

As you begin designing the tables, you'll find that you'll need other tables
to hold "common" data (e.g., the number of doors from which the user can
choose for a car, the sex of a person, the types of clothing, etc.)

What you want to set up as a database is not a hugely challenging task in
this case, but it is more challenging than might appear from just a single
screen shot. Time spent now on learning about database design concepts and
working with paper and pencil will more than reward you with how well the
database works later and how well it allows you to do what you want (or to
add new things to it).

 

[Ken Snell \(MVP\)]

nanne -

I can help you for a small fee

These newsgroups are for free, peer-to-peer assistance, not for soliciting
work. Although a poster may specifically request people to respond to do
work for a fee, it's very "gauche" and unprofessional to reply as you did.

 

[LeAnne]

I suspect "nanne" may be an alias for PC Datasheet...

L.

 

[J. McGonigal]

First I want to thank you for your assistance so far. Can you please explain
the primary key and how it is used. I had already had a basic database but
wasn't sure if it is functional. Let me explain what I have so far.

I have 13 tables total.
tblArea of Campus
Area -(lists the 10 or so areas that you should be able to select from)

tblCamera
Camera# (number associated with the camera)
Camera Location (general location of camera)

tblEmployee Information
Title (Mr, Ms, etc.)
First Name
Middle Name
Last Name
Birth date
Employee ID
Sex
Race
SSN
Email
Phone #
Then several other yes/no fields (is fields the correct term here?)

tblIncident Code
Incident code (50 or so # to describe situation, like police 10 codes)
Description (actual description for code)

tblLocation
Location (will be a long list of buildings, rooms etc.)

tblRace
Race
tblSex
Sex

tblStates
Abbreviation
State

tblVehicle Color
Color

tblVehicle make
Make

tblVehicle type
type

tblReport
report # (auto generate)
case number
date
time
camera #
camera location
incident code
location
synopsis
etc. basically all the info from the orginal screenshot, its using a lot of
lookups from other tbls to provide "menus" to fill in the field.

Am I anywhere near the right path here?

Thanks

 

[Lynn Trapp]

I suspect "nanne" may be an alias for PC Datasheet...
I had that same suspicion myself.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[mscertified]

Primary key is a field or fields in a table that uniquely identifies a row.
An example would be an employee id or an equipment serial number.
If a table does not have a 'natural' primary key, you can set up an
autonumber field that auto incremnents to create the unique key.

This forum can offer advice for specific questions but don't expect it to be
able to teach you Access from scratch or to design a complete database for
you. You will need to do some reading and/or training on your own.

-David

 

[Arno R]

I had that same suspicion myself.

Hmmm,
I don't think nanne *is* PCD. I am even afraid it is a compatriot of me...
Look at he mail address (e-mail address removed) and the headers.
Originating from Holland so it seems...

Or maybe PCD is having a 'break' in our lovely country?

Arno R

 

[Rob Oldfield]

I had that same suspicion myself.

Hmmm,
I don't think nanne *is* PCD. I am even afraid it is a compatriot of me...
Look at he mail address (e-mail address removed) and the headers.
Originating from Holland so it seems...

Or maybe PCD is having a 'break' in our lovely country?

Arno R

__________

I also don't think it is.... although http://www.home.nl/ does have a
webmail login available.....

 

[BruceM]

I took a look at the screenshot, and I understand that the purpose of the
database is to track suspicious incidents, and that the cameras are security
cameras. Access is a relational database program. A sort of classic
example of relational database principles is a sales order. One order may
contain many line items. This is accomplished by having an Orders table
that contains basic order information such as date, order number, sales
representative, customer, etc. An OrderDetails table contains the specific
items that are part of the order. If an order is written by hand the clerk
does not usually write a separate order for each item, but rather one order
with many individual items. Same idea with a relational database.
In your case it seems that the main table is your Incidents table (or
Reports table). The screenshot shows space for two vehicles, but what if
there are three or more? To allow for the greatest flexibility, you would
need a VehiclesInvolved table. Same with PeopleInvolved, or whatever it is
called. What if there are more than four?
Now here's a part that can get confusing. VehiclesInvolved is different
from the various vehicle details such as make, model, etc. Those tables as
you have described them are just ways of creating lists from which the user
can select. The user selects Blue, and Blue is saved as the vehicle color.
The user could just as well type in Blue. Those list tables are there for
convenience. They are static tables, which is to say you populate them once
and select from the static list after that. You may modify them as, for
instance, new models of cars come out, but otherwise they do not grow.
tblSex is not really necessary, since you can create what is called a value
list containing just those two items.
As Ken pointed out, you will need several related fields such as OperatorID.
Suppose that somebody's name changes. If you have created the relationship
as Ken has described, then when you look at a record from a year ago you
will see the operator's current name. This is because you have created a
relationship between an Incident record and an OperatorID. When you view
the record from a year ago, Access looks up (in a sense) the OperatorID, and
identifies the name and other information currently associated with that
operator.
CameraID as Ken described it would do the same thing, so if Camera 20 is
moved to another location, any records associated with Camera 20 will now
show the new information. If the camera may be moved or renumbered, and if
the camera's location at the time of the incident is important, then you
will need to save that specific information rather than creating a
relationship as you did with the Operator.
So in addition to what Ken has suggested, I would add a table for
VehiclesInvolved and one for PeopleInvolved, as already described.
This is a very, very basic outline. You really will need to do some
studying. The good news is that what you are describing is a pretty
straightforward use of a relational database. It's a pretty good first
project. However, Access is not intuitive, and there is definitely a
learning curve.

 

[Ken Snell \(MVP\)]

A primary key is the field or combination of fields whose values uniquely
identify a record in a table. May I recommend that you obtain a book about
ACCESS (there are many good ones in bookstores and libraries), as that will
give you an excellent introduction to ACCESS?

Others have given comments on this proposed table structure. I don't think I
can add to those comments at this moment. You appear to be headed in the
right direction with identifying "entities" and the entities' "properties"
with the table setup that you have laid out so far, but I'm sure you'll find
some changes that you'll want to make as you begin working on the
relationships and data.