Is a many to many relationship needed?

[jsaddiction]

Ok so I am trying to build an application for users to log activities or accomplishments. My job requires me and everyone else to construct employee evaluations every six months or every year depending on our rank. The work flow is as follows:
1. Users login and enter information about their accomplishments
2. Users login and view statistics about their accomplishments
3. Users login and view reports sorted by the applicable competencies when reviews are due

I started off with an accomplishments table and an employees table. I have been able to accomplish both item 1 and 2 using mostly VBA but i am stuck on item 3.

The problem is this. Each row in the accomplishments table has 25 competency columns among other items. The competencies are yes/no check boxes. For each accomplishment entered by the user, he or she must decide which competencies apply to the accomplishment. For instance, the accomplishment may have some text in the action column such as "I painted the entire 02 deck" the user must now accociate some compentencies with that action and he/she may select "professional knowledge", "using resources", "working with others" and various others.

I am trying to develop a report to show all of the users accomplishments between two dates but organized by these competencies. This should have a heading of all the 25 competencies and all the actions that apply to each competency listed under that heading.



I could construct a query to select all records between two dates that have a check in one of the competencies and then use that as a record source for the report. I don't know how to include all of the other competencies.

When i was designing the accomplishments table i though i might have needed a many to many relationship to connect each action to one or more competencies listed in another table but i was unsure of how to do this effectively. I tried to skirt around my ignorance by having a bunch of competency columns but now i am having difficulties extracting the data in a "makes since" kind of way.

I am now thinking I only have two options.
1. rebuild the database using the many to many relationship
2. use VBA to pull data with 25 different querys of the database and populate controls on a query.

What do you suggest??? Have I programed myself into a wall??

[June7]

These 25 yes/no columns is not a normalized structure. Yes, sounds like many-to-many relationship.

Is there a standard set of accomplishments available for users to select from? Or is this totally free text entry?

[jsaddiction]

The 25 columns are only yes/no options. for example, if the user opened the accomplishment entry form he is presented with a text box where he/she will enter what they did. This is saved into the "action" column. He/she will then put a check next to one or several compentencies that this action applies to. The form is closed and now each accomplishment in the accomplishmets table is associated with a date, employee id, the action, and any of the 25 competencies that apply to the action.

I would like the report to have a heading for each of the competencies and under those headings i would like a date and the action

[June7]

If you mean a column heading for each competency then the output you describe repeats the date and action under each associated competency heading. Doesn't make sense.

If you mean a single column for competencies then grouping in a report with related data below each competency, that requires normalizing the data structure.

The current structure could have a report with groupings by employee ID and date and action then the competencies would be a row of checkboxes or textboxes with yes/no in the detail section.

[jsaddiction]

I am not sure if i understand what you mean June7. I would like to have every accomplishment that is associated with "professional specialty knowledge" under a label with the caption "professional specialty knowledge". Each accomplishment under that heading should have the "date" it was entered and the "action" text describing what the employee did on that day. This should be repeated for each of the other competencies like "professional specialty knowledge" I have started working towards creating the entire report from VBA but this is problematic as it is requiring a lot of code. I tried to create a basic template that i could modify with vba at run time but apparently you can not add controls unless you are in the design view. besides constructing the report from VBA the only other option i have is to redesign the database using many to many joins and looking at all the background processing and user interface it seems that i would be best served starting from scratch. I hope this isn't the case as the report is the only thing left to construct before the application is ready for testing.

[June7]

I have used VBA to write data to a 'temp' table and that table is the source for report. The table is permanent but the records are temporary. They are deleted after reporting process completes. This allows the report design to be static. That might be the approach you need with current data structure.

Or maybe a UNION query would serve. A UNION can manipulate non-normalized data into normalized structure.

Maybe example of raw data and a picture of your desired output would help.

[jsaddiction]

Mark Me Database.zip

here is a copy of where i am on this project. please excuse the code i have not been able to clean it up at all. If you log in under any of the users the password is "pass" Jtlawrence is the admin account (able to add modify users) once logged in click on "view my bullets" and a report will show but the design isn't really established since i could not get the data to populate very well. The reports "onload" event triggers various subs that were meant to populate the report using vba but i commented out the conflicts so that the application would run and not cause errors.

[June7]

Here is UNION query that rearranges data to more normalized structure. This more closely represents what your data would be in a normalized table. Actually, would probably be two tables.

It can be the datasource for the report. Create a group in the report for Compentency and put other data in detail section. May even want groups below the Competency group. No VBA code required behind the report.

UNION queries must be typed into SQL View of query builder. You can copy/paste from here to your db.

