Steps to get the query:

1. Create a new query using your version table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under your ManualID field, accept:
Group By

4. In the Total row under your version field, choose:
Max

That gives you the query as I originally suggested. (If you want to see the
SQL statment, switch the query to SQL View.)

If you need to add the date field as well (so your primary key is ManualID +
VersionNum + VerDate), create another query using the one you just saved as
an input 'table' as well as your original table. In the upper pane of query
design, join them on the ManualID and VersionNum fields. Depress the Total
button in this query also, and choose Max under the VerDate field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian Carlson" wrote in message
...
Allen:
One more question on this item. I do not fully understand the query
design you have given...I am not that fluent with queries. In addition, I
do
not know how to limit the records by the most recent date. Help would be
appreciated.

Brian

"Allen Browne" wrote:

Yes: in that case, you woul use all 3 fields at the primary key (i.e.
ManualID, Version, and Date.)

Or, you could put a business case that adding an addendum should be
recognised as issuing a new version, if you think people would agree to
that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian Carlson" wrote in message
...
Allen:
Thank you. A follow up question: To complicate things there are
multiple types of revisions...one manual could have three revisions and
two
addendums, with the most recent change being the one I would like to
track.
I think that I could add a field where one enters the type of revision,
i.e.
revision or addendum, and then run the query based upon the most recent
versions date...At least I think? Thank you in advance.

Brian

"Allen Browne" wrote:

One option to consider would be keeping all the versions (originals
and
revisions) in the one table.

tblManual table:
ManualID AutoNumber primary key
ManName Text Required, uniquely indexed.

tblVersion table:
ManualID Number foriegn key to tblManual.ManualID
VersionNum Number size Single or Double (or Currency)
VerDate Date/Time when this was version released.
Primary key is the combination of ManualID + VersionNum.

Query to give the current version of your manuals:
SELECT ManualID,
Max(VersionNum) AS CurrentVersion
FROM tblVersion
GROUP BY ManualID
ORDER BY ManualID;

If that sounds okay, I would personally use a Currency field for
VersionNum,
because this may prevent problems if you need to use it as a further
foreign
key to other tables. Currency is a fixed-point data type, so does not
suffer
from floating point matching issues:
http://en.wikipedia.org/wiki/Floatin...uracy_problems
(Set the Format property of the field so it does not display as
dollars
and
cents.)

"Brian Carlson" wrote in
message
...
I am in the process of designing a table and I am not sure what the
best
way
to approach the design is. In summary, I have one table, lets call
it
t_Manuals. This contains the following field ManName, ManDate, and
Man_Revision. This table is full of manuals and for right now they
all
have
the value of 0 in the Man_Revision field, becasue they are the
originals.
However, I want to be able to track changes to these. I was
thinking
that
I would create a table called t_ManRevisions, which would have the
same
fields. Each new record would be a record of a revision. I am not
sure
how
well this will serve my purpose...at the end of the day I would like
to
run a
query that will show all of the Manuals, the most recent revision
date
for
each, and the revision number for each. Does this design seem
solid.
It
seems that I could group the query by ManName and then somehow
filter
to
only
show the most recent? Thank you in advance for any help or tips.