MS Access - where to start?

Factual Questions
1

I consider myself to be pretty darn good with most of the MS Office suite of programs - Word, PowerPoint, and I’m particularly good with Excel, if I do say so myself. Access, on the other hand, I am apparently a complete buffoon.

I have done some minor databases with Access, but I’m stumped on where to start on this one. I look to the Millions for some ideas.

I need to build a database to track a training program. I’ve been using an Excel spreadsheet that prints out to about twelve pages long to track this stuff, and it’s a nightmare. Access should solve the problems, I hope. Here are the general “must haves”:

  • There are 14 categories that must be independently tracked
  • Within each category, there are anywhere from 3 to 22 objectives, which also need to be individually tracked
  • A training event can hit multiple objectives across multiple categories
  • One category needs to track each objective in relation to location (everything in the category needs to be done for every aircraft that lands at our airport)
  • A training report needs to track who was at the training, which can (and will) change for every class taught.

The wall I’ve run head-long into is in setting up the initial tables. Do I use a different table for each category, listing each objective individually? Do I use yes/no boxes, and list all 130-odd objectives in one long table? Do I have each training event make a full entry of all of the training information for each person individually, or use a seperate table of training event vs attendees?

Once this is all done, the form that the data is entered in must be simple enough for a dinosaur to use - the kind of dinosaur that can just about use that new fangled “e-mail” stuff.

I fear I may be biting off more than I can chew, and perhaps the answer that comes might be in a language I don’t speak. Be gentle.

2

If I understand the requirements correctly you have something like this:

- Categories (Category_ID, Description...)
- Objectives (Objective_ID, Category_ID, Description...)
- Events (Event_ID, Description, Location, Date...)
- Participants (Participant_ID, LastName, FirstName, Company...)
- EventsObjectives (Event_ID, Objective_ID) This is an associative table to implement the many-to-many relationship between Events and Objectives (each event can cover many objectives and each objective can be covered by many events).
- EventsParticipants - (Event_ID, Participant_ID) This is an associative table to implement the many-to-many relationship between Events and Objectives. (There will be many participants at each event, and each participant can go to more than one event).

That’s my first pass at it, but I would need some clarity especially this part:

3

I tried to learn Access years ago and the main lesson I learned is this: get yerself a database designer.

Access by itself isn’t too hard to work with (although, I’ve heard that database designers universally hate it). The problem is that relational database design can be persnickety. You have to know every little thing you want the database to do and how all the information relates to each other bit of information before you even start building tables. Trying to adjust it later can be really frustrating.

Having said all that, instead of starting with what you want to record, start with what you want to report. What information are you going to be reporting and in what combinations? It might help to draw some sort of flow chart showing all the relationships.

Unfortunately, that’s about as much as I know about database design and Access.

4

One thing you might consider is that Microsoft is moving away from supporting access and may remove it from the Office suite in the future.

Since its there now and available to you it might be a good place to start. I’ve been advised by others on this board to jump in to SQL and become familiar with it as having a working knowledge of Access could actually get in the way of understanding SQL.

I’ve been developing a fairly large database and also learning SQL by working with the SQL Developers edition which costs around $100.

Maybe some Access heads can give you a few pointers or the guys from my link will stop by here. Good luck. It takes a pretty good effort to launch into this stuff but after getting a few of the basics down I’m really enjoying it.

5

I agree with the general thrust of what JustThinkin’ said except for this:

Database designers often look down their noses at Access because it’s not Oracle, or Sybase or whatever their industrial strength DBMS is. Of course it was never intended to be an enterprise-wide tool - it’s a desktop application. Even so, I’ve used it with some pretty big databases and it’s performed just fine.

So, anyway… In any suite of office tools - whether it’s Microsoft’s or anyone else’s - it will be the database application that will be the toughest to wrap your head around. Still, if you can work with Excel, you can probably manage Access.

As JustThinkin’ suggests, you really need to think through your requirements before you start building the system. Unlike other office tools, you need to go through a somewhat formal process before you start typing away.

