Filter data into new field

I would like to print a report that filters data from two
fields of the two linked tables.

Documents table Revision field represents a revision of a
document at the release stage.
Change Orders table Revision field tracks the changes of a
document and shows the latest revision.

How can I generate a report that would have a look up
function to go to Change Orders.Revision first, look for
records and display the latest revision letter in
Current_Rev field (there could be more than one record,
i.e., A,B,C, D), if there are any records in Change Orders
table go to Documents.Revision and take the value from the
Revision field of the Documents table.

This is a report that I generate now

Document_ No Documents.Revision Change
Orders.Revision
6000206 -
A
6000208 -

6003129 -
A
B
C

I would like to generate a report that has an added field
named Current_Rev containing filtered data from both
tables and display only one line for each record.

Document_No Current_Rev
6000206 A

6000208 -

6003129 C

Please help, I'm not very familiar with Visual programming
language, usually use wizards, your guidance is greatly
appreciated.

Jeff,
Thank you very much for your help. I was away on vacation
and didn't have access to the internet.
Yes it is very confusing.
Sounds good but I'm not shure if I understand how to
replace alpha-seq with integer (1,2,3,..). Will the
report show the true document's revision, i.e., -, A, B,
AA, AB, 0100, 0200, etc. (0100, 0200, 0300... are used
for software revision)

Thank you,
Mary

-----Original Message-----
Mary (see below)

Very confusing - I still have think of how to make
Access
understand that if there is a rev AA or AB select 2
characters from right.

You could replace your alpha-sequence with a simple
integer (1, 2, 3, ...)
and not have to evaluate your Rev AB vs Rev BA at all.

Just one person's opinion

Jeff Boyce
Access MVP

.