Navy PFA(PRT) 2011

[BusDriver3]

I am a currently in the USN and need to schedule/track about 800 Command members for the bi-annual Navy PFA/PRT (Fall 2011). I know a little bit about Access and thought that I would try my hand at this.



I created a database to track the members at my Command and wanted to know if the database could some how be more optimal. Or if this will work, I plan on splitting the Front End and Back End so that my Team can take/make several appointments at once. Any feedback would be great. Thanks in advance.

Please see attached file.

[June7]

Nice start.

1. Only last name needed?
2. Normally, age would not be saved, just birth date and then age would be calculated when needed. But if this db will not have a long life (as in multiple years data), age probably okay.
3. Might want to do more standardization of entry options like you did for Events, such as for Divison and Rank. How many ranks? Do you want entered as like E-6 or Lieutenant? Limit user options with combo and list boxes or Input Mask in textbox. The RowSource for combo/list box can be a typed list or a table field.
4. Might set field Time of PT Test to Date/Time and Short Time format.
5. You have a field in Personnel for Event. Can there be only one event for each participant? I suspect not. Each event has many personnel and each personnel can have many events? This is many-to-many relationship and requires a junction table to associate personnel and events.
PersonnelEvents
EventID (foreign key)
ContactID (foreign key - do not use names as keys and don't save name in child tables, only the linking key ID)
EventResults

Seems to me some of the items in Events are not just events, but the results of an event. Example: Failed - Cardio, Failed - PRT, Waiver No PRT. Cardio and PRT are events. Failed and waiver are outcomes. If every person will be in every event might change Events structure to field for each event and the results would be recorded in each field (time, reps, pass/fail, waived) and then these fields could actually be in Personnel. This might not be strictly normalized but simplifies data entry. However, can sometimes cause issues when manipulating data for reports and summation/statistics.

So far, no code (macro or VBA) is involved. Next step to making this more user friendly involves code. I use only VBA. You could have a menu (switchboard) where users click button to open a form or report. You would also set project so users do not view the Navigation pane. Users should not normally interact directly with tables/queries so don't make it easy for them to mess things up.

Splitting is intended to optimise database performance with multiple users and I have done this with one of my projects. It is packed with code for user friendly GUI and data processing. Each user has their own copy of frontend on their workstation. Modified frontend is automatically copied down to their workstations. I have another very small project that is not split and multiple users open this file simultaneously and edit/add data. We tested this and amazingly it works. However, there is no code at all and despite my warning above, the users can interact with the Navigation pane. This is what they wanted, simple and no code and they learned to use the intrinsic Access tools for limited searching and filtering.

I see you are testing out three forms as interface for the Personnel table. The tab control is a way to compact a large data entry form into a small space, however, unless you set an ALT key shortcut on the tab, users have to go from keyboard to mouse to get to the controls on each tab. I try to limit hand movement between keyboard and mouse. I don't think the non-tab form is that big. It could be even shorter by resizing and moving some of the controls. They are just a little taller than needed for the font size. Maybe widen the form. I explored the split form when it was introduced and didn't like it. You built these forms with design wizards. I don't use the wizards. They do things I don't like (such as use macros) and make design edits harder. The controls will have to be 'ungrouped' for you be able to resize and reposition individually as you want. Select these controls, right click>Layout>RemoveLayout.

Also, recommend no spaces, special characters, punctuation (underscore is exception) in any names (tables, queries, fields, forms, reports, controls). If you do, must enclose in [] when referenced in queries or code. Access will try to provide the [] but spaces will confuse it, VBA never will. You may have discovered this when you built the expression in the PT Status query.

[BusDriver3]

WOW! Thanks for the insightful and detailed response... I am not sure of some of the details that you mentioned and I will have to do some more research (I'm still a noob) to educate myself... But I did get a chance to make some of the changes that you mentioned.

I will keep pressing forward thanks for you help and speedy response.

Please see new attached file.

[June7]

Does look good. You obviously learn fast. I expect you won't be a noob for long.

However, I am still not clear about the data relationship. Are Personnel and Events many-to-many? No reason for name field in Event Types table.

[BusDriver3]

It look a little bit but I now understand what you are talking about concerning the name cell and the relationship… no need for the relationship between the tables (I was using a Microsoft legacy file) so I deleted the relationship and the name field.

Do you think that there is anything else that I can do to this database
to make it more fluid, before I go public at my Command?

I deleted the Navigation Form so I could use a computer at work with a different version of Access...

Please see attached file. Thanks.

[June7]

The Navigation form, introduced with 2010, is just a wizard that builds a form with tab control and subforms on the tabs. As I noted earlier, I don't like using wizards. I would build this same structure manually with the tab and subform container controls.

Names are customarily broken into separate fields for the various parts (first, last, initial, suffix). It is easier to concatenate fields than split them. However, as long as you are consistent this will probably serve your needs.

Creating relationships in the Relationships editor is a good idea. Just need to get the jointype correct - "Show all records from Personnel ..." Not only for enforcing referential integrity, having these relationships established offers a convenience. Whenever you include these tables in a query, Access will be aware of the relationships and use the appropriate join clause on the key fields automatically.

I do have a concern about relationship between personnel and events. Still not clear to me if personnel particpate in more than one event. Right now the structure is such that each participant can have only one event.

[BusDriver3]

- I will build a switchboard for the database at work (we use 2007 at work and I have 2010 at home) I will take that for action.

- Unfortunately, my understanding about database relationships is still a little fuzzy, but I can do some more research to fix that.

- This database will be used primarily for scheduling and recording scores, once the PT Test is completed all the records will be exported and the database zeroed.

- The PT Test that we do in the Navy is conducted twice a year and each member is required to do only one event per cycle... So you get judged on the one event that you sign up for and that is it.

- This has been really great help. Thanks.

[June7]

Then seems the table structure is appropriate and ready for data. Congratulations!

[BusDriver3]

June7,

Thanks!!! I have attached a final file... the only difference is it has a option button for Gender... other than that I am calling this case closed! Thanks again.