So the first thing you need to do is come up with an entity-relationship diagram or ER diagram that will model whatever it is you are trying to track. In the ER model, entities are just those things that make up your world, and relations are the ways the entities interact to each other. Kind of a noun and verb thing.

In your case it looks like you are tracking training. So each training event (entity) will be taught to (relation) several students (entity). Also each training event will cover several categories and each category will have several objectives.

That’s about as far as I can go with the information I have. It would be helpful if you could flesh out the requirements a bit if only by giving a few examples of your “categories”, “objectives”, etc

6

What are they going to replace it with?

7

Besides the ER Diagram, you might want to read up on normal forms.

8

Now I’m trying to remember where I read that and any google searches seem to prove me wrong.

I could have sworn that I read one or two articles that claimed that Microsoft is providing less support, fewer update features, and planned to phase out Access in favor of SQL Express. But I can’t find the article so I admit that I shouldn’t have stated that without at least a cite.

9

I’m not sure what the main category should be. Is it the training category or are you tracking by employee?

I would make a table with objectives and create a form (call it OBJ form). The OBJ form would show one objective. Then add it to the main category form as a subform.

You can also make employee tables and add them as subforms to either of the other forms

You can then add or delete the subforms out of the main form. You open you sheet and have the 22 objectives show up you then delete the objectives that you didn’t use.

I know this sounds complicated and I will look to see if I have an example, but I would look at MS Access’ help section for creating, adding subforms, and subform overview. But I think the subforms are the key to keeping your data straight.

I think this is what you will want.

Does this make sense?

10

El Zagna, it seems like you got what I was trying to convey. The tables you mentioned are right in line with what I was looking to do, I think it’s the relationships that are throwing me for a loop. That and some form stuff, but I’ll wait until I can get the tables straightened out before tackling the forms.

Here’s an example of two categories and their associated objectives:

**(2) Aircraft familiarization. **
(a) identify the types of aircraft operating at the airport;
(b) identify the categories of aircraft propulsion systems;
© locate normal entry doors, emergency exit openings, and evacuation slides for a given aircraft;
(d) demonstrate the opening of all doors and compartments for a given aircraft;
(e) identify aircrew and passenger capacities and locations for a given aircraft;
(f) indicate the type of fuel used, location of fuel tanks, and capacity of fuel tanks for a given aircraft;
(g) identify and locate components of the fuel, oxygen, hydraulic, electrical, fire protection, anti-icing, APU, brake, wheel, and egress systems for a given aircraft;
(h) identify and locate the flight data recorder and cockpit voice recorder.

(11) Familiarization with fire fighters’ duties under the airport emergency plan.
(a) identify airport prefire plans;
(b) identify the various types of aircraft-related emergencies;
© identify and understand the incident command system to be utilized in an emergency;
(d) identify the procedures to be used to size-up a given aircraft accident; and
(e) identify the other duties of his/her organization under the airport emergency plan.

That Aircraft Familiarization category is the one that needs to be tracked for aircraft as well. We need to hit each objective (the letters) in each category every six months. We also need to apply objectives (a) through (h) in Aircraft Fam to each aircraft at the airport every six months, if that makes any sense.

Regarding JustThinkin’,

Here’s what I need to do (end goals):

  • Generate a training report (not necessarily a “report” in Access-speak) for each training event
  • Each training report must have

 
     - attendees
     - start and end date/time
     - shift the training took place on 
     - location
     - a description of the training 
     - instructor
     - check boxes for classroom or practical,
     - a couple of check boxes for various reviewers
     - category and objectives were met with that training
  • A training report must be able to be printed to a hard copy (not a problem, I know, but it’s a requirement)
  • Be able to retrieve training events by attendee, shift, instructor, date/time, review status, practical vs classoom, training category met, objectives met, and (in the case of that Aircraft Fam category) aircraft used.
  • Be able to generate a report of anyone coming due for training in a category or an objective
  • Entry of the training reports has to be dinosaur-proof

I’m working on the ER diagram, but its the phyiscal making-it-talk in Access that I’m fighting with…how to make the program give me categories and objectives all at the same time.

Thanks for the help so far…