Table set up, query

[Guest]

I am creating a query of current vendors and their status. My boss needs to
see a date when the status changes and be able to query on this. He also
would like to see how often it changes, what the original status was, etc.

The setup for the table I have now are far as fields is as follows:

VendorName, Address, POBox, City, State, Zip, Phone, Fax, Status,
DateChange, Comments

What I am thinking right now is, let's assume VendorA starts out with a
status of 1, then 3 months from now, move to a status of 3. I am thinking I
would have two records for this vendor indicating the status change.
Actually, I could have one table with the address information and another
with just status information and then link the two together. But, my real
question is having separate records each time the status changes the best way
to display this? I can't think I can have fields because I have no way of
knowing the maximum number of possible changes.

My second question is, if I do have separate records, let's assume my boss
wants to run a query for a listing of all vendors, but only wants to see the
latest status for each one. Is there a MaxDate function or something I can
use to pull the latest date change?

Thanks in advance,

 

[Allen Browne]

The related table with a new record each time the status changes is the way
to go.

To get the info about the latest record, see this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The article gives you 4 solutions.