Query To Track Revision History
I would like to track the revision history of table records. To do so, I have created tables with two-key fields. A typical table is tblPO_Data. The first key field is PO_Number and the second key field is PO_Revision. Data fields follow.
A key element of this table is that each record may have any number of data fields left blank AND later revisions may only show the "changed" field values.
To Illustrate tblPO_Data:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
100 _________0___________10_____15___________________2 ____20
200 _________0_______________________________________1 ____10
NOW I would like to create a query that lists one record for each PO number, showing the most recently revised data. The desired result would look like:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
Please realize that this is an example. There would be numerous one to one tables for each PO number to reduce the number of blank fields (ie. only tables with changes would be get revised records).
Can someone suggest a way to create a query to accomplish the above requirements, thanks in advance, Jeff...
I am looking for a sample database or application that allows for tracking of pricing for stuff (products) over time. It would be similar to tracking the value of one's inventory as purchases and sales are made. The user could enter a "price point" on a given date or absent such point, the value of the product would be based on the last purchase or sales price.
Perhaps a simple concept would be to track the value of a portfolio of stock shares (microsoft for example). As one buys MSFT shares, the value of all the shares in the portfolio would reflect the most recent price at which the shares were either purchased or sold. In addition, one could enter a stock price on a specific date which would be used to reflect the value as of that date forward until a different price was entered or another purchase or sale transaction was made.
Finally, it would nice to be able to run a report that shows how the value of the portfolio changed during a specific period of time (monthly or over a specified start and end date).
Any thoughts or perhaps know of where something similar might have been created that would shed some light.
Hy, I have this db where a user can purchase and item and total stock number needs to be updated.
I can calculate how many items are left but how to update the warehause table?
I can live without table updating, but if the same item is purchased multiple times my query return incorrect values as the number from which the formula subtracts is never changed.
See attached database.
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.
However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.
I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?
I have asked the users and (of course) they don't know.
Does anyone have a code to check if the frontend version being used is the latest? I have a frontend in the server linked to a backend which I distribute to users via a batch file. The batch file simply copies the frontend unto the users desktop. When I do an update, I simply overwrite the old one with the updated frontend and tell the users to run the batch file again. Any ideas is highly appreciated. Thanks.
Using VBA when the project starts, I need to check what the revision Number of the Adp file is. Simple as that really. Tried FileSystemObject, but it only gives me Dates (creation,modification) etc. Any Ideas
I'm curious how others handle revision tracking in their databases. Do you maintain a separate Word doc or something similar to list revisions, or do you somehow maintain them within the database itself?
I'm thinking of adding this to my latest project, and I'm unsure how to proceed. I just want to have some sort of history showing what updates were done, and when, to assist anyone who may have to maintain it in the future.
I have finished completing the initial version of a database and am now ready to begin the testing phase. When looking at the database properties (Specifically - "Statistics") there is a "Revision Number", which is always blank. I expect some changes and would like to set the Version Number each time I make a revision. How do I set the "Revision Number" found under the Statistics tab from Database Properties which was pulled from the "File" button on the toolbar?
Is it possible to generate a database revision number in code. If not, is it possible to have a revision number generated when an update or fix is done.
What is the best way to make a revision control system?
I have these tables
RevID PK Autonumber
RevNo Number (I want to increment this using Nz(Dmax)
RevIssueDate/Time (Date when the revision was officially issued using Now() and no changes can be made to "RevText" or "PunchItem" in tblPunchList (only new comments can be added to tblPunchList))
RevTextMemo (Description for revision)
FKRevIDNumber FK ID to Revision number in tblRevControl when Punch List item was created
Here are my expectations:
Revision number is numerical, e.g. 1, 2, 3… (using Nz(Dmax) function)
For each revision there will be a date recorded for when the revision was released and locked for changes
I want to be able to track under which revision new records in other tables are entered. An example might be best to explain my thinking. E.g. revision 15 was released 10 July, 2006. Between July 10 and July 12, 2006 five records where added to a separate table tblPunchList. These five records shall show that they were created as part of revision 16, when the PunchList is released July 12 as revision 16.
I like to have a command button, which is used to control when a revision is released and locked for changes. I.e. when user selects the button, the highest revision number should be locked, issue date assigned and a new revision number should be created such that new records in table "tblPunchList" can be linked with this new revision number (in above example revision 17 after July 12)
I intend to have the lock function based on whether RevIssue is blank or not. If blank then last un-issued revision, if date entered all records in tblPunchList with same revision number should be locked for changes.
I have a hard time figuring out how to record date "Now()" in tblRevControl for the last record and at the same time create a new record in the same table. Or is it better to add a yes/no field in tblRevControl to control locking of records?
All advice and help is much appreciated.
I have two tables. Drawings and Revisions. Here is the structure (some of it)
Isos_ID (Primary Key)
Drawing (Text - name of the drawing)
Sheet (Integer - sheet number of drawing)
Rev_ID (Primary Key)
Isos_ID (Foreign Key)
Rev (Integer - revision number for the drawing)
Dates (various date fields for when each drawing passed a test)
What I need is a query that will show me the latest revision and that will be updatable. Since a relationship exists between the two tables it is easy for me to get the latest Rev number but everytime I try to get an updatable query, it does not work.
I recently normalized my tables (before all this data was in one table and I used a subquery to get make the query updatable) and I am going through and making changes to the database to reflect the new structure and one of my forms will not work because it needs to be updatable (we need to be able to change information, but only for the latest revision).
Does anybody have any suggestions?
PS - If I do not answer quickly, it is because I may not be at work over the weekend. But I will read and respond to all answers sometime Monday
Hi! I am not sure exactly where to post this question.If this is the wrong forum, please direct me to the proper forum.
I have recently revised a previous ACCESS db and would like to notate that in the Database properties as Revision 2. How can I set a value there? This is found in the Database Properties under the Statistics Tab.
I am trying to capture the 'revision number' data from the Microsoft Access database Property under the Summary tab. I want to store the information as a variable and use it in a condition statement in a module. Any ideas on where this is stored and how to call it???
I needs some advice on how to make a working revision block in a reports title block(i.e. Report Footer). One problem I have is that I am a little baffled about how to render revision records in a report's footer. Perhaps this is possible with some kind of sub-report?
Also as I'll explain below, the data in revision blocks behaves differently than it ideally should.
I've created some really nice reports that can actually pass for my client's Autocad generated drawings. That problem that I am struggling with handling data for the reports revision block. ( There can also be an Engineering Group's QA/QC block. But this is a problem for a different day). Currently, I am hand coding the revision blocks. Yuck!....
It is an easy matter to generate report content like Bills of materials, Construction Notes, Schedules, and other miscellaneous tables. It is also easy to link fields in the title block ( title1, title2, titl3, dwg.No, ...) to a db table. But the problem is the revision block.
A revision block behaves differently than the rest of the title blocks data. It generally has 7 columns( rev_nbr, date, project_nbr, description, originator, checker, approver) and 8 rows.
According to the clients engineering standards, revision blocks are required to filled from the bottom to the top. And once the revision records have completely filled the revision block to the top, the next newly add revision record overwrites/replaces the records in the initial starting position. And so the cycle continues.
Aside from hand coding the revision block, the only other option (I know of) that I don't want to try it to use a de-normalized revision block table (1 record contains all eight rev_block records). Yuck!...
I would much rather simply grab that last eight rows from a drawing's revision table and somehow display them in the correct order.
Is there a better way to dynamically fill in this data in the reports footer?
I currently have a "customer" form (which displays client details from a table - name, address etc) I would like to add history comments for each customer.
Does anyone know how i would go about doing this.
I image i could press a button on the current "customer" form that will open a history form which after typing a comment would be displayed on the main "customer" form, with date, time and comment.
I would obviously be able to add lots of comments to each record.
i would be greatful for any kind of help
I need to write a db for my boss where he can access his patient treatment information. How can I make the db (Access '03) to be able to add new treatment to the same patient and to display all treatments seperately on a form? There are about 500 patients. Appreciate any assistance.
Hi. I am creating a contact management database. Also I am new to Microsoft Access. How would I create a calling history record for each of the clients in the contact database? What would be the best way and how would I go about it? The calling history ideally would include the call recipient, time, date, subject and notes. Thanks. Take care.
I am trying to create a historical record of Employees at our business. What I would like to do is have a query that will create a begin date and create that date for each day starting w/ like 1/1/06 and continue thru Date()-1. For example:
Jane Doe and John Doe started employment on 1/1/06. Jane Doe was employed until 1/5/06. So using their "Hire Date" I would like the query to do this..
Created Name Hire Date Termination
1/1/06 Jane Doe 1/1/06 1/5/06
1/1/06 John Doe 1/1/06
1/2/06 Jane Doe 1/1/06 1/5/06
1/2/06 John Doe 1/1/06
1/3/06 Jane Doe 1/1/06 1/5/06
1/3/06 John Doe 1/1/06
1/4/06 Jane Doe 1/1/06 1/5/06
1/4/06 John Doe 1/1/06
1/5/06 Jane Doe 1/1/06 1/5/06
1/5/06 John Doe 1/1/06
1/6/06 John Doe 1/1/06
1/7/06 John Doe 1/1/06
So in this case Jane Doe will show up every day in the "Employee List" until she was terminated. Is it possible to loop a date like this in a query or will I have to make an estranged table with a list of dates? TIA for any help.
I have 2 fields - "Balance" and "Date" - each time data in the "balance" field changes, the "Date" field updates to todays date (thanks to help from Kiwiman in the forum earlier).
I now need to keep a history/log of all data input into balances and the dates.
The new field to store this information is called "Balance History". so an example of what i want to end up with would be
Any help would be greatly appreciated
In MS Access, form
I want make like this:
Company Name: Automobile
Company Name: AuID
If I type A, then it will show out the word, but the cursor is till After A, because if we type another character which will change another word
Company Name: Automobile
Company Name: Au[I]ID[I]
What is the keyword to search about it in the forum?
Anyway, does anybody know how to solve it?
Please let me know, thanks.
ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.
any help provided is muchly appreciated thanks.
I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.
I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.
Any help would be much appreciated.
Is there a way to create or access a history file of all events in a database?
I am creating a database for a company that sells a product with a variety of options.
They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.
The company wants all those 1.5 million records accessible in their access database.
I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.
My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)
The history table then will take the new orders each year and add to that table.
(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)
Thanks for any advice.
Hi all, I need some help figuring this problem out. I’ll keep it simple. I have a data entry form with 3 text boxes where you can enter product name, serial number and comments. A save button writes the entry to a table called “Products”. That’s all fine.
I also have a data edit form and this is what I need some help with. The product name will always remain the same but the serial number and comments may change. Should a change be made, I want the existing record being modified to go into a table called History before the Products table is updated with the new serial number and comments. The idea is to keep a full traceability of all the changes made when a report is printed off. What’s the easiest way to this please? thanks
I'm building a history table to keep track of some changes that occur on one of my forms.
Here is what I'm using to build my history table:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDateCycleTestingChanges", dbOpenDynaset)
![CycleTestingDatesID] = Me.CycleTestingDatesID.Value
![Changed] = Now()
![ClientID] = Me.ClientID.Value
![ProjectID] = Me.ProjectID.Value
![TaskID] = Me.TaskID.Value
![TaskStartDt] = Me.TaskStartDt.Value
![TaskEndDt] = Me.TaskEndDt.Value
Set rs = Nothing
I have 2 questions:
1. I'm not sure where to insert this event. I'm thinking on Before_Update on the updated field? (I don't want to put it into Before_Update for a form event as I have other things that are being filled out/changed and I only want to keep track if certain fields on the forms are updated/changed.
2. I would also like to keep track of Old and New values for those specific fields. Is that at all possible?
Hi, I'm new to the forum.
I would like to know how to create a history table that will copy information from a field called "Status" on my form just in case I inadverently erase old information from that field without me being aware of it with my keyboard keys etc. In others words when I'm am interrupted by someone and I didn't notice I had erase the information by mistake and closed out the form and realized when I go back into it, my old information have been erased and I don't have that information documented anywhere else to re-enter that old information into the Status section on my form.
Is there any way to leave a trail or history everytime a record is changed? We have kids in our database that sometimes go to follow-along when our service will not work for them at the present time. These kids go in and out sometimes several times. Since there is no absolute way of knowing, we have to create a new record everytime they come back to our service. I would like to be able to see a history of status changes along with some dates. Is there anyway to do this? If anyone knows, let me know. If not, oh well. Thanks guys.
I'm not sure if I'm posting this to the correct forum but here goes...
I have a database that tracks user security to various programs on our network. The supervisor goes into the database pulls up the user and populates the form with the various security requests. After the form is populated, an e-mail is sent to the next VIP up the list until it reaches the IS department, where the security is actually granted. Once all the security is granted, all the tables with the security settings are cloned with a version number added. The user is then taken out of the not submitted so it doesn't appear in the add list.
I'm now working on if user needs a change in their security. I need to pull up what the user already has access to from the cloned or history table (the latest version). My problem is how do I just the changes to go through the routing? The stickler is that the auditors can pick a date and they would need to know who had access to a particular program at that time.
I don't expect anyone to write this for me, but would appreciate any ideas.
How do I keep a history of who has been given a piece of gear.
I have a table with names and a relationship to a table with all of the gear. I just need a running track for each piece of gear, who has had it in the past
Table Name Table Gear
Name One - Many Item
Last 4-----------------------------------Last 4 of who it's checked out too
After revising a spelling error in table design view, how do I ensure the revision is visible in my forms ? The field list within the Form Design view does not show the revision. In addition , when I click the desired form , an "Enter Parameter Value" Box shows. Can anyone help me out with this ?
Thanks for your time.
Recording to a table Who is logged on to Database, When they logged on and when they logged off.
Gets tricky as I really would like the info to come from the mdw file(all users go through this for access)
If someone can help would be great. Need my hand held for this as I have been attempting to do this for over a month with no real success. I have so many samples that don’t quite make it happen I am now totally confused.
Forever grateful - every crumb helps