Picking the latest record...???

[equalizer700]

We have a table that has dates and amounts in it. There are multiple rows for each date. We need to have a way to return the sum of the amounts for the latest date in the table, and only the amount for the latest date. We have a query that sums them, but it returns a row for each date. How do we pick out only the one row with the latest date?

 

[dkinley]

You can try using the Max function for the date field in the Totals row.

-dK

 

[dkinley]

Forgot the First bit ... attaching a picture of the QBE for the setting of the Totals row.

-dK

 

[equalizer700]

I didn't explain it very well...

Here is the query:
querys.GIF

Here is the data that results from the query:
data.GIF



For each budget id we need to pick out the SumofAmount for the latest mod_end_date, either in this query or the following one. I am a noobee at this kind of thing, and have tried more iterations than I can count...but I still get the SumofAmounts for all ending dates for each budget id, instead of just the last one.

Any help at all is greatly appreciated!

John

 

[dkinley]

I am not 100% sure because I have not duplicated your efforts (I am not that hot at working it inside my head) but check the sample I posted on this site to see if some derivation will work for you. It expands to a couple of more fields ...

http://www.access-programmers.co.uk/forums/showthread.php?p=762366#post762366

I am thinking that you might have to do something like this in one query and then use that query in another query in order to extract only 1 record.

If this doesn't help, could you post your db? Change all major identification information or strip out the non-essential tables/data and I will look into it further for you.

-dK

 

[equalizer700]

dK,

Thanks for the help. I have tried iterations of the post you pointed me to, but to no avail. I am way over my head I think

Here is the (scrubbed) needed portions of the db and queries. SampleDB.zip
If you look at query_hippo_current_mod_only you will see it uses _zebra_current_ to find and sum all charges for the current period, by mod and budget_id. What I am trying to do with _hippo_previous_ is to find the total for the last mod for the same budget_id, ie the one that has the oldest ending date of the ones that ended before today. What I have been getting is a row for every previous mod, instead of just the last one.

I'm sure I am making a Doh! mistake, but I have been staring at this for quite a while.

Thanks for taking the time to help.

 

[dkinley]

No problem .....

Okay, I may have gotten it. Look at Q2 for the output. I only sampled a couple for testing - you will need to do more thorough that this works for every case. If this is not it, post back letting me know and I will test further.


-dK

 

[equalizer700]

Thanks for your work!

The query is close but still not exactly right.

A question: When you specify 'First', 'Last', 'Max', etc. in a query, will the result be limited to a single row? I.e. if the rows are different will the query return the first, last, max value no matter which row it is in, or will the values all be picked from a single row? If not, would it return data from the first, last, max rows? In this case we need an entire row.

If you look at the sample queries you worked on, Budget id 4 is correct, but budget id 6 needs to return the data in the single row with mod-id 93 ($1372.54); budget id 8 should return the row with mod id 91 (amount is wrong), etc. What has to happen is the query needs to return the entire row for the last mod-id of each budget id.

I made it work by sortiing Q1 by budget-id/mod-id and selecting Last in Q2 sumofamount. I also sorted Q2 by mod end date, but I don't think it is necessary because it works both ways.

Thanks again for your help...I couldn't have done this without it!

John