A
Armando
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.
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.