A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a Log



 
 
Thread Tools Display Modes
  #1  
Old February 5th, 2009, 01:39 AM posted to microsoft.public.access.tablesdbdesign
Brian Carlson
external usenet poster
 
Posts: 49
Default Creating a Log

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.
  #2  
Old February 5th, 2009, 02:11 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Creating a Log

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.)

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


  #3  
Old February 5th, 2009, 04:06 AM posted to microsoft.public.access.tablesdbdesign
Brian Carlson
external usenet poster
 
Posts: 49
Default Creating a Log

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.)

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



  #4  
Old February 5th, 2009, 05:40 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Creating a Log

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.


  #5  
Old February 7th, 2009, 06:24 AM posted to microsoft.public.access.tablesdbdesign
Brian Carlson
external usenet poster
 
Posts: 49
Default Creating a Log

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.



  #6  
Old February 7th, 2009, 09:56 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Creating a Log

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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.