Vertically Aligning Fields in a Sub-Report

[RichKorea]

I’m putting together a report for a service database where each service
ticket can have multiple call reports, and each call report can have multiple
engineers associated with the report. While all the data’s getting on to the
form, the vertical alignment shifts if there’s more then one service engineer.

What I would like to have is:
Data Eng #1 Task Data 1 Memo
Eng #2 Task Data 2 Memo
Task Data 3 Memo

What I’m getting is:
Data Eng #1 Task Data 1 Memo
Eng #2 Memo
Task Data 2 Memo
Task Data 3

The textboxes for Task Data 2 & 3 are shifting down to align with the bottom
of the last engineer, but I don’t want them to shift.

My data’s setup as follows:
The service ticket data comes from the main table
The Task Data fields 1, 2 & 3 come from a task table, which references the
service ticket’s key.
The service engineers come from a table that tracks which engineers worked
on which task (can be one or more then one), referencing the task key.

My report’s setup as follows:
The service engineers are in a sub-report that has a field for the
engineer’s name and is connected to the task key.
The tasks are in another sub-report that has the engineer sub-report on the
left, linked to the task sub-report’s task key, three textboxes stacked
vertically for three dates in the middle, and a textbox on the right that’s
connected to the task description, which is a memo field.
The service ticket data’s in the top level report, with the task report as a
sub-report.

Thanks,
Rich

 

[Duane Hookom]

I am confused by your description. I don't know is you have 3 Task Data
fields or 3 Task Data records. You mention 3 dates and memo field linked to
task description but don't provide information on which table or where task
description is located.

 

[RichKorea]

Sorry for the confusion. In my sample illustration, it was 3 fields. It's
possible to have multiple task records, but I left that out of my first
illustration to keep it simple. A better sample with three task records of
what I'm getting might be:

Task1 Eng #1 Task Field 1 Memo
Eng #2 UNWANTED SPACE Memo
Task Field 2 Memo
Task Field 3

Task2 Eng #1 Task Field 1 Memo
Task Field 2 Memo
Task Field 3 Memo

Task3 Eng #2 Task Field 1 Memo
Eng #3 UNWANTED SPACE Memo
Eng #5 UNWANTED SPACE Memo
Task Field 2
Task Field 3

Depending on the number of engineers, fields 2 & 3 are moving down on the
page, creating an unwanted space. The memo field, which is a single field,
expands to the size of the input data, without any blank lines, so it's
displayed nicely. It's just fields 2 & 3 that will move down if there's more
then one engineer (the engineer comes from a separate table, so right now
I've got the engineer as a subform with just the single field in it).

Thanks for your help.

 

[Duane Hookom]

Sorry if this is a repeat... Can you provide your actual table and field
names with their relationships?

 

[RichKorea]

Here's the applicable table/field/relationship info:

MAIN_TABLE ("SR" is the main table for the report)
SR_Key - autonumber key for the service reports
SRNumber – Number off the service ticket
SR_etc - there are a bunch of other fields for problem description, error
code, warranty/non-warranty which don't come into play for the task section
of the report.

TBL_ServiceTasks
TaskID - autonumber key for the task reports
SR_Key – ties the ServiceTask back to the ServiceReport. There can be
multiple Tasks associated with a Report
Task_DepartTime – when the engineer left the shop for the customer’s site
(times are all DateTime fields)
Task_StartTime – when the engineer arrived and started to work
Task_EndTime – when the engineer finished this task
Task_Description – what was done (Memo Field)

TBL_TaskCSE (CSE ~ Customer Service Engineer)
Task_CSE_ID – autonumber key for the task engineers
TaskID – ties the engineer back to the task. There can be multiple
engineers associated with a task (this is what’s causing my current confusion
on how to report in a nicely formatted way)
TaskCSE – ties the task back to a specific engineer
LeadCSE –which engineer was the lead for this task

TBL_CSE
CSE_ID – autonumber key for the engineers
CSE_Name – Text field for the engineer’s name (Tom, Dick, Harry, etc.)

The main report is based on a query to TBL_ServiceReports. I’ve got Service
Tasks coming in through a sub-report, and the Task Engineers coming into the
Task sub-report through another sub-report.

TaskCSE Sub-Report
Record Source: SELECT TBL_TaskCSE.TaskID, TBL_TaskCSE.LeadCSE,
TBL_CSE.CSE_Name
FROM TBL_TaskCSE INNER JOIN TBL_CSE ON TBL_TaskCSE.TaskCSE = TBL_CSE.CSE_ID
ORDER BY TBL_TaskCSE.LeadCSE DESC , TBL_CSE.CSE_Name;
Sub-Report Contents: a single text box in the detail section with the
control source set to the CSE name that comes back from the query.

Task Sub-Report
Record Source: Query to the ServiceTask Table
Sub-Report Contents: In the detail section, the TaskCSE sub-report linked
on the TaskID, three textboxes stacked vertically for the date fields, and
the task description (memo field)

Main-Report
Record Source: Query to the MAIN_TABLE
Contents: Data from the individual record, plus the Task Sub-Report linked
on the SR_Key. If there are multiple tasks, then multple sub-reports are
printed. The data from the individual records is about the same vertical
height on the page as a single task, so I don’t know if having multple tasks
would impact the alignment of the SR data.

Hopefully this is enough info to understand what happening.

Thanks,
Rich

 

[Duane Hookom]

If this is what you want
Data Eng #1 Task Data 1 Memo
Eng #2 Task Data 2 Memo
Task Data 3 Memo
apparently you don't need to show any connection between engineers and
tasks. If this is the case, I would create a subreport of unique engineers
for each SR. Place a subreport of tasks to the right of the engineers
subreport.

 

[RichKorea]

I though about having a separate column, but there wouldn't be any way to
assoiciate engineers with specific tasks (which is how they do the work), so
unfortunately, there is a relationship between the engineers and the tasks.
For example, on Monday, Frank goes out by himself on a service call (first
task). He finds the system needs to be removed and brought back to the shop
for repair (a two man job). On Tuesday, Frank and Bill go and pull the
machine (second task), and then on Friday, Bill and George re-install the
machine (third task).

I'm getting the impression there's no way to tell the three data fields to
no move/grow (I've got grow set to "No") so they stay put, rather then the
lower two shifting down the page if there's more then one.

 

[Duane Hookom]

You could possibly place engineers in a multiple column subreport to the
right of the task. You could also use my generic concatenate function to put
all the engineers in a single text box.

 

[RichKorea]

Thanks for the suggestion. Concatenating gives me a way to get almost
everything I want. I can fit a text box into the Task Sub-Report that can
show up to four lines of text. I can fill up the text box in the
Sub-Report’s Detail On Print event (I don’t believe it’s possible to change
the height of the text box at the On Print event) by running a query in code
against the TaskCSE table, and then filling up the text box with a name, and
then a Chr(13) & Chr(10) followed by another name. If there are more then
four names, which is really rare, I can substitute something like “and 2
others†for the last name.

Thanks for your help.

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.