Discussions

I need to create a report that pulls information from 3 tables and stacks the information on top of each other instead of everything on one line. Is this possible?

Did you try sorting and grouping option, so that you can achieve what you are trying to do in some (may not be 100%) meaningful way?

No I need info from table 1 on top. Info from table 2 in the middle. Info from table 3 on the bottom. They contain different info that has to be broken out and kept separate. Sorting and grouping can't accomplish this.

Is there a relationship between the different tables?  What do they have in common to match up the records.

They are related by a date
The top section records employee time, the middle section is for equipment used on the job and the bottom is information about the jobs. It is entered in the field electronically by the workers and the report is used by accounting.
Employees do X jobs per day using equipment and time. This report is used by data entry to enter info in to accounting. It has been tracked in Access until this year, reports created exactly as I've described, but we are no longer using Microsoft office therefore no more Access.

Are you saying that there is essentially 1 record per date?

If that is the case, make a table and load it up with a bunch of dates using excel.  For testing purposes, just hand enter a few dates.

Set the key field as the date field.

Make a three relationships to the respective three tables, but for your use case let the relationship builder create a new field initially called related date.  But then once you make the relationship, change that field to be a formula date field equal to the date field and call it [Date Mirror for Relationship]

Then on each date record, you can have three clean embedded reports for the Hours, Equipment and the Job Information.

The report has to be printed and signed by the employee and turned in to the admin so embedded reports wouldn't work. But I appreciate the input.

Huh? Let's not give up too quickly.

Why can't the record be printed and it will print including the three embedded reports and the EE signs the paper?

Hi Steve,

I have done something like this in the past using a formula rich text field and a little HTML or CSS. Essentially on the child record you would make a field that you could then set up to draw in and style text from various fields. This way on the table at the very bottom you can have all the fields but then you can draw them all into the formula rich text field to style and colorize the data to make it appear less like one long list and more like a block entry. I have done something in the past when I wanted a dashboard that had a little more visual interest. It just requires you to pick a consistent way you want to display the data and build out the formula. 

"<div style=\"text-decoration:underline;font-weight:bold;font-size:150%;font-family:Goudy Bookletter 1911;\">"&[First Name]& " " & [Last Name]&"</div>"   &  "<div style=\"font-style:italic;font-size:120%;\">"&[Job Name]&"-" & [Job Date] &"</div>" & "<br>" &    "<div style=\"font-weight:bold;font-size:120%;\"> "What tools were used on this job?" & "</div>" &    [Tools Used] &"<p>" &    "<div style=\"font-weight:bold;font-size:120%;\">"  & "How long did the job take?" & "</div>" &    [Total Number of Hours] &"<p>" &    "<div style=\"font-weight:bold;font-size:120%;\">"  & "What other resources were used?" & "</div>" &    [Resources Used?]    That is just an example of a type of formula rich text field I have used with a little CSS for this in the past just to end up with a report that has some visual depth to it.