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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|