only want a maximum id 4

[bobalou]

from a database where the id is the key field. right now i am getting all of the records from the table where the selection criteria is being met (many tables being linked) but i only want one output line where the id is the maximum of all of the records assoicated to the rest of the data. (does that make sense). have tried a summary formula, but is does not show up as an option when i use the select expert. have tried evaluateafter, whileprinting records, etc. but still cannot the the max, i get all of the records where the main data is repeated for each record.

 

[lbass]

You haven't indicated what you mean by "maximum". If you mean the most recent record for each ID, then you could group on {table.ID} and go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.ID})

After using a group select, you need to use running totals for calculations, not simple summaries, since the non-group selected records would still contribute to summaries.

-LB

 

[vidru]

Have you tried using a Group Selection formula (Report>Edit Selection Formula>Group)?

Your formula would look akin to this:

{Table.KeyField} = Maximum({Table.KeyField})


-dave

 

[Ngolem]

your problem is as clear as mud!

So you set up a record selection formula (who knows what it is??) and have this report (God knows how it is structured?) and you want the maximum value of ID (based on an unknown criteria??)

Yep easy to solve this problem!

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bobalou]

i am looking for the last record from the last table in the query. the assumption being it was the last entered. it does have a date and unique system id. in this case there is a "communication" table (like a mail log) with an association table and the third table with info on who and when "tasks" associated with the communication were done. when i group as suggested above i get a repeat for each task rather than the last one. i tried both suggestions and still get all of the records not just the last one.

i used
{TASK.TASK_ID}) = Maximum (TASK.TASK_ID})

and {TASK.TASK_START_DATE} = Maximum ({TASK.TASK_START_DATE},{TASK.TASK_ID})

 

[lbass]

Well, that's not really possible, although you could get multiple records if the same date appears for multiple records.

Please note that you will see all records in the group tree on the left, but in the body of the report, only the group selected records will be displayed.

You should be grouping on ID if you want to see the most recent activity per ID. Also, if you are using a count summary to determine that all records are included, that will remain the same after group select. If you insert a running total, using the ID field, count, evaluate for each record, reset never, you will see a smaller number.

Also please verify that you entered the formulas in the edit selection formula->GROUP area.

If this still doesn't work, then it would be helpful if you described your table links and provided some sample data.

-LB

 

[bobalou]

i tried changing the grouping to
{TASK.TASK_ID}= Maximum ({TASK.TASK_ID},{COMM.COMM_ID})
and it did work! thanks to the first two respondents, i appreciate the help and your ability to grasp what i was looking for. need to do a little qa/qc to verify the output is correct

the inability to attach graphics and other files makes it somewhat difficult to get the question across clearly sometimes but you two managed.


The quality of the answer is based on the knowledge and skills of the responder!

 

[lbass]

Please note that you are now getting the highest value of taskID for each group of CommID. If the values are entered consecutively this could be the most recent value. Otherwise, if you are trying to get the most recent taskID within CommID, try:

{table.date} = maximum({table.date},{comm.commID})

-LB

 

[bobalou]

will keep that in mind, the advantage i have is that the task id is generated by a sequence generator and will be incrementally increased by 1 every time a new task is created, the date is manually entered and the users have been known to put in the wrong date (go figure).

i will keep that in mind though since each record in the db also has a system date attached to it. i will try it both ways and see if there is any difference as the sys date is updated everytime a user touches and saves a record.

thanks again for your help!
mike