Show last adjustment of a bill...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of bills. Each bill has a bill #. Each new bill is labeled
VER_NUM "001". When a bill is adjusted, the VER_NUM changes to "002". If the
same bill is adjusted a 2nd time, the VER_NUM changes to "003". I want to
create a query that will show only the last VER_NUM for each bill. I have
tried an IIF statement, but I can't get it to work. Please help.
 
Susanne,

You need to use a nested subquery to get what you are looking for. I'm not
sure from the way you wrote your message whether the VER_NUM is a numeric or
text field, so I'll assume that it is text (although I'd advise you to
change it to numeric). The following should give you the entire record
contents for the final adjustment for each Bill.

SELECT * FROM yourTable as T
INNER JOIN
[SELECT [Bill#], MAX([Ver_NUM]) as FinalAdj
FROM yourTable
GROUP ON Bill#] as T1
ON T.Bill# = T1.Bill# and T.Ver_NUM = T1.FinalAdj

I strongly recommend you change your Bill# field if you can. Avoid blanks
in column names, some databases don't even allow them. If you want to
separate words in a field name, us an underscore. Also, I advise against
using special characters (#) in a field name, instead, use NO or NUM.

HTH
Dale
 
Thank you so much for the prompt response. I'm still new to using Access and
have never used SELECT or INNER JOIN before. I'm having problems trying to
get your suggestion to work. Could you show me how to write it exactly? My
table name is MedBillDump. VER_NUM is a text field. I put this in the zoom
box but when I run it, I get an error message. Am I missing parenthesis?

FinajAdj: SELECT MedBillDump.* FROM MedBillDump.* as T
INNER JOIN SELECT [Bill#], MAX[Ver_NUM] AS FinalAdj
FROM MedBillDump.* GROUP ON [Bill#] AS T1
ON T.Bill# = T1.Bill# AND T.Ver_NUM = T1.FinalAdj

Thanks also for the suggestions on the naming rules. I did learn this when I
took an Access class and I know I should apply this to my naming. I will do
this going forward.

Dale Fye said:
Susanne,

You need to use a nested subquery to get what you are looking for. I'm not
sure from the way you wrote your message whether the VER_NUM is a numeric or
text field, so I'll assume that it is text (although I'd advise you to
change it to numeric). The following should give you the entire record
contents for the final adjustment for each Bill.

SELECT * FROM yourTable as T
INNER JOIN
[SELECT [Bill#], MAX([Ver_NUM]) as FinalAdj
FROM yourTable
GROUP ON Bill#] as T1
ON T.Bill# = T1.Bill# and T.Ver_NUM = T1.FinalAdj

I strongly recommend you change your Bill# field if you can. Avoid blanks
in column names, some databases don't even allow them. If you want to
separate words in a field name, us an underscore. Also, I advise against
using special characters (#) in a field name, instead, use NO or NUM.

HTH
Dale


susanne in new jersey said:
I have a table of bills. Each bill has a bill #. Each new bill is labeled
VER_NUM "001". When a bill is adjusted, the VER_NUM changes to "002". If
the
same bill is adjusted a 2nd time, the VER_NUM changes to "003". I want to
create a query that will show only the last VER_NUM for each bill. I have
tried an IIF statement, but I can't get it to work. Please help.
Click to expand...
Click to expand...
 
Back
Top