Problem with maximum...

[Slingky]

I have 2 tables.

samples and product.

they are linkey by the field productNo.

But for each samples, there is a multiple match based on a field named revision...cause there can be multiple revision for each product...

ok check:

productNo: 101
description: product a
revision: 1
effectiveDateTime: 01/01/2002

productNo: 101
description: product a
revision: 2
effectiveDateTime: 01/01/2003

so for each sample with the productNo = 101
i have in this exemple 2 matches...

i have a field called LoggedDateTime in sample table.
and this field will tell me which revision to choose to get only one record in product table.

i set a record selection formula :
LoggedDateTime >= effectiveDateTime

but for all records for which loggedDateTime is >= 01/01/2003, i get two times each ones....

so i put a group selection formula :
effectiveDateTime = maximum(effectiveDateTime)

BUT NOW, i only get the records corresponding to revision 2.

So how can i solve this one?

 

[synapsevampire]

Join the sample table to the product table based on the logged field.

Don't use an outer join, and you'll only get one row per sample row.

Rather than trying to express data with descriptions, try posting good example data (you didn't describe which table the example data was from above, or if it was both, which wouldn't be very helpful)

Show table 1 example data, table 2 example data, and the expected output. As a bonus, if you add your Crystal and database version and how you're connecting to the database you'll probably get your answer the first go round.

-k

 

[Slingky]

i solved this one by:

1. group selection formula:
effectiveDateTime = maximum(effectiveDateTime,loggedDateTime)

and i removed my grouping on revisionNo...

all is ok now.