Calendar like layout of records

[buratti]

Hello All. I’m revisiting a post I placed about a year ago about how to layout records in a certain way.

What I am trying to do is create a form or report that summarizes all of my employees daily hours and tasks for each week. The actual layout is what I am having trouble with here. The end "visual"/printed result I need it to be is explained as follows:

Picture a spreadsheet... Row 1 columns A through whatever are employee names...
Row 2 states all the work completed and hours for Monday for the employee in that column.
Row 3 states all work for Tuesday
Row 4 for Wed. and so on until Friday.

Then once a week I print a copy of everything from Mon to Fri and it would display all work/hours from all employees in a chart/calendar, or whatever name you would use to describe this type of layout.

Each cell (A2, B3, etc.) would be a separate RECORD from my employee Log table.

What I do have already is a normalized database with an employee table, an employee log table (and others that don’t relate to this post), along with an employee form with Log subform for entering daily tasks and hours for each employee. The problem with this though is the way it is set up, I can only enter or view one employee at a time. I have a combo box in the header that I use to switch between employees and enter their info. What I have now is great for entering data each day, but I need that "spreadsheet" weekly summary of all employees to print once a week.

I have attached my DB here. The form "Employees" is where I enter daily data/hours. The form "Employee Worksheet" (along with report "Print Employee Worksheet") is the way I would like the data displayed. But the way I have it there is un-normalized and such a hassle to change if I add, remove, or change an employee. I would like to use that layout, but somehow have a combo box displaying the employee name and if that value is changed, that column of data is changed to reflect the new value.

Any suggestions?

 

[wiklendt]

why don't you have the employees down the page instead? and the work details along the top - that way, if you change your employees the report automatically draws on all the employees you have.

and i'm afraid your table "Employee Worksheet" is not normalised. what i think you need is probably a junction table to utilise Many-Many relationship between employee and what ever all that data is.

i would also suggest that you use proper nomenclature for your tables (e.g., tblEmployees), forms (frmEmployees), and PK/FK pairs: (e.g., EmployeeID in all your other tables is fine, but it should also be called "EmployeeID in the Employees table) - also all your other tables only have "ID" as the PK, these should be renamed into something a little more meaningful (you'll thank me for this advice later!) e.g., DayOffID.

Also, your relationships are not selected for referential integrity - i suggest you enable this (double click on the relationship line, select for referential integrity), and you have missed making a relationship between Employee and Vacation_Time_Taken (for your EmployeeID).

Your "Employee Worksheet" table should also have EmployeeID - so you can see straight away that the table as you have it is incorrect. read up on junction tables in this forum, and post back if you have issues.

 

[buratti]

Thanks for the suggestions. One reason why I cant have the employees down the page and work across the top is that I am trying to create this for my boss and he currently does it all hand written on a piece of paper in the same format I am asking for, and he DOES NOT LIKE CHANGE!!! If he's willing to change the process to a computerized version then it has to look exactly the same as he is used to. I know it sounds silly, but that is the way he is!!!

As for the "employee worksheet" table not being normalised... I know. That is just an original table I came up with (and the only way I could figure out how to format it the way i need at that time) that I dont use. I just added it as a way to show you guys the actual format I would like it.

I have never used junction tables before and wouldn't even know where to start with one. i will search around through here, but does anyone know of a good reference or website that gives good examples and descriptions on how to use them?

I may have figured out a way to get the format I need though, but it probably isn't the best or "correct" way to do it and I dont even know it is possible. What i figured out is to create an unbound form and place 5 or 6 subforms side by side (one representing each employee). In the forms header I will have dropdown combo box to select a different employee for each column (I will have each one set to a different default value). These subforms are all based on the same form, but the record source for each one will be changed/filtered in the onLoad event of the main form to reflect whatever employee is listed in that column's header combo box.

My problem here is, the VBA code below I get an error "Data type mismatch in criteria expression"

Private Sub Form_Load()
Me!Employee1Subform.Form.RecordSource = "SELECT * FROM EmployeeLog " _
        & " WHERE EmployeeID = '" _
        & Me.cmboName1 & "'"
Me!Employee2Subform.Form.RecordSource = "SELECT * FROM EmployeeLog " _
        & " WHERE EmployeeID = '" _
        & Me.cmboName2 & "'"

    
End Sub

Here coboName1 & 2 are the UNBOUND combo boxes I was describing earlier. The row source type for those combos are Table/Query and the row source is: SELECT Employees.ID, Employees.FName
FROM Employees;
I understand the error, meaning "EmployeeID" is a number format, and "cmboName1" is NOT. But being an unbound field, how would I change its format to a number format?

 

[wiklendt]

He DOES NOT LIKE CHANGE!!! If he's willing to change the process to a computerized version then it has to look exactly the same as he is used to. I know it sounds silly, but that is the way he is!!!

"the way it is" is that computer programs work in a logical, systematic way. just tell your boss it cannot be done 'his way' without making a mess of things. eventually he'll come around to the new system (he might not like change, but i'm sure your boss has had to accept and deal with changes in most of his working life).

edit: my supervisor was resistant to many changes and 'formats', but has subsequently kissed the ground i walk on for forcing the issue.

 

[wiklendt]

I have never used junction tables before and wouldn't even know where to start with one. i will search around through here, but does anyone know of a good reference or website that gives good examples and descriptions on how to use them?

took me a while to get my head around it too. aside from this forum, you can find many examples and advices on the web.

in a google search, i entered "access junction tables" and got very many useful hits. here is a selection:

http://www.foresightsoftware.com/AccessJunctionTables.htm

http://www.google.com.au/url?sa=t&source=web&ct=res&cd=10&ved=0CFUQFjAJ&url=http%3A%2F%2Fwww.ieor.berkeley.edu%2F~ieor115%2Flabs%2FLab3-MSA-Relationships.doc&ei=3BX_S_6sJ4rBcbrX5IcK&usg=AFQjCNGQaQ_8DOV357EiJPYaIp-DkTqjGg&sig2=XABATUYXlMcOBKYPKhHfEQ

http://www.eggheadcafe.com/software/aspnet/32695174/junction-tables.aspx

http://bytes.com/topic/access/answers/194566-implementing-junction-table

http://en.allexperts.com/q/Using-MS-Access-1440/Junction-table-2.htm


...i'm sure you can trawl through google yourself and find which kind of tutorial works best for you. search the "sample databases" on this forum to get 'real' examples - not sure how many there are, but there're bound to be some there.

 

[HiTechCoach]

About change:

I have found that with software, and most things, a lot of the resistance to change comes from the fear that the change will make their life more difficult. Change is not normally seen as an improvement. I hate to say this, but I have seen way to often that update/change actually have a negative impact on productivity or work flow.

If you can show that the changes will be an improvement then you can do a lot to lower the resistance. Getting the users to collaborate in the design can also help insure that it is actually an improvement.


About junction tables:

Most people have seen the example of an order or invoice. Where there is a Invoice header table and an invoice details table. The invoice detail table is use to link multiple products to a single invoice. Include in the details are things like quantity, etc. This is a great example of a junction table. I think that this is often overlook as a great example if a junction table.

Hope this helps ...

 

[wiklendt]

If you can show that the changes will be an improvement then you can do a lot to lower the resistance. Getting the users to collaborate in the design can also help insure that it is actually is an improvement.

i couldn't agree more.