Hi there,

I have two tables in access, one called "A", which has the following
fields:

VendorID  VendorName  DocumentNumber Amount Period
123     ABC             55545           132             1
456     DEF             55776           145             1
487     ADE             54758           100             1
123     ABC             57457           665             2
487     ADE             66547           547             2
123     ABC             55474           554             3

And so on.. As you can notice, this basically is a table that records
the amount spent by vendor for a certain period. The period goes from 1
to 12.

The other table, "B", has the status for all vendors by period... Every
once in a while, users modify the status of the vendors and through a
query, I build up this table "B".

VendorID        VendorName      Status  Period
123             ABC             A               1
456             DEF             A               1
456             DEF             B               2
487             ADE             C               2
123             ABC             B               3

And so on... In table B, one vendor can have one one record at the
maximum for a specified period... What I would like to be able to do is
to have a query that yields the exact information (with the exact
number of registries) contained in table "A", plus and additional
column called "status". I want the query to select for each registry in
table "A" the corresponding status from table "B" (using VendorID as a
link) such that the period in table "B" is the maximum of all the
periods less or equal to the corresponding period in table "A". In this
way, I'd have the status for a certain vendor for a specific period and
I'd be considering the case that a vendor not necessarily has an status
in table "B" for a specific period. For example, in the case of 123, in
table "A" (the first record), the query should select all the records
in table "B" for that vendor such that the period in table "B" for that
vendor are less or equal than 1. In this case, it'd be:

123     ABC     A       1

and select the maximum period of that selection.

In the case of 123 in table "A" (the last record), the query should
select the maximum period of and:

123     ABC     A       1
123     ABC     B       3

In this case, the corresponding status should be B.

How can I do this?? If anybody can help me out, I'd be very grateful.

Thank you so much,

A.