Joint Data set - multiple join

Hi,

I have created a report that joins an Access database with a Showcase (iSeries) database. Everything is working fine and I was able (with help from here!) to prompt the user on the date range. The Access report contains the name, year of attendance and month of attendance (from attendance date). The showcase report contains the name, accounting year, and accounting period. Currently the databases are joined on name. If I run the report with prompts, the attendance is correct but the payment amount is for the entire year. I would like to join the datasets on year and month in addition to name. I googled the multiple join for a joint dataset and came up with creating a computed column in each dataset using the concatenation aggregation and then joining on those fields BUT can't get it to work.

Step by step please......If I create the column, choose "string", choose "concatenate".....what do I put in the "expression field" that will give me what I'm looking for?

Again....thx for the help
Brenda

Find more posts tagged with

Comments

bren1519

Ok....so I figured out finally that I needed to use the + to separate the fields that I wanted to concatenate, HOWEVER it's concatenating all the rows every time.

i.e.

Name
JOHN
JOE
BILL

Number
123
234
456

Desired Results
JOHN123
JOE234
BILL456

Actual Results
JOHN123/JOE234/BILL456
JOHN123/JOE234/BILL456
JOHN123/JOE234/BILL456

thx again
Brenda

mwilliams

Hi Brenda,

Can you show some actual sample data how it is in your two datasets and what you'd like to get in your joint dataSet? Something that shows your year/month issue? Thanks!

bren1519

Hi Michael,

I'm attaching a printscreen of what I entered in the computed column setup that produces the results shown in my second post on this question. Not sure what I'm doing wrong and your help is much appreciated!

Brenda

bren1519

<blockquote class='ipsBlockquote' data-author="'bren1519'" data-cid="66303" data-time="1278688293" data-date="09 July 2010 - 08:11 AM">
Hi Michael, 
 
I'm attaching a printscreen of what I entered in the computed column setup that produces the results shown in my second post on this question. Not sure what I'm doing wrong and your help is much appreciated! 
 
Brenda </blockquote>
 
Attaching a sample report and database to show how I set up the computed column. 
 
Thx

bren1519

Never mind Michael.....just figured out what I was doing wrong! I was using an Aggregation NOT just a concatenation.....fixed and working fine now

mwilliams

Sorry for the delay in getting back to you! Glad you found a solution! Let us know whenever you have questions!