• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Min/Max Date-Time Stamp Sorting

I have an ODBC Access data source that has data structured in this format:  Product ID, Value 1, Value 2, Value 3, Date-Time Stamp of when values were recorded.  The product ID repeats several times as readings are taken at different times - i.e., I have multiple entries for each ID.  I want to report in summary style, only the minimum and maximum date-time records with associated values in Crystal Reports.  Can anyone provide me an explanation that would allow me to summarize in this fashion?
0
mgt4rwg
Asked:
mgt4rwg
  • 2
1 Solution
 
mgt4rwgAuthor Commented:
Edited text of question
0
 
mayhewCommented:
Do you want the min and max for *each* product ID?

What version of Crystal are you using and how are you getting your result set (i.e. SQL Query or wizard)?

If you are using a SQL query, please post it and we'll take a look.  :)

However you populate your result set, the answer is going to be along the following idea.  You'll need to add a condition (for example, a where clause at the end of your select) that sets the data equal to the max OR the min.

In pseudo-SQL, something like:

SELECT <data> FROM <table> WHERE <date-time stamp> = (SELECT MAX(date-time stamp) from <table>)
OR <date-time stamp> = (SELECT MIN(date-time stamp) from <table>)

This will grab the high and low regardless of product id.  If you want this info for each product id, it's a little bit more complicated, but still doable.

Again, if you post the info asked for above, maybe we can give you a more concise answer.

Don
0
 
AnswerTheManCommented:
from my experoience with crystal reports - if u are not prepared well with your db be4 u go there - it's a waste of time.
do just this : prepare a temporary table with access with all
the records u want to show in the report. (only those u want !!
sort it out yourself in CODE !!!)
then - link crystal to this temporary table. it will work fine.
 
0
 
mayhewCommented:
AnswerTheMan,

I would agree with you if this were a particularly exotic query.  For this kind of thing, though, I've found Crystal to be adequate.  But whether mgt4rwg does it in Crystal or with code, he/she is probably going to need to know how to do the query.

Even so, instead of a temp table (can you even do that in Access?) I would just set up a query in Access and link to that through Crystal.

mgt4rwg,

If you post the kind of info I asked for above, one of us will be happy to look at it.

Don
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now