Retrieve the next date for each record 1

[Trogg]

I have a query that involves several tables and fields but the problem at hand involves a date field (TrxSerDate), a type field (TrxType), and a procedure field (TrxProcedure). The date field stores dates for various different Types and procedures. The query currently extracts the records if the TrxType is "R" and the TrxProcedure is "PMT" (short for Payment). This is simple and works fine. Now I need to also be able to extract, in addition to this, the very next date and occurence following a "PMT" that has a TrxType of "S" (short for Statement)for the same account. By aliasing the table(B)and including the date field from the aliased table and specifying a criteria there of >[A.TrxSerDate], I get the desired result but also all the other dates following the TrxSerDate that are type "S". I only want the first one... that is my problem. TOP doesn't work (or I'm not using it right) because I am processing multiple accounts over a specified range of dates and need the information for all accounts in the range. I hope I am making this clear.. thanks for your help in advance!

 

[GingerR]

Make it a totals query and set the GROUP BY to MIN for the date field.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Trogg]

Outstanding!... It was already a totals query, but was set for Group by. I guess I was trying to over think this. I'm still not understanding completely how changing the aggregate to Min eliminates the unwanted data... can you enlighten please? Cheers and thanks!

 

[GingerR]

So with GROUP BY, you get all of the dates because it's GROUPING by them. Look at the list of dates returned with GROUP BY. You only wanted to see the earliest date. If they are in ascending order, you could have picked FIRST as well. MIN and FIRST probably work the same in this case. With MIN, it will only show the minimum one, which in this case also happens to be the first one if they are in chronological order. It's not like I figured this out in one second...I was thinking: Trogg wants to see just the next date, which is the first one, which since it's a DATE could be considered the "minimum" one. Try using other options, like MAX, and see what happens. Ones like SUM, AVG won't really apply. It will probably give you an answer, but it doesn't really make sense since it'll be summing "dates" and it's not something you'll ever use in reality.

Say instead of dates, you have numbers. So like for one PartNumber (or whatever, something that you're GROUPING on in a totals query), there are several numbers returned. If you use GROUP BY for that field, you get:

6
2
4
1
9
7

But changing the option:

FIRST will return 6
MIN will return 1
LAST will return 7
MAX will return 9
AVG, SUM etc will do just that.

Just different ways to do calculations and display the pieces of data you want to see.

Hope this helps.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Trogg]

It's all in the perception.. I don't typically think about dates in terms of minimum or maximum but I can see how the logic works here now. I always have to remind myself to think like the machine and not me! Thanks again for your help!