Newbie: where do I start?

[Ed from AZ]

The last time I tried to build an Access database from scratch, it had
one simple table in Access 97! Haven't touched it since. Now I'm up
to Access 2003, and I need to build a database, and I don't really
know where to start.

I need to track maintenance on a fleet of vehicles. We're concerned
about how long we use certain parts before we have to replace them.
When it was just a couple of vehicles, an Excel spreadsheet did fine.
It's time now to move up to a full database!

I envision opening the database and getting a data entry form. The
form will ask me for the vehicle number, the section it's in, and the
odometer and hours meter readings. It would also have a drop-down
list where I can pick which part failed this time, an input field
where I can specify location (left/right, front/rear, inner/outer,
etc.), and another input field where I can input anything else about
the part (manufacturer, size code, etc.).

Hitting enter would write the info into the database, and cause the
data entry form to show me the "life" of that particular item. That
means there would have to be some way for the program to find the last
one of that part installed at that particular position (the left front
tire, for example), get the parameters at installation, and calculate
how long this part has been on the vehicle. There would also have to
be some way to calculate in (and therefore some way to enter and
update) any "fudge factor" in any of these calculations (changing a
towed or connected attachment may give me a new "hours meter" reading,
but won't change the actual "life hours" of a front tire).

Last, I would also need a query with an input form that would let me
generate a report (probably a spreadsheet format) against any vehicle,
or set of vehicles in a section, or part overall or at a specific
location showing me how long each returned item has "lived" before
needing replacement.

Is this do-able for a newbie? Or should I hand this off to a Big Boy
and just stay out of the way?

Ed

 

[MikeB]

The last time I tried to build an Access database from scratch, it had
one simple table in Access 97! Haven't touched it since. Now I'm up
to Access 2003, and I need to build a database, and I don't really
know where to start.

I need to track maintenance on a fleet of vehicles. We're concerned
about how long we use certain parts before we have to replace them.
When it was just a couple of vehicles, an Excel spreadsheet did fine.
It's time now to move up to a full database!

I envision opening the database and getting a data entry form. The
form will ask me for the vehicle number, the section it's in, and the
odometer and hours meter readings. It would also have a drop-down
list where I can pick which part failed this time, an input field
where I can specify location (left/right, front/rear, inner/outer,
etc.), and another input field where I can input anything else about
the part (manufacturer, size code, etc.).

Hitting enter would write the info into the database, and cause the
data entry form to show me the "life" of that particular item. That
means there would have to be some way for the program to find the last
one of that part installed at that particular position (the left front
tire, for example), get the parameters at installation, and calculate
how long this part has been on the vehicle. There would also have to
be some way to calculate in (and therefore some way to enter and
update) any "fudge factor" in any of these calculations (changing a
towed or connected attachment may give me a new "hours meter" reading,
but won't change the actual "life hours" of a front tire).

Last, I would also need a query with an input form that would let me
generate a report (probably a spreadsheet format) against any vehicle,
or set of vehicles in a section, or part overall or at a specific
location showing me how long each returned item has "lived" before
needing replacement.

Is this do-able for a newbie? Or should I hand this off to a Big Boy
and just stay out of the way?

Ed

I started some time back with the "Exploring" series by Robert T.
Grauer and Maryann Barber.

Your system as you describe it doesn't sound too difficult.

I'm currently playing around with Access, trying to get better at it.
If you are interested, I'd be happy to give it a spin and see if I can
come up with something that you could use. No promises, though, I'm
really learning myself. I'd have to look at the spreadsheet you have
and Ill probably develop it in a lower level of Access than 2003, I'm
not that up to speed with my 2007 version and the older version I have
is 2000 or 2002.

My email works.

 

[akphidelt]

Hey Ed! You gotta want it!!

But yea, I did something very similiar to this... I can't give you every
detail but here are some of the things I would do.

#1 Create a master table with as much information as you can possibly need
on it. The unique identifier on this table will be the Vehicle number.
Everything else you create from here on out will have a relationship with
that number.

#2 Create a table that allows you to input the data info. Remember create
the vehicle first before you start making edits to it. You can actually
create a combo box linked to the master vehicle list that only allows people
to select a real vehicle. *Just a little trick. Create a field in the master
table that is a Yes/No field to tell if the vehicle is active. Create a form
called Deactivate Vehicle and in that form type in the vehicle number and
then have a check box to deactivate the vehicle. Then create a query, in the
parameters make sure that only active vehicles are selected. That way you can
always have an active list of vehicles instead of searching through tons of
old vehicles.

#3 Make sure you add a date field. The thing I like to do is allow the users
to input the date of the actual incident in case they fill the form in
late... and then I input an invisible text box that has a default value to
Date(). So that regardless if they put a bad date or not you will have the
actual date of the entry.

#4 As long as you have the master vehicle table you can create any query you
want that has a left join with the data table. This is where you will be able
to generate your custom reports... be able to set fields to the queries
record source and put in combo boxes or text boxes as parameters to get the
information you need. *Another thing I like to do is create a master search
page that has a list of all the active vehicles and allows them to custom
search them by each field by putting a combo box above each one and have them
interchangeable. Then create a double-click event so they can drill down to
the gritty details of their selection.

All and all, the most important thing is to figure out what your drivers
are. What fields link to what and what are the fields that are needed to
generate the information you want... like Dates, Vehicle Numbers, Parts,
Odometer readings, etc.

Good Luck!

 

[Ed from AZ]

Thanks for the input, akphidelt. I definitely want this! My
spreadsheets are just a whole lot too inadequate for this big of a
monster.

#1 Create a master table with as much information as you can possibly need
on it.

Yah - that would be the logical place to start. I could probably just
import one of spreadsheets and have most of what I need already.

#3 Make sure you add a date field.

D'oh!! Of course! I like your idea of the blank Date(), too!

#2 Create a table that allows you to input the data info.

A separate table for data input?? I don't understand. Wouldn't I
build an input form off the main table?

*Just a little trick. Create a field in the master
table that is a Yes/No field to tell if the vehicle is active. Create a form
called Deactivate Vehicle and in that form type in the vehicle number and
then have a check box to deactivate the vehicle.

That's a great idea that I would not have thought of until down the
road: "Gee - we haven't used that truck in 3 years! I wonder if I can
take it off the list."

Okay - so I got a place to start. I'll be picking my way through this
thing over the next few weeks, and will definitely yell for more help!

Thanks again!!
Ed

 

[akphidelt]

No problem, feel free to ask any questions any time...

Trust me, from my experience in the past you will want two separate tables.
When I create databases for people at work I try to make it as simple as
possible and that requires eliminating major errors. One of the first
databases I created was exactly like yours for a vehicle pool in the North
Slope. We spent more time reconciling the damn thing then using it because of
typos, errors, mess of data. Life really became easiar once you split the two
tables up. If someone has an error in the data form it is very easy to delete
it... if you lock the vehicle table up with just a master list of vehicles it
eliminates lots of stress and extra work. Just my opinion!

 

[rumkus]

When it was just a couple of vehicles, an Excel spreadsheet did fine.

It's time now to move up to a full database!

"Just a couple of vehicles" for Excel ?

Excel will handle couple of thousands vehicles along with their couple
of thousands parts on its
couple of tens sheets without any difficulty actually.

Jump on access which i'd say good but lets leave Excel's credit
intact.

rgds

 

[Dennis]

http://www.freedownloadmanager.org/downloads/fleet_maintenance_software/

http://www.fleetwisevb.com/

 

[Ed from AZ]

Hi, rumkus. Not trying to disparage Excel in any way - I use it all
the time. It was just starting to get very unwieldy to keep
everything straight and handle multiple "updates" of multiple
spreadsheets. Access is just a way to consolidate everything. I had
actually thought about building this as an Excel app, with separate
worksheets for each vehicle. The VBA wouldn't be all that bad, and I
can do formulas for the calculations with no problems. But that would
be making xcel do something Access does naturally. And this way I get
to lerarn Access, too! Paid to play? That's the way to go!

Cheers!
Ed

 

[Ed from AZ]

I still can't quite wrap my brain around how the two tables will work
together. If I build a table for InputData and build a data entry
form using those fields, my data will then go into that table. Then I
have to have a separate function that puts the data into the MainData
table, right?

I understand keeping the main table untouched. And I understand the
need for data validation so clean-up is minimal. I just don't know
enough yet to visualize how the two tables work together.

Ed

 

[Adien]

Hey Ed,

A while ago I made a database that is similar to what your talking
about but it was a bank program the first table contained all the
customer info and the second one contained transaction data, they were
linked by a one to many relationship. So each customer has mutiple
transactions similar to each of your vehicles would have many things
done to them. Make sense?

 

[Ed from AZ]

Ah - I think the lights are beginning to go on!

Thanks, Adien. I appreciate the boost.

Ed
(Off for the weekend. Y'all have a good one!)

 

[Ed from AZ]

Okay - I think I'm starting to get a handle on this. I need:

-- a main data table
-- a table of vehicles with a Boolean "active/not active" field
-- a table of parts with a Boolean "active/not active" field

-- a query that returns the active vehicles
-- a query that returns the active parts
-- a query to return all records for specif vehicles and or parts

-- a form to add new vehicles and toggle Active status
-- a form to add new parts and change Active status
-- a form to add new data records, using drop-downs of active vehicles
and active parts

Sound like a good place to start?

Ed

 

[Adien]

Sounds to me like your on the right track Ed, best of luck!

 

[Ed from AZ]

We'll give it a go, then, and I _know_ I'll be back with questions!!

Thanks for all the help.
Ed