Print Page | Close Window

Data Retrieval help please

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11446
Printed Date: 05 May 2024 at 4:04am


Topic: Data Retrieval help please
Posted By: colman1212
Subject: Data Retrieval help please
Date Posted: 18 Oct 2010 at 6:24am

I am joining two tables.

Customer table has a one to many relationship with Inventory table

I just want to pull back the customer table rows with the most recent row on the inventory table for that customer. There is a date on the inventory table so is it possible to tell crystal reports to just pull back the customer row along with the most recent row on the inventory table?

 
Any help will be much appreciated!


Replies:
Posted By: tschmid41
Date Posted: 18 Oct 2010 at 10:38am
You should just be able to select the MAX of the date.  That will return the latest(most recent) date.

Posted By: colman1212
Date Posted: 19 Oct 2010 at 4:29am
Can you explain to me exactly how this will work?

Posted By: tschmid41
Date Posted: 19 Oct 2010 at 4:45am
You will have to go into the Select Expert and create a custom formula that says where [date column] = Max([insert date column here]).  The Select Expert is located on the toolbar and looks like a hand pointing to a couple of marbles.

Posted By: colman1212
Date Posted: 19 Oct 2010 at 5:03am
Yeah I understand that but this will just bring back one row as being the max for every report. I want the max date per customer. So if there are ten customers on the report I want the max date for each of their inventory items, so ten dates, not one max date for the whole report.
 
For example: My report is currently showing:
 
 
Customer             Inventory No:
 
John                         200
John                         300
John                         400
Michael                     700
Michael                     800
Michael                     900
 
 
I want it to show:
 
Customer             Inventory No:
 
John                         400
Michael                     900
 

Posted By: tschmid41
Date Posted: 19 Oct 2010 at 5:21am
So add an AND statement which matches the customer names of the max and the other row.  Not currently sure about the syntax of that, I'll have to take a peak here a bit later today.

Posted By: colman1212
Date Posted: 19 Oct 2010 at 5:26am
Thanks for all your help.That doesnt really makes sense to me though. What do you mean by 'Other row'?

Posted By: tschmid41
Date Posted: 19 Oct 2010 at 5:28am
By the "Other Row", all I'm referring to is the row it would be currently getting checked against.  I'll look into this and get back to you later today.


Print Page | Close Window