Last record in a month 1

[djieon]

Hi All,

I have a database that is may have 3 or 4 records against one unique ID number in one month

Is there a way that I only show the latest record in the month that I pick?

Ie. If using a parameter field I look at all tests carried out between 01-08-2010 and 31-08-2010 is there a way to show me if it had more than one test in that time period only the latest test result?

Thanks in advance.

David.

 

[CindyK]

Your report is probably grouped by your unique ID number - yes? Add a second grouping by the date, ordered in descending order. Suppress that group header. Then, in the ID header section, enter the data that you want displayed for your "test in the time period". ie - Date, test type.

Since the report is grouped by test date, and since your data is in the ID header, ONLY the most recent test will appear. Your parameter filters them by date - so if there were no records in that date range, nothing would appear in the "Date, test type" section.

You might want to keep the date group FOOTER visible - you could use that to display the actual number of tests that were done in the given date range. (Basically ALL of the data in the date range is being fed into the report - but you're only displaying the most recent one.)

Another tip: if you add a second ID number header section and put your test date fields in that section, you could click the 'suppress if blank' formatting option so that you won't get the blank space if there were no records.

 

[lbass]

Another approach would be to go to report->selection formula->GROUP and enter:

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

Only the most recent record per group would appear in the detail section.

-LB

 

[CindyK]

Good add, LB. An advantage to that solution is less data being loaded into the report; the disadvantage is that you can't report on the total number for the month. Seems like I almost always have to add some sort of running totals or other aggregate in the footer!!

 

[lbass]

Actually, the same data is available in the body of the report, so it doesn't really lessen the load--it's just that only part of the data is being selected for display.

With group selection, if you insert a summary, e.g.,a count, it will still count the non-group selected records--so you could count all records if you wanted to. If you insert a running total instead, it will ONLY count the displayed records.

-LB

 

[CindyK]

Wow - thanks for posting that LB. I just played around with it and of course, you're right. I thought the group selection created a HAVING clause in the SQL - but see that it didn't. Pretty sad that I was convinced I knew what I was talking about. (So, lol, a star for correcting me and I'm pretty sure David ended up with a solution one way or the other!)

 

[djieon]

Hi Both, yes both solutions work so thanks both for doing this for me. If I knew how to give stars then I would!

 

[djieon]

Ah, just come accross another problem, I had a count in the header, and now I have added the formula in the group selection it has not changed and is still the answer it was before. When I put a running total in the header I am getting the answer of 1, I put it in the footer and the answer is what I expect, so my question is, is there any way to get the running total to work in the report header?

Thanks

David.

 

[lbass]

No, running totals won't give you the correct answer in the group header. What is it that you are counting within the group? Are you just testing whether the most recent record contains a certain value? Please identify the field you are grouping on, too.

-LB

 

[djieon]

Hi lbass, I want to count the number of tests between the dates i specify, however if 2 tests are done in the dates specified then I only want to count the most recent (which is the formula you have already given me and I have sorted)
My report is looking at streetlights, and so my report is grouping 1st level on road name, then on the streetlight number and then on the unique 6 digit number of the lighting unit. I have placed the maximum formula in the 3rd level group header (the unique ID)

Does this help?

 

[lbass]

So wouldn't the count always be 0 or 1? Either the maximum falls within the date range or it doesn't?

-LB