Building Database for Army Helicopter Maintence Program...Need Hel

[M. Parker]

I am building a database to track workorders to a support unit. I am
completely new to this and need help with pretty much everything. I need to
be able to track by open/closed workorders, by company i.e. A Co, B Co, C Co,
D Co, and by priority of the workorder. I also need to be able to print
reports that show the info I need to track. Any help would be awesome.

 

[Ken Snell \(MVP\)]

A "large" task that you face, indeed! < grin > Depending upon the level of
sophistication that you seek to achieve, this task will take a lot of time.

The first step in setting a good database is to identify all the data items
that you need to store (trucks, repair action items, repair orders,
mechanics, work locations, repair parts, etc.). Keep in mind when building
this list all the things that you'll want to be able to include in
reports -- e.g., vehicles currently in repair status, repair history for
vehicles, work performed on each repair order, all repairs performed by a
mechanic, etc.

From that list, you then begin to identify the table structure that will
work for you. Normalizing the table structure is very important to making
the report queries easier to write and design.

If this is your first foray into database design, may I suggest that you
obtain a book or two on database design -- that will give you a good start
in how to design your application. Post back if you'd like suggestions for
books.

If this isn't your first foray, let us know your experience level and what
you've started to do on this application so far. We won't be able to
design/build the application for you, but we're very willing to offer
suggestions, pointers, advice, and critiques!

 

[M. Parker]

You are talking about way more than I was planning on doing. I am in a
Production Control office for an aviation unit, and all i am wanting to track
are the workorders that our support battalion does for our aircraft.
Ultimately, what I want out of this database is to have a form for entering
the info that I have to track and have it sent to the appropriate table, and
be able to print reports for the higher-ups when they need info. This is my
first attempt at such a task, hence my desire to make this as painless as
possible. If ican get this to work, then i may do an elaborate database for
the rest of the stuuf we track. Oh, i do have a book. It's the Access for
Dummies. It is some help, but leaves alot to be desired for what I am trying
to do.

 

[Tom Lake]

I am building a database to track workorders to a support unit. I am
completely new to this and need help with pretty much everything. I need
to
be able to track by open/closed workorders, by company i.e. A Co, B Co, C
Co,
D Co, and by priority of the workorder. I also need to be able to print
reports that show the info I need to track. Any help would be awesome.

When I was at Ft. Lee, VA (Quartermaster Center), I was on a team that wrote
a
program to keep track of all the Army's inventory. Every lightbulb, roll of
toilet
paper and piece of ordinance for the entire Army was in that FEMS (Field
Engineering Maintenance System). It wasn't written in Access but in COBOL
on an IBM 360 DOS system which was much smaller than today's desktop PCs.
If I could do it with that primitive hardware, you can certainly write a
great system
with Access. One method I like to use is to see how a job is being done now
and try to emulate the manual system as far as practical. I get less
resistance
from users if I don't change their SOP too much. I also write the system to
be
so easy even a Colonel can use it! 8^)

Good luck!

Tom Lake

 

[Ken Snell \(MVP\)]

Once you start, you'll be drawn into the wonderful world of ACCESS and
you'll be enticed to do all kinds of "extra" things < grin >.

OK - nonetheless, you still need to list on paper all the data items that
you want to store so that you can begin to identify the table structure that
you want to use. Let's start with that list. Post it here and we'll continue
the discussion.... ok?

--

Ken Snell
<MS ACCESS MVP>

You are talking about way more than I was planning on doing. I am in a
Production Control office for an aviation unit, and all i am wanting to
track
are the workorders that our support battalion does for our aircraft.
Ultimately, what I want out of this database is to have a form for
entering
the info that I have to track and have it sent to the appropriate table,
and
be able to print reports for the higher-ups when they need info. This is
my
first attempt at such a task, hence my desire to make this as painless as
possible. If ican get this to work, then i may do an elaborate database
for
the rest of the stuuf we track. Oh, i do have a book. It's the Access
for
Dummies. It is some help, but leaves alot to be desired for what I am
trying
to do.

