Creating a Log

  • Thread starter Thread starter Brian Carlson
  • Start date Start date
B

Brian Carlson

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.
 
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/Floating_point#Accuracy_problems
(Set the Format property of the field so it does not display as dollars and
cents.)
 
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
 
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.
 
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.
 
Back
Top