Table Design

[indyaries]

Table Design
Access 97

Greetings. Apologizing in advance for the lengthy post. Looking for clues on the best method to capture data for a management report. I have to capture and report on several categories pertaining to Performance, Leave and Discipline. I'm currently using Excel, but it's clunky.

There are a total of 26 Organizations, which is broken out as 9 Divisions with 17 Branches. Most of the Orgs and Branches are listed in the format in the example below:

ORG - IIEB
Branch - IIEBA, IIEBB, IIEBC

Branches provide their data, which is totaled and only shown at the Division level. Thus in the above example, IIEB-BA-BB-BC would input their data (current month) and it would be totaled and displayed as IIEB. Year to date would also be displayed.

Not all Division Organizations have Branches beneath them, and exist at the Div Org level only:
IAE, IIE, ISE, IXE

Data is collected in 7 categories:
1. Performance Management
2. Leave Management
3. Conduct Issues - Verbal Warning
4. Conduct Issues - Letter Warning
5. Conduct Issues - Written Reprimand
6. Conduct Issues - Suspension
7. Conduct Issues - Adverse Action

Performance Management has the following categories:
1. Standards Signed
2. IDPs Signed
3. Quarterlies Conducted
4. Performance Ratings Completed
5. Awards approved
6. Monetary
7. Non-Monetary
8. Time-Off
9. Performance Counseling
10. Informal PIP
11. Formal PIP

Leave Management has the following categories:
1. Advanced Sick Leave Requests
2. FMLA Requests

Conduct Issues all have the same categories:
1. Unauthorized Absense (less than a day)
2. Unauthorized Absense (one or more days)
3. Misrepresentation of T&A
4. Rude and discourteous conduct
5. Fighting
6. Insubordination
7. Use or possession of alcohol or illegal drugs
8. Sexual Harassment
9. Suspected Leave Abuse
10. Travel Card Debt
11. Other

I have to report on data each month, current month (end of month) and year to date.

I'm guessing that I'll need at least two tables, one for the Organizations & Supervisors, and at least one for the data I need to capture. Or, should I create a table for each category?

Suggestions on how to proceed will be appreciated.

I can provide an Excel example for any who need to see what I'm trying to do.

Thanks much,

Bob in Indy

 

[FoFa]

Just my input, but sometimes you have to let the data you are the most familair with drive the design somewhat. Typically you do not allow duplicate data. Saounds easy, but with the information you supplied I would at least start with an ORG table, A DIV table with a FK back to ORG. A branch table with a FK back to DIV. I would record the data at the branch level, and for those DIV's with no branches, leave the branch identifier blank (or some known value to mean no branches). Why, it makes the queries easier to handle. You could record information at the DIV level, and include a branch FK to a branch table as an alternative, both would work, and the later is a more normilized form. I would put your catigories and subcatigories in lookup tables, and allow something like Comboboxes to drive them and record the FK to the lookup table in your table to record the entries.
Just my 2 cents worth.
BTW hows Indy? I am from Muncie and did time at Ft. Ben H.
Been years since I been back however.

 

[indyaries]

FoFa,

Thanks for the guidance. I'll have to tinker with your suggestions and see if I can make them work.

Indy was nice today. Clear, blue skys, 82, low humidity. I've been at "The Fort" for over 20 years now. Worked on Post for the Engineers, but when FBH closed, I was RIFed into USAFAC (now DFAS-IN).

Most of what was FBH now belongs to the city of Lawrence. They've turned Colonels Row into high-priced townhomes. Same with "Sergeants Row".

I doubt you'd recognize it now.

Thanks again. I'll muck around with the design.

Bob in Indy