Reporting data based on a dependent variable

[Jotun]

Hello everyone,

I'm trying to work out a problem I'm having with a report I've constructed to match our company's formatting. Before I move onto the report, let me explain how I've set up the tables and queries.

Tables: I currently have 3 tables. An Employees Table, a Project Table and a Time Worked Table. The tables are normalized to 3NL. I have Employee names and ID numbers in the Employees table (ID's are primary keys), JobID Numbers in the Project table (Primary key), and the Time Worked Table has foreign keys tied to both, and hold all the other data including job specifics, hours worked, employee names and ID's and total hours.

Queries: I have two queries, one that queries data form the Employee's table and one that queries data from the Time Worked table. The form I use is RecordSourcing the Time Worked table.

Now, with that said, heres my dilemma. If you take a look at my excel attachment, you'll see the format to which I must mold this report. I have very limited experience in Access, so setting up code/expressions to do what is asked of me is getting much too frustrating! Please read on.

Firstly, the report is centered around the Employee. Through my query, I was able to get Access to pull employees off the list in ABC order one at a time. This was a big step for me, since my last attempt at this database was completely worthless. If you look at the period ending, that is actually two dates: [Beginning Date] and [Ending Date] since this report has to prepare data for the pay period. I've set up the aesthetics of the report without a problem, including the expressions which put all the header dates in the right places. My goal for this report: to show a particular employee name/number and from that fill out his days worked on a particular job, descending numerically and placing his hours undernearth the appropriate date for the appropriate job number. It really seems simple enough but I'm completely road-blocked.

I hope the information I've provided is useful PLEASE don't hesitate to criticize my layout or offer better means to my desired end. As I've said in other posts, I'm a total noob at this and only have about 1 more week left on my internship to see the database completed. Thanks so much guys! You rock :rockon:

Please see my rapidshare.com download for the excel doc:
http://rapidshare.com/files/47937712/TIMESHEETS-Edit.xls.html

 

[Jotun]

Bumping, still need a hand.

 

[PeterF]

I'm glad you decided to redesign your table structure, to get the desired layout you need a Crosstab query (the wizard can help you with that). To make a comment on your structure: I hope you didn't put spaces in your Table names as they can make things difficult in the future and I strongly recommend to use some kind of naming convention (for example this one or this one)

There will be some things that wont work as expected:

In your previous layout you had fields for start and end time, although it's no problem to subtract them in Access and display this result. To get a total from that you need to know how Access handles Date/Time fields.
Access (or all M$ products) use a base date (01-01-1900) and the number of days from that to calculate the date, the time is a fraction of a day. This means that when you sum your calculated time you will get 1.66 for 40 hours (1 day = 24 hours and .66 = 16 hours) multiply your result with 24 gives the desired 40 hours for reporting.

A crosstab query can't have a lot of fields (Access can't do more the 255 fields in a query), so you have to limit this by a filter. The easiest way to do this is to make a form for reporting where you enter a date (end or start of a week) and calculate the other date to use in your query as filter,
reference it like this

>=[YourReporFormName]![txtStartDate] and 
<=[YourReporFormName]![txtEndDate]

or use the wizard to help you construct the filter criteria.
This way you know for sure you have a 7 day week and can check for the correct day entered by simply use the validate function of the control, for example:

Weekday([YourControlName])=1 'for a Sunday

.

Edited: Reason added a link to a better explanation why to use a naming convention.

 

[Jotun]

Thanks again for the thorough explanation Peter, I'll be sure to apply these concepts to my database right away!

 

[Jotun]

Alright, I've done as asked - renamed everything, basically started from scratch. My Database is tabling data properly and everything is ready to be reported. With my limited knowledge, I can't make this report as my employer is requesting; I just dont know how.

If you look at the file I've attached, and refer back to my post, you'll see what I need to do. I understand that I can't use a "Beginning Date" prompt with a crosstab query, I also am limited to the fields I can report, and to the one value that can be totalled.

How do I write syntax for putting ONE employee at a time, in the "Employee Name And Number" area, and from his name, list ONLY the job numbers hes worked on, and ONLY put hours in the dated boxes that he has worked. So, if "Brian Soandso" Worked 3 days, 3 different jobs, I'd want to see his name in the spot, and those three jobs, ascendingly ordered with hours worked in each box corresponding to the date header.

This is WAY to complicated for me, but our bookkeeper insists it has to be this way. If anyone needs to take a look at the database, please let me know!! I can send it over upon request. I'm boiling down to the zero hour as I've indicated in my first post, and I'm not sure how to finish!

 

[PeterF]

I made a sample database so you can see how to do things.
I used your Exelsheet as a header but didn't take the time to covert it to access so some colums will be out of line.

Hope this helps.

 

[Jotun]

Thank you!!

 

[Jotun]

Alright! I've set up everything to my liking, and I'm really comming along. I have a few questions, however.

1) If our bookkeeper has to go back and put in the same job number with a different date, I'd like access to auto-fill certain feilds that aren't indexed and allow duplicates.

2) When our bookkeeper goes back and puts updated dates under the same job number, it stacks the data in the report. IE it gets put in twice with a second job number. Is there a way to make it add itself and total itself in the crosstab?

3) I need to show that certain jobs were "Extra Work", however I'm out of rows in the crosstab (since you're limited to 3). I don't know how to solve this one.

4) I have another set of numbers to show on this report, called "Special Hours". Can I just create another crosstab and use an SQL query to report the data?

Thanks so much for your time! I couldn't have made it this far without your demo-database!

Jotun.

 

[PeterF]

1) I have no clue what you wan't to do here it may be that the dlookup function can help you but it cauld be easier to copy the record and alter the date.

2) If you make your select query grouped you can do the math there.

3) You could join the Extra work with one of the other fields in the query and split them in the report, if you use a nonstadard character to join the fields like

YourFirstField & "°" & YourSecondfield

you can use the split function to seperate them again like:

split(YourCombinedField,"°")(0) 'for the first part and
split(YourCombinedField,"°")(1) 'for the second part

4) Yes you can with a extra subreport.