Shift log

[Canyonroom]

I have an excel workbook that is used as a shift log for entering daily shift activities. It has 3 pages, 1st page has: supervisors on each shift (3 shifts) and Shift daily entries (most of the fields are the same for each shift) Page 2 has open items that are carried over to the next day and Page 3 is used for comments.

The .xls works great and is printed and cleared at the end of the day but we now need to track some fields as well as general look up of past dates (vs looking through the paper binder)

My question is about the tables. Should I list the items contained on each page or put it all on one table? I'd be happy to provide more info if needed.

Thanx

 

[The_Doc_Man]

When designing an application for Access, remember that Access is a modeling tool. You model aspects of what the business does and what the business has as resources.

You have shifts, supervisors, log entries, carry-forward situations, and the need to report things on a daily basis.

When you do this sort of thing in Access, you will need a table of supervisors that tells you about the supervisors, including a supervisor ID.

You will need some sort of shift table that gives starting and ending times, supervisor ID, and maybe a code number acting as a shift ID. This number could be encoded, but an ordinary autonumber might equally suffice, since you could use that number to find the other info anyway.

You will need a log-entry table that has a few fields such as shift ID, the text of the entry, and whether it needs to be carryied over. (Perhaps this is just a yes/no flag that says "COMPLETED" - if the flag is checked, you don't carry it over.)

If you need more than this, like perhaps the name of the person making the entry if it isn't the supervisor, then the personal ID code would go in the entry. (This implies that the supervisor table wouldn't be limited to supervisors.)

If you wish to keep comments separate from the other log-entry items, a separate comments table would be OK, though it might be just as easy to include it with the log-entry table but include a yes/no flag that says "COMMENT" - in which case the entry isn't something that needs to be completed.

This multi-table model tries to track the description you gave us of your business. Your question "Should I list the items contained on each page or put it all on one table?" possibly betrays an Excel mindset. Remember, spreadsheets and databases are BOTH powerful tools - but they do very different things.

 

[Canyonroom]

Thank You

If first want to write a big THANK YOU! for the response. It was probably the best response that I've ever received. I know you are right about the mindset thing. I've been in an Excel/vba world for a long time and just needed the kick start into relations.

The program I wrote over 5 years ago is a VB GUI front end that basically controls 80% of the programs XL files with "a lot of VBA". Its pretty seemless to the users (over 200) BUT alas is all "flat" if you will pardon the XL pun. My boss doesn't see the need to upgrade most of it to Access. His comment of "it works" is what I'll be trying to change.

thanx again

 

[The_Doc_Man]

The way to convince your boss is to point out the improvements in the kinds of reports he can get and how far back he can go with data requests to see the shift log for a given date.

If you add dates on the "Completed" flag as well as the entry itself, you can rebuild your ENTIRE shift log for any date.

You would be able to find all entries made by Supervisor XYZ in chronological order.

With dates on completion as well as entry, you would be able to figure out processing times for certain actions.

Usually, though, you have to be careful when energizing the boss. Suddenly he finds out you can do more. Then this impudent person decides he WANTS you do to more. Just because he's the boss. So of course making improvements in any situation like this is a double-edged sword.

 

[Canyonroom]

I am fired up about the possibilities of doing it in Access. The users that actually run my program are fired up as well. The boss? I'm sure he will see the light. Especially if I can crunch some $$$$.

 

[The_Doc_Man]

If you really want to get the boss energized, just remember that, when compared to straight VB, Access is considered a "rapid application development" tool.

The forms will take a while, but the tables and supporting queries will probably take less than a day of total time. And as long as you are careful about separating items that don't belong together, it should be easy to build forms using the wizards. Then just go back and use the drag-n-drop editing ability to make the form look like you want.

The Access wizards are VERY dumb when it comes to the aesthetics of layout. But they can give you a BIG head start on having fields defined on that form. After that, just move 'em where you want 'em.

 

[Canyonroom]

The log is coming along very well. Thanks again for your assistance.

Next is the scheduling program for 200+....