Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display info from 2 different records next to each other

Status
Not open for further replies.

jenschmidt

Programmer
Sep 30, 2002
145
US
Happy Friday!!

I want to display info from two different records next to each other, as if they are on the same record.

Example of the data:
TABLE_1.TX ID TABLE_2.Line TABLE_2.Prov ID
23515696

23515697 1 100038

23515698 1 130088
23515698 2 601


There are three scenarios that can happen -
1.) A transaction (noted by TABLE_1.TX ID) can have no records in TABLE_2

2.) A transaction can have one record in TABLE_2

3.) A transaction can have two records in TABLE_2

When scenario three happens, I want to show the Prov ID from line 2 on line 1, as in the following;

TABLE_1.TX ID TABLE_2.Prov ID TABLE_2.Prov ID (Ln 2)
23515696

23515697 100038

23515698 130088 601

Currently, I am using the following formula:
WhilePrintingRecords;
if {TABLE_1.TX_ID}={TABLE_2.TX_ID}
and {TABLE_2.LINE}=2
then Previous({TABLE_2.PROV_ID})

This works, but with the Previous function, I'm reduced in flexibility regarding sorting and possibly other formatting. I'm not sure how this will be used in the end, I've just been asked for help with the solution, but I want to make it as flexible as possible. I was thinking that some variable declarations might be needed, but I can't quite figure out how.

Any other ideas, or is this the way you would do it too?
Thanks!


jennifer.giemza@uwmf.wisc.edu
 
Group by tx_id

In the Report->Edit Selection Formula->Group place something like:

(
isnull({TABLE_2.Line})
or
{TABLE_2.Line} = maximum({TABLE_2.Line},{TABLE_1.TX ID})
)

This will select only those rows with the maximum Line, or allow for the Left Outer case of Line not existing (it will also pull back null Line fields so be aware of that).

-k
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top