Multiple records on one row?

[PTW]

Hi Everyone,

I have a table where several different records are related to each other. This table is then LEFT OUTER JOINed to another table to add a FirstName and LastName field. For example:

Emp_No, Trans_Type, Value_Type, Value, FirstName, LastName
1234, "New Hire", "First_Name", "Joe", ,
1234, "New Hire", "Last_Name", "Smith", ,
2011, "Change", "Address1", "123 Park St", "Sue", "Jones"
2011, "Change", "City", "Boston", "Sue", "Jones"
2011, "Change", "State", "MA", "Sue", "Jones"
2050, "Change", "Phone", "555-666-7777", "Al", "Walters"

Each Emp_No represents a different group of related records. The "New Hire" records have no FirstName or LastName records to link to.

Here is how I would want the report to look:

Emp_No, Trans_Type, Name
1234, "New Hire", "Smith, Joe"
2011, "Change", "Jones, Sue"
2050, "Change", "Walters, Al"

So all of the related records become a single record. I tried doing this by grouping Emp_No, but then I couldn't figure out how to get the two "New Hire" records onto a single line.

Any suggestions hon how to do this? I apologize for the length of this post. Thanks.

PTW

 

[Turkbear]

Hi,
Are you really saying that the second table has 2 or more records for each emp_no and what it contains in the 3rd field is defined in the second one?

Can you post the desing of the table with the first and last names?

 

[PTW]

Hi Turkbear,

Here is the first table (these are simplified):
Emp_No, Trans_Type, Value_Type, Value
1234, "New Hire", "First_Name", "Joe"
1234, "New Hire", "Last_Name", "Smith"
2011, "Change", "Address1", "123 Park St"
2011, "Change", "City", "Boston"
2011, "Change", "State", "MA"
2050, "Change", "Phone", "555-666-7777"

Here is the second table:
Emp_No, First_Name, Last_Name
2011, "Sue", "Jones"
2050, "Al", "Walters"

So to get the table listed in my original post I would do a LEFT OUTER JOIN on Emp_No.

The goal is to produce a summary report of New Hires and other employee changes, and I have to include names. I need to get these all on single lines in order to fit a specified reporting format. Thanks!

 

[adhhealth]

1. create a formula: f_l_name:

{Table2.First_Name}+","+{Table2.Last_Name}

2.create a group empno.

Display
Table1.Emp_No, Table1.Trans_Type , f_l_name

in the GROUP HEADER and not in the details section.

you should be all set!!

Happy Holidays!!

 

[PTW]

Hi Everyone,

Thanks for the suggestions. I ended up finding some info about the "3 formula rule" using the "whileprintingrecords" command in the group header, detail section, and group footer. That worked out quite well since I ended up having to do a couple of additional formulas based on single records in the recordset.