Query for Highly Custom Report 1

[bgv]

I have a report to be created will need to look like:

Date Hours Date Hours Date Hours
Training Course #1 02/01 2 05/20 2 08/20 2
Training Course #1 04/01 1 05/10 1 04/20 1

The data comes out of an activity table in sequential record format by date and a lookup table for the training course types.

My SQL query so far looks like:

SELECT frs_codes.parent_code_id, frs_codes.code_id, frs_codes.code AS course, frs_employee_activity.employee_id, Format([start_date],"mm/dd&quot AS courseDate, DateDiff("h",[start_time],[stop_time]) AS courseHours
FROM frs_activity RIGHT JOIN (frs_employee_activity RIGHT JOIN frs_codes ON frs_employee_activity.training_id = frs_codes.code_id) ON frs_activity.id = frs_employee_activity.activity_id;

The query also selects the employee_id so training records can be selected for a particular employee in a given year.

How can I set up this report to look this way (with dates and hours displayed horizontally), either by writing a query or through formatting the report in a certain way? I am stumped!

 

[dhookom]

You can use a multicolumn report or the Generic Concatenate function from the demo at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bgv]

Duane,

Thanks for your help. Discovered that there is not much online help on multi-column reports in Access. Nothing at all either in the two Access2000 Development books I have either. It is interesting that I have found most of my Access Dev help using this forum and have pretty much wasted money on my Access2000 development books.

Anyway, my first approach was to try the multicolumn report and I have the report almost the way I want it . Noticed that in this type of report though, that you create the report using a half-page format and that the Page and Report Header cannot be centered on the page as in a single-column report. Is there any way to center a report title in a multi-column report? I have also tried creating the header as a master report with the multi-column portion as a sub report. Does not seem to work at all.

Help! I'm all ears!

 

[dhookom]

You can use a full width page with multicolumn reports by un-checking the column width the same as the detail section (something like that) in the column setup. There is a kb article

http://support.microsoft.com/default.aspx?scid=kb;en-us;210044

that explains how to keep your labels on the left.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.