Help Displaying Data Horizontally In Report

[bgwool]

I am trying to create a report that formats some data horizontally instead of vertically, but I'm having a lot of trouble. The default report will display data like this:

Employee Date Hours Overtime
Mike 8.15 4 0
8.16 3.5 0
8.18 7.5 .25
8.19 5 0
___________
20 .25


Dave
8.17 6 0
8.19 5 0
8.22 3 0
___________
15 0

I need it to display like this:

Employee: Mike

Date: 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Hours: 4 3.5 0 7.5 5 etc...
OT: 0 0 0 .25 0 etc...

Total: 20
OT: .25

---------------------------

I have tried to use a pivot chart, but it's sloppy (especially because I need it to be printed & fit on a paper landscaped horizontally.)
I've attached a copy of the DB as an attachment. If anybody gets a chance to look at it, please open the 'Menu' form, click 'Report', try using the date range of 8/16/2014 to 8/31/2014 & click 'Report By Employee'. This will open 2 different items, a pivot chart form and a report.

Any insight is really appreciated. I've been struggling with this for months. Thank you!

[June7]

Doubt this will be easy. De-normalizing data can be tricky. CROSSTAB query is one way to de-normalize. However, building a report to run perpetually based on CROSSTAB is difficult.
Review:
http://allenbrowne.com/ser-67.html
http://www.access.hookom.net/Dynamic...rosstabRpt.htm

To emulate CROSSTAB with expressions, review: http://www.datapigtechnologies.com/f.../crosstab.html

Getting the Hours and OT rows could require two CROSSTABS in a UNION - something I've never tried - or subreports.

Alternative would likely involve lot of VBA code and temp table.

[bgwool]

Yes, I've tried using a crosstab query, which has worked fine as far as getting the data oriented correctly. But, you're right that the report based on the crosstab query didn't work well at all.

[ssanfu]

I had a peek at your dB. I have to agree with June... it won't be easy.

The main thing is that I see is the table "TimeCards" is not normalized. You have 8 fields for the survey type, 8 fields for the start time and 8 fields for the end time. This is known as "Committing Spreadsheet".
This is why you have to have 8 copies of the survey table in the relationship window.

The structure I would have for table "TimeCards" would be:

TimeCardID_PK	AutoNumber
EmployeeID_FK	Number Long
SurveyCode_FK	Number Long
WorkDate	Date/Time
StartTime	Date/Time
Endtime	Date/Time

It seems that the "short- wide" (spreadsheet) table is easier than a "tall- narrow" (database) structure, but later on in the development, it is very hard to create queries/reports. (I went down that road several times when I started working with databases )

To generate the report, you might be able to use VBA, a "Reports" table (to hold the data each time a report is generated) and new queries.

Good luck with your project.

[June7]

IMO, payroll/timekeeping is one of the harder database applications to design. Our timesheet is an Excel spreadsheet that users fill out and print and save the file to a central folder. Then I have code in an Access database that extracts data from spreadsheet and creates normalized records in tables. But then I have to twist it back for a report to the boss but not for issuing paychecks - he wants to track people who are submitting overtime. Timesheets are submitted to central office for payroll processing into some system far more complicated and advanced than Access db.

An example of my db in post 60 https://www.accessforums.net/forms/g...tml#post239648

[bgwool]

Thank you very much for the input. I definitely had issues with using eight different fields for survey/start/end. It came about because I needed to mimic the time cards we use for the entry form, and they have eight separate entry lines. How would creating a report with horizontal layout work if I had started with a better table setup?

[bgwool]

June, the report labeled "OTRequestWeeklyHours" (From 'Print OT Approval Request') in your DB looks almost identical to what I'm looking for. The 'Authorized' field would display the # of hours worked that date & 'Actual' would display overtime. I don't need them totaled by the week, just the bi-monthly period. I'm sure there is a lot involved in creating that, but it's exactly what I'm looking for.

[June7]

Changing the table to the normalized structure described by Steve will not alter the core issue of trying to arrange the data to the output you want.

[bgwool]

Hmm...it looks like you have a consistent 2-week period, so you can have 14 text boxes for the dates. My period changes depending on what month it is, so that may be trickier.

[June7]

Yes, our pay periods are bi-monthly (15th and 28/29/30th). But overtime laws are based on weekly (Mon-Sun), regardless of where the period cutoff is during the week.

Here's another you might find of interest. https://www.accessforums.net/databas...ses-18459.html

[bgwool]

Thanks, I'll check that out