remove multiple records

[DBAFrog]

I have a record where the each date entry pulls the description with it.

ie
12/1/04
charge 1
charge 2
1/5/05
charge 1
charge 2
and so on...each date pulls the orginal charges into the table. Now, when I try to spit out the charges I get

charge 1
charge 1
charge 2
charge 2

and so on...
I tried to get a FIFO on the records, but I get The Summary/running total field coud not be created.

formula: {DISPOSITION.HEARING_DATE_TIME}= maximum({DISPOSITION.HEARING_DATE_TIME},{DISPOSITION.DISPOSITION_CODE})

IF I suppress dupes I get large gaps and there are times when there are multiples of the same charge.

tia...


This was the basis for this formula, from my search of the forum:
If you're pulling from inventory, then you might use a FIFO to grab the oldest inventory by using the date instead, as there's probably a date field for the date the items were placed:

{table.date}= minimum({table.date},{table.item_id})

-k

------------------
Curious by Nature,
Linux by Design

 

[pelajhia]

are you assigning the date that matches to a variable?
for instance, as you are going through each record:

shared datetimevar max_dispdate;
if
{DISPOSITION.HEARING_DATE_TIME}= maximum({DISPOSITION.HEARING_DATE_TIME},{DISPOSITION.DISPOSITION_CODE})
then
max_dispdate:=formula: {DISPOSITION.HEARING_DATE_TIME}

then use a footer formula to display in the group footer:
shared datetimevar max_dispdate.

 

[DBAFrog]

are you assigning the date that matches to a variable? " ~ NO
I am trying to pull all FIRST OCCURANCES for a set of data. I don't even have data params in the query. I need any and all first occurances.

That is what seems to be crushing me...

------------------
Curious by Nature,
Linux by Design

 

[lbass]

You might want to try a SQL expression for this, as in:

(select min(AKA.`HEARING_DATE_TIME`) from DISPOSITION AKA where
AKA.`DISPOSITION_CODE` = DISPOSITION.`DISPOSITION_CODE`)

If you have other limiting criteria they might need to built into the expression.

Then go to report->edit selection formula->record and enter:

{%mindate} = {DISPOSITION.HEARING_DATE_TIME}

This should return only the first record per disposition code.

-LB