:

< snipped >

 

[M. Parker]

ok, here goes.... data i need:
units, aircraft tail numbers, aircraft model, unit workorder number, part
description, work requested by, serial number, fault description, priority of
work order, opened date, accepted date, support workorder number, and closed
date. dates need to be military julian i.e, 13 april 06 = 6114. i want to do
a master table that will automatically put the info from it onto the
corresponding units table.
thanks for the help, you rock!

matt

 

[Ed Warren]

If you have it available you might take a look at InfoPath. It has been
built to allow 'document tracking' (mirrors the initiation, review, approval
process).

Ed Warren

 

[Ken Snell \(MVP\)]

OK - we're going to approach this as a database, not as a "souped-up"
spreadsheet < g >.

From the data list that you've posted, you should create the following
tables:

tblUnits
UnitID (primary key)
UnitName
UnitLocation
UnitContactPerson
(etc.)

tblAircraftModels
ModelID (primary key)
ModelName
(etc.)

tblAircrafts
AircraftID (primary key -- note that this could be the tail number)
AircraftTailNum (if you don't use tail number as primary key)
ModelID (foreign key from tblModels table)
(etc.)

tblParts
PartID (foreign key)
PartDescription
(etc.)

tblPersonnel
PersonnelID (primary key)
PersonFirstName
PersonLastName
PersonRank
PersonSerialNum
UnitID (foreign key to tblUnits)
(etc.)

tblWOPriorities
WOPriorityNum (foreign key)
WOPriorityDescription

tblWorkOrders
WONumber (primary key -- this would be the support work order number)
AircraftID (foreign key to tblAircrafts)
PersonnelID (foreign key to tblPersonnel -- this is person requesting
the work)
UnitWONumber
FaultDescription
WONotes
WOPriorityNum (foreign key to tblWOPriorities)
DateOpened
DateAccepted
DateClosed
(etc.)

tblWOParts
WOPartsID (primary key)
WONumber (foreign key to tblWorkOrders)
PartID (foreign key to tblParts)
WOPartQuantity
(etc.)


The above table structure allows the following things to be done in the
database file (this prevents the entry of slightly misspelled names for the
same person, for example; and it prevents the entry of invalid values for
priorities, for example):
1) Use a combo box on a form in order to select the person requesting
the work
2) Use a combo box on a form in order to select the aircraft to be
repaired
3) Use a combo box on a form in order to select the work order priority
4) Use a combo box on a form to select the part for the work order
5) Have multiple parts for each work order
6) Track which orders are "pending" (not accepted yet), "in process"
(opened but not closed), and "closed"
7) Track which persons have requested work orders
8) Track which units have requested work orders
9) Show all work orders performed on an aircraft
10) Show all work orders performed on an aircraft model
(etc. etc. etc.)

Your comment about the "military" date having to be "Julian". Personally, I
would store dates in the database in the "normal" manner (Date/Time data
type) that ACCESS uses (namely, a long integer number representing the
number of days since December 30, 1899). You can always display the dates in
whatever format you wish, although it's likely that you'll need a custom
function to convert the normal date to the military Julian date, and
vice-versa. This is a bit tricky for beginners, but very doable. The reason
I recommend this is because ACCESS queries are going to expect dates to be
in the normal format (e.g., "mm/dd/yyyy") in order to select data records
for your reports. If you store the dates as military Julian dates, you then
will need to have a function that converts the Julian date to a "mm/dd/yyyy"
date just to run the query... and you'll not want to do that all the time
because it will slow your queries down as you gain more data.

In the above table structure, where I indicate "foreign key to xxx table",
that indicates a Relationship exists between the two tables -- a "join line"
in the Relationships window. Through these relationships, you can store a
data value in just one table and then be able to look it up via query at any
time. Thus, by putting the UnitID in the tblPersonnel, and then by using
PersonnelID in tblWorkOrders, you can display the UnitName for the unit that
has requested the work order through the PersonnelID link to tblPersonnel
and then through the UnitID link to tblUnits.

