How to select a distinct record from only one table

[mbutch]

How do you select a distinct record from only one table?

 

[kenhamady]

I am not sure what you mean by a 'distinct' record, since all records are distinct.

If you mean "How do I select a single record? " the answer is to give Crystal a selection formula that is so specific that only this record qualifies. If two records are completely identical Crystal can't select one of them, because it can't tell them apart.

Crystal can, however, select several identical records and only display one of them, however, this is a different question.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[mbutch]

Here is the problem:
I have four tables that I'm using,
'cms.order' left outer joined to 'items.order'
'items.item' left outer joined to 'stock.number'
'stock.number' left outer joined to 'buyprice number'

The buyprice has duplicate records for some part numbers, so when I sum information from the items table, it doubles certain ones because of the duplicate numbers. I need to get rid of the duplicates in the buyprice table.

 

[kenhamady]

Crystal V8 has a "Select Distinct" option in the Report Options menu, also appears in the database menu. This assumes that all fields used in the report are identical. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

"The buyprice has duplicate records for some part numbers"
"I need to get rid of the duplicates in the buyprice table"
That is a probably database maintenance issue then.
Or do you mean the table has multiple records for some part numbers?
Duplicate records are bad in databases, as they indicate a violation of one of basic principles of databases. With most databases, this triggers an error.
If Select Distinct is not a solution for you (and assuming you have v8) then a running total can also fix you up - you can have the running total triggered on change of part number, so that it only gets fired once per part number.
If you have v7 or less, you are probably best to use the 3 formula approach to running totals (see FAQ on running totals). Malcolm
wynden@telus.net