First Database---Scary Stuff!!!!

  • Thread starter Thread starter tan
  • Start date Start date
T

tan

Hi All,

WARNING: This is a long post, so please bear with me. This may be an
impossible question to answer because of its complexity, but I'm
hoping someone will takle it. At least, point me in the right
direction.

I have been using MS Office applications for a number of years and
consider myself a somewhat advanced user. I wrote some excel
spreadsheets in the past with quite involved macros and formulas. Now
I want to tackle Access.

I should mention that this is probably my fifth or sixth attempt at
Access. I don't care what the literature says, unless you're using the
canned solutions offered by MS (Contact Management, Event Management,
etc.) as is, you're in for a surprise if you think Access is easy.

With that in mind, I picked up a PC Tutor cd at my local Staples store
and brushed off my dusty "Access for Dummies" book and, well, here I
am.

I understand from what I've learned that I have plan the database
before I even start the software. Ok...I'm at my first impass already.

Our company does snow removal in the winter. I'm trying to design a
database that will keep track of how much time we spend at each site
to determine the actual cost to what we bid to see if a site is
profitable or not.

I would like to be able to enter how much time is spent at each site
during a snowfall--more specifically, break it down into how much per
cm a site costs.


So, I started my tables list as follows.

Employee ID
Employee First Name
Employee Last Name
Address
City
Prov.
Postal Code
Home Phone
Cell Phone
Email


Customer ID
Customer Type ID --- this could be a "commercial", "government",
"Mall", or "Housing" customer.--see next table.
Company Name
Contact First Name
Contact Last Name
Address
City
Prov
Postal Code
Phone
Fax:
Email:
Website:


Customer Type ID
Customer Type
Note:

This table lists the job types.
Work Type:
Plowing
Salting
Sidewalk Clearing
Breaks & Lunches

This table lists the equipment used. (Truck & a Plow, Backhoe, Loader,
Etc.)
Equipment ID
Equipment Type

Aside from the work of clearing snow, there is also removal at certain
sites. There I need to keep track of how many truckloads of snow have
been removed from a particular site and where it was dumped. There are
three possible dump sites in the city.

Dump Site ID
Dump Site Name

As you can see, the more I put this on paper, the more the task seems
insurmountable. I don't mind spending the time on this, in fact, I
rather enjoy the learning experience--even if I get totally frustrated
and my project doesn't pan out. I'd like to give this project a
fighting chance, however, so I'm asking for pointers in the right
direction.

How do my tables look so far?
Am I trying to tackle something too big to start?
Are there any sites that could help novices?

Any help would be greatly appreciated.
 
Hi Tan,

You're working on the right lines, but you've picked a complex
real-world domain to model and need to do some more hard thinking about
the entities involved.

By the sound of things, the key entity is something like an "Episode",
when you clear a particular "Site" on a particular date.

Each "Job" or "Contract" presumably involves keeping one or more sites
clear for a particular period, so may involve more than one Site and
more than one Episode. (I.e. there's a 1:M relationship between
Contracts and Sites, and another 1:M relationship between Sites and
Episodes.)

You can probably have have more than one Contract with each Customer
(either simultaneously, covering different sites or groups of sites, or
in succession, e.g. last winter and this winter), so there's also a 1:M
relatioship between Customers and Contracts.

All this gives you rather indirect but nevertheless sufficient
relationship between Customer and an actual Episode of snow-clearing.
Going back to Episode: each Episode involves certain inputs of labour,
machinery, materials (fuel, salt, etc.) So you'll need to decide how you
relate Employees to Episodes. At it's simplest it's a M:M relationship,
perhaps implemented via a "WorkPeriod" entity:
tblWorkPeriod
EmployeeID
EpisodeID
Duration
WorkType

but you'll also need to work out whether the use of Equipment is related
to a whole Episode or to a WorkPeriod.

If your Employees work in teams and each episode would be assigned to a
team rather than to one or more individuals, you may need to use a
"Team" entity and track which Employees belong to which Team at what
dates.

About this stage I expect that the pressure in your brain will become
intolerable<g>. Take a break, sleep on the problem, and carry on later.
You're absolutely right to feel Access is an order of magnitude more
difficult than the other Office applications. This isn't a failing on
Access's part, it's just that the problems people tackle with Access are
an order of magnitude more complex than almost all the problems they
tackle with Word or Excel.
 
Hi John,

Wow! That sounds even more scarry. I guess I'm in for a lot more than
I expected. Maybe I should look at a community course.

You got the idea of what I was looking for quite accurately. Maybe I
should start with a simpler database.

Any pointers for a novice or someone looking to start learning Access?
 
tan said:
Any pointers for a novice or someone
looking to start learning Access?
Click to expand...

Two books from Microsoft Press:

Microsoft Access <versionnumber> Step by Step

Microsoft Access 2003 Inside-Out by John Viescas

Another that I've liked is:

Special Edition Using Microsoft Access <versionnumber>,
by Roger Jennings, published by Que

There are quite a number of online training modules available at "Office
Online"... you can access the site from the Help pane of Access 2003. That
takes you to http://office.microsoft.com/en-us/training/default.aspx.

There's lots of good information on specific topics, though no "tutorials",
exactly, at http://www.mvps.org/access and links to a good many resource
sites at http://ntaccess.tripod.com, too.

Larry Linson
Microsoft Access MVP
 
Hi Larry,

Thanks. I'll look at those. I got a CD Tutor as well that walked me
through a lot of the features of Access. I can get around the software
no problem, but I think I need help understanding how to design a
database with real world situations.

Especially Databases that involve a bit more than recording a cd
collection, etc.

Ciao,
Tan
 
Hi Tan

I had been tinkering with Access for a while and always coming up against a
brick wall that seemed to be completely baffling. The books I had were not
showing me the 'big picture'. Then I got talking to a customer who said he
did access as a hobby which had become a full time job. He did apps for
small businesses, and he said he would come by and see where I was getting
stuck and give me a hand. Of course, I think he saw his next project ;)

Anyway, I sat down and distilled about a half-dozen trouble spots that I
had and was ready when he came by. Well, the fog lifted big time when I
saw him cruise around sorting out my difficulties. Since then I have been
full steam ahead with access. My problems with linked tables, queries,
report formatting, etc are behind me.

Anyway, I gave the guy a hefty discount on his order :)

- Peter
 
Back
Top