After you've had a chance to look this over, post back with questions.

 

[Apache]

I am not sure if you have ULLS-G, and is it not working? There is also
another system that will soon replace ULLS since it is way overdue.

In any case since you are interested in learning access this is a great. You
may want to add another table for the Technical Inspectors or TI's this would
serve as a lookup up table. You could probably use the Personnel table. I
would also add an archive table to store the repair history of the aircraft.
You could have a form where when any of the serial or aircraft numbers are
entered the form populates the required fields. You will also require a field
for the TI or test piliot who cleared the repairs.

Since aircraft NMC is recorded in hours, you may also need the ability to
track the hours?

When developing a db it is important to research the information and place
it on paper, prior to implementation. I would highly recommend finding a copy
of "Database Design for Mere Mortals" author Mike Hernandez. This will
provide some insight to the task. I would also attempt to find the FUG
(Functonal Users Guide) to ULLS-G to see what fields they are capturing. You
maynot need all of them; however, it may provoke some thought when designing
the db.

There is a lot of interesting information on the web and in the NG for
Access. The Access gurus (Access MVP's) like Ken Snell, Tom Wickerath or many
others provide awesome assistance; however, they are also assisting several
posters as yourself.

HTH

 

[M. Parker]

Actually, we are using ulls-a...the replacement system will be ulls scp6.
ulls is working fine, i just hate using it. i am doing this to replace the
da2405 log book. there is some good info in your post, are you aviation as
well, if so, where? i am at hood. i won't need to track hours, b/c our bamo
set up an excel spreadsheet that does that. if you are here, maybe we can
link up.

 

[M. Parker]

OK- after looking at what you suggested, i have some questions. 1: how will
i be able to us this to enter info into a form and have it sent to all of the
correct tables? 2: the i'm not sue what to do with the personell table. the
work i track is requested by units, not people. 3: with the workorders
table, i can enter the info i have now, but what about future info that needs
to go in. hence, my desire for a single form to send out the info. 4: the
tables parts and w/o parts, i am not sure what to do with them, b/c there are
way too many parts to list and i don't even know what they all are. thanks
for the patience.

 

[Ken Snell \(MVP\)]

The tables that I suggest are based on what I am understanding (ok,
inferring < grin >) from your list of data that you want to store.

Your list included work requested by and serial number. I was assuming that
these two items refer to a person who is making the request, and the serial
number of that person. If this is not correct, please clarify what these two
items are.

The data design that I'm suggesting is using what are called "lookup"
tables -- a table that stores unique data that will be used throughout the
database -- e.g., tblUnits. In order to enter data into them, typically
you'd create a form that is based on that table and then you can enter new
records and edit existing ones.

No one form should be considered for all the tables that I suggest. However,
you will be able to use a single form for entering records into
tblWorkOrders table -- and, if you were to use it, tblWOParts could be
populated using a subform within this form.

Note that my suggestions are just that -- suggestions, based on my
interpretation of what you listed. I assume that a repair work order may
involve more than just one part for the repair, which is why I suggest a
separate "child" table for the parts used on a work order.

From you list of data items, and from my suggested table structure, you now
can make decisions about what the database will actually hold, and what will
be the "business" rules for it (e.g, only one part per work order; no
person makes a request for a work order but instead a unit makes a request;
etc.).

Creating a database should always involve a lot of upfront paper/pencil time
as you consider the data, the characteristics, the future needs, etc. --
only after that should one begin to create the database. We're still in the
"paper/pencil" mode -- we're hampered a bit by the fact that our
communications are via this written medium, so our questions/answers will go
back and forth without the benefit of seeing "live" the types of things you
already have or want to do. Doing the design on paper first is more likely
to produce a workable database for reports, entry, etc.

So, consider the business rules and the data again, and then let's determine
the items that should be stored so that you can use them in reports later
on.

 

[mnature]

Not to discourage you from building a database from scratch, but there are
some templates available on the Microsoft site, that could either be modified
for what you need, or at the very least give you an idea of how to structure
your own database.

The first one is an Orders Management Database, which is good for handling
discrete items:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

The other one, which is perhaps more applicable to what you are doing, is a
Service Call Management Database. This one can also track parts.

http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT011366681033

I would suggest that you download these, look at the tables and
relationships, and see how the queries, forms and reports are laid out. They
are set up as civilian business templates, but I believe they could be
modified (in some cases by just renaming fields) to fit the information you
would require in the military.

Remember, before modifying any database, make a back-up. A database can be
very unforgiving of errors (many processes do not allow you to back-up once
they are completed). It can be very frustrating to just do one final change
to a form, and have it blow up and stop working.

 

[Apache]

ULLS has never been well received. In any case you really need to decide if
you want to take the time to learn database fundamentals and Access. I do not
want to discourage you; however there is a steep learning curve that requires
years to reach the level the Access MVP are cabable of producing. OTH you
have a an excellent mentor who is willing to share his time and expertise.
You should consider yourself fortunate. I am familiar with the 2405. I am an
old soldier who retired years ago. I applaud your initiative and highly
encourage you to pursue learning Access if for nothing else but your own
knowledge for future use.

You mentioned you only work with unit information. Has the Aviation field
changed where they are no longer using TI's? I am sure they are still using
the 1687 Sig cards. I would include this field and the date of expiration on
the card. You can also send reminders to the individuals when the card will
expire. This will keep you one step ahead of the inspection. I would
definetly have the personnel table that Ken recommended. It is also highly
recommended to sharpen your pencil you will have to design the draft on
paper. Once you have this you should consult with your co-workers and some of
the old timers for their input.

Again if you want to learn you have an excellent mentor. It will be a long
road ahead. You can build something very basic with Excel for the 2405. The
down side is the reporting features as well as capabilities do not even come
close to the task at hand. OTH if you decide Access you will enjoy and
appreciate the application once you discover the power of Access. I have seen
one of Tom Wickeraths (another Access MVP) databases and it really far
surpasses anything I have ever seen, and I have been using Access for 10
years. These guys really know their craft. I can help where I can with the
logistics side.

Your decision, good luck.

 

[Tom Wickerath]

Hi M. Parker,

OTH if you decide Access you will enjoy and
appreciate the application once you discover the power of Access. I have seen
one of Tom Wickeraths (another Access MVP) databases and it really far
surpasses anything I have ever seen, and I have been using Access for 10
years.

<Begin Blush>
I believe my friend "Apache" is talking about the Query-by-Form (QBF)
technique. I alerted this friend to your post, because I think it would be a
great opportunity for Apache to continue learning Access, while helping you,
if he has the time available. One of the best ways to learn any subject, in
my opinion, is to make an attempt to tutor others in the same subject.
</End Blush>

In any case, if you would like to see a sample of QBF in action, send me a
private e-mail message with a valid reply-to address. My e-mail address is
available at the bottom of the Contributor's page, indicated in my signature
below. Whatever you do, please do not post your e-mail address (or mine) to a
newsgroup message.


Good Luck on your project,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[M. Parker]

sorry for the slow reply...work has been very hectic the past two days. ok,
now to business. work requested by would be either an aircraft tail number,
or tech supply. not a single person. the serial number would either be the
aircrafts full tail number, or the serial number of the single part off of
the aircraft being repaired. the downfall of the single part is that i won't
have the serial number until the part is in my hand, so i couldn't make a
table of parts..or at least as far as i can tell, i can't. for the "business
rule" of it, units make request. the tail number of the aircraft, or if it
is tech supply, lets me know the unit. i am thinking, maybe a table for each
company with the tail numbers, or tech supply, as a field? the one part per
workorder is a correct assumption. those are pretty much the rules. the data
i need remain the same, minus the corrections i made in this post.
units(a;b;c;d co's), aircraft tail numbers, aircraft model(uh-60 or ch-47),
unit workorder number(workorder number we generate with each request), part
description(what the part is or what type of aircraft it is), work requested
by( being either tail number or tech supply), serial number(being either the
single parts number or the aircrafts full serial number), fault
description(what the issue is), priority of
work order(high or normal priority), opened date, accepted date, support
workorder number(work order number the supportng unit generates), and closed
date. i hope this helps clarify things. once again, i really appreciate
the patience.

 

[Ken Snell \(MVP\)]

My turn to apologize for slow answering... have been tied up all day and
night on work duties, so it'll be tomorrow night at the earliest when I can
reply. Stay tuned.... < g >

 

[Ken Snell \(MVP\)]

My apologies again... ran completely out of hours tonite. I'll be back as
soon as possible.....

 

[Ken Snell \(MVP\)]

OK - So what you want is the ability to store the unit number and name, and
the serial number (is that the same as the tail number?) of the airplane.
And you don't need to store information about any personnel assigned to a
unit.

Also, you're not maintaining a table of parts, but we can still capture
information about the part that is being repaired for the database.

Then, I would modify the table structure that I'd proposed just a bit:

tblUnits
UnitID (primary key)
UnitName
UnitLocation
UnitContactPerson
(etc.)


tblAircraftModels
ModelID (primary key)
ModelName
(etc.)


tblAircrafts
AircraftID (primary key -- note that this could be
the tail number or the serial number)
AircraftTailNum (if you don't use tail number as
the primary key)
AircraftSerialNum (if this is not the same thing as
the tail number, and if you don't use
serial number as the primary key)
ModelID (foreign key from tblModels table)
(etc.)


tblWOPriorities
WOPriorityNum (foreign key)
WOPriorityDescription

tblWorkOrders
WONumber (primary key -- this would be the
support work order number)
AircraftID (foreign key to tblAircrafts)
UnitID (foreign key to tblUnits -- this is unit requesting
the work)
UnitWONumber
FaultDescription
WONotes
WOPriorityNum (foreign key to tblWOPriorities)
DateOpened
DateAccepted
DateClosed
(etc.)

tblWOParts (allows multiple parts for each work order)
WOPartsID (primary key)
WONumber (foreign key to tblWorkOrders)
PartDescription (memo field to allow free-form text
entry of information about the part;
this can be split into separate fields
if you know the specific details that
you want to store about each part
that is being repaired for the work
order)
(etc.)


How does this look?

--

Ken Snell
<MS ACCESS MVP>

sorry for the slow reply...work has been very hectic the past two days.
ok,
now to business. work requested by would be either an aircraft tail
number,
or tech supply. not a single person. the serial number would either be
the
aircrafts full tail number, or the serial number of the single part off of
the aircraft being repaired. the downfall of the single part is that i
won't
have the serial number until the part is in my hand, so i couldn't make a
table of parts..or at least as far as i can tell, i can't. for the
"business
rule" of it, units make request. the tail number of the aircraft, or if
it
is tech supply, lets me know the unit. i am thinking, maybe a table for
each
company with the tail numbers, or tech supply, as a field? the one part
per
workorder is a correct assumption. those are pretty much the rules. the
data
i need remain the same, minus the corrections i made in this post.
units(a;b;c;d co's), aircraft tail numbers, aircraft model(uh-60 or
ch-47),
unit workorder number(workorder number we generate with each request),
part
description(what the part is or what type of aircraft it is), work
requested
by( being either tail number or tech supply), serial number(being either
the
single parts number or the aircrafts full serial number), fault
description(what the issue is), priority of
work order(high or normal priority), opened date, accepted date, support
workorder number(work order number the supportng unit generates), and
closed
date. i hope this helps clarify things. once again, i really appreciate
the patience.

< snipped >