Select the last version of a bill line

[Guest]

I have a table that looks like this:

BILL_NUM LINE_NUM LINE_VER PAID
0001234 001 001 $100.00
0001234 001 002 $125.00
0001234 002 001 $100.00

I want my query to show only the last LINE_VER for each BILL_NUM's LINE_NUM.
I don't want to show LINE_VER 001 whenever LINE_VER 002 exist.
I am lost how to go about doing this.

 

[Tom Ellison]

Dear Susanne:

First, we need a definiton of "last". If that means "most recent" and you
do not record the date and time of each row when it is added, then perhaps
it has some other significance. I'm going to guess that the sequential
numbering of LINE_VER defines this.

So, for each BILL_NUM and LINE_NUM, there may be more than one row in which
LINE_NUM varies but is unique. Another guess on my part. (Can you see how
difficult this can be? Not meaning to be too critical here, but such
guesses on my part are not always correct. It is important for me to
communicate my assumptions because the value of what I try to transmit in my
response depends on the validity of my understanding, which is based on such
assumptions.)

I think what you could do is this:

SELECT BILL_NUM, LINE_NUM, LINE_VER, PAID
FROM YourTable T
WHERE LINE_VER =
(SELECT MAX(LINE_VER)
FROM YourTable T1
WHERE T1.BILL_NUM = T.BILL_NUM
AND T1.LINE_NUM = T.LINE_NUM)
ORDER BY BILL_NUM, LINE_NUM

You must substitute the actual name of YourTable. Assuming the column names
are correct, don't make any other changes till you have tried it.

For your reference, this query uses aliases (T and T1) and a correlated
subquery (lines 4-7 if it doesn't wrap on me). You can study these topics
in online help or any good book on the subject of queries.

Tom Ellison

 

[Guest]

Your assumptions were correct and your answer was just what I needed. Thank
you so much. I've been working in Access for about a year now. An excellent
book that has helped me tremendously is Microsoft Office Access 2003 Inside
Out by John Viescas. If I can't find an answer there, the very kind people
here in the discussion group always help me.

 

[Tom Ellison]

Dear Susanne:

I know John. MVPs get together from time to time. You can hardly go wrong
with his book, but I guess it doesn't answer every possible question. (I'm
not saying your answer wasn't in the book!)

Actually, your question was quite "standard". You might want to look on the
CD that comes with InsideOut at the first article on queries.

Tom Ellison