SELECT ID AS ActionID, When, Emplid, Priority, Action, "ProfessionalSpecialtyKnowledge" AS Competency FROM Accomplishments WHERE ProfessionalSpecialtyKnowledge = True
UNION SELECT ID, When, Emplid, Priority, Action, "QualityOfWork" FROM Accomplishments WHERE QualityOfWork = True
UNION SELECT ID, When, Emplid, Priority, Action, "MonitoringWork" FROM Accomplishments WHERE MonitoringWork = True
UNION SELECT ID, When, Emplid, Priority, Action, "UsingResources" FROM Accomplishments WHERE UsingResources = True
UNION SELECT ID, When, Emplid, Priority, Action, "Safety" FROM Accomplishments WHERE Safety = True
UNION SELECT ID, When, Emplid, Priority, Action, "Stamina" FROM Accomplishments WHERE Stamina = True
UNION SELECT ID, When, Emplid, Priority, Action, "Communicating" FROM Accomplishments WHERE Communicating = True
UNION SELECT ID, When, Emplid, Priority, Action, "ProfessionalDevelopment" FROM Accomplishments WHERE ProfessionalDevelopment = True
UNION SELECT ID, When, Emplid, Priority, Action, "DirectingOthers" FROM Accomplishments WHERE DirectingOthers = True
UNION SELECT ID, When, Emplid, Priority, Action, "WorkingWithOthers" FROM Accomplishments WHERE WorkingWithOthers = True
UNION SELECT ID, When, Emplid, Priority, Action, "DevelopingSubordinates" FROM Accomplishments WHERE DevelopingSubordinates = True
UNION SELECT ID, When, Emplid, Priority, Action, "Responsibility" FROM Accomplishments WHERE Responsibility = True
UNION SELECT ID, When, Emplid, Priority, Action, "Evaluations" FROM Accomplishments WHERE Evaluations = True
UNION SELECT ID, When, Emplid, Priority, Action, "WorkLifeSensitivity" FROM Accomplishments WHERE WorkLifeSensitivity = True
UNION SELECT ID, When, Emplid, Priority, Action, "SettingAnExample" FROM Accomplishments WHERE SettingAnExample = True
UNION SELECT ID, When, Emplid, Priority, Action, "MilitaryBearing" FROM Accomplishments WHERE MilitaryBearing = True
UNION SELECT ID, When, Emplid, Priority, Action, "CustomsAndCourtesies" FROM Accomplishments WHERE CustomsAndCourtesies = True
UNION SELECT ID, When, Emplid, Priority, Action, "HealthAndWellbeing" FROM Accomplishments WHERE HealthAndWellbeing = True
UNION SELECT ID, When, Emplid, Priority, Action, "Integrity" FROM Accomplishments WHERE Integrity = True
UNION SELECT ID, When, Emplid, Priority, Action, "Loyalty" FROM Accomplishments WHERE Loyalty = True
UNION SELECT ID, When, Emplid, Priority, Action, "RespectingOthers" FROM Accomplishments WHERE RespectingOthers = True
UNION SELECT ID, When, Emplid, Priority, Action, "HumanRelations" FROM Accomplishments WHERE HumanRelations = True
UNION SELECT ID, When, Emplid, Priority, Action, "Adaptability" FROM Accomplishments WHERE Adaptability = True
UNION SELECT ID, When, Emplid, Priority, Action, "Judgment" FROM Accomplishments WHERE Judgment = True
UNION SELECT ID, When, Emplid, Priority, Action, "Initiative" FROM Accomplishments WHERE Initiative = True;

[jsaddiction]

that union was something magical!!! I know i am being picky but when the report is generated the order of the competencies seem to be sorted alphabetically. Is there a way to keep it in the order listed in your union query?

secondly, I will need to build a dialog box to limit the query to both time and emplid. for instance, the dialog box pops up and the user selects the user he/she would like to generate the report for, a begin date and an end date to limit the results and size of the generated report. I have done something similar to this process in the stats form but how could i implement this into the above union? how much can i add to the where clause?

[June7]

If the ID field doesn't provide for the desired order, create another field in the UNION for use in ordering records. Set sort criteria in report design.

... Action, 1 AS SortOrder, ...
... Action, 2, ...

Options:

1. can add as much filter criteria to each SELECT as you want

2. use the UNION like a table - in another query with dynamic parameter filter criteria and use that query as RecordSource for the report

3. use direct reference to the UNION query object (or an SQL statement such as SELECT * FROM MyUnion;) as RecordSource for report and apply filter criteria to report when it opens
DoCmd.OpenReport "report name", , , {filter criteria here}

Option 3 is my preference.