Query To Track Revision History
Dec 28, 2007
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
100__________1_________________________30
100__________2_________________________35____100
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
100_____________2_______10_____15_____35_____100__ ___2____20
200_____________0_________________________________ ___1____10
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...
View Replies
ADVERTISEMENT
Sep 8, 2014
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
View 14 Replies
View Related
Apr 11, 2006
Hi Guys,
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.
Luther
View 1 Replies
View Related
Jun 29, 2005
Good afternoon,
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?
Thank you,
Scot
View 8 Replies
View Related
Mar 11, 2014
I am trying to create an individualized revision timetable for my students.
Each student does English(En), maths(Ma) and science(Sc) and four additional subjects (Sub1, SUb2, Sub3 and Sub4).
Each of the subjects has produced 16 revision tasks. (Task1, Task2 ...Task16)
I currently have a number of excel tables:
Student details Table:
Name En Ma Subj1 Subj2 Subj3 Subj4
Revision details Table
Subject Task1 Task2 Task3....Task16
I would like to produce something like this for each student:
In week 1
Mon EnTask1 Subj1Task1 MaTask1
Tue SubjTask1 Subj2Task2 EnTask1 etc
But I am stuck with the query and the reports.
View 5 Replies
View Related
Aug 14, 2006
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.
View 1 Replies
View Related
Sep 27, 2006
Hi folks,
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
Files_id int
Filetransdate date
Filestatus_id int
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.
Regards
Charles
View 4 Replies
View Related
Jul 25, 2007
Hi
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.
TIA
View 3 Replies
View Related
Nov 20, 2012
I have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.
I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.
I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.
View 14 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Nov 1, 2005
Hi,
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
Regards
Chris
View 4 Replies
View Related
Mar 19, 2006
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.
View 1 Replies
View Related
Jul 13, 2006
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.
View 1 Replies
View Related
Apr 27, 2007
Hi,
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.
View 2 Replies
View Related
Apr 27, 2008
Hi
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
01/01/08 300.00
01/02/08 250.00
12/02/08 210.00
Any help would be greatly appreciated
Thanks
Geoff
View 1 Replies
View Related
Jan 9, 2006
Hi,
In MS Access, form
I want make like this:
1st time:
Company Name: Automobile
2nd time:
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
3rd time:
Company Name: Automobile
4th time:
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.
View 1 Replies
View Related
Sep 11, 2005
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.
View 4 Replies
View Related
Mar 24, 2006
Hi Guys
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.
Thanks
Brad
View 1 Replies
View Related
Jul 21, 2006
Is there a way to create or access a history file of all events in a database?
View 1 Replies
View Related
Dec 8, 2007
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.
Melanie
View 7 Replies
View Related
Jan 19, 2008
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
View 1 Replies
View Related
Mar 7, 2005
Hello.
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)
With rs
.AddNew
![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
.Update
End With
Set rs = Nothing
End If
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?
Thanks.
View 5 Replies
View Related
Sep 26, 2006
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.
Thanks.
View 1 Replies
View Related
Feb 19, 2008
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.
View 6 Replies
View Related
Feb 23, 2012
I have a table for my customers with current information.
CustomerID:0001
CustomerName: xxxxx
Address:xxxxxx
Telephone :xxxxxx
etc.....
But some time customers change their information. I want to keep their old information in other table. How it is possible?
View 4 Replies
View Related
Feb 18, 2012
I have a table in my database for my customers. Sometime my customers change their address but I want to keep these changes in other table. How it is possible.
View 1 Replies
View Related