Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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
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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Keep Track Of Stock Items By Query
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 Replies !   View Related
Query To Track Files Last Status
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 Replies !   View Related
Track Form/Query/Report Usage
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 Replies !   View Related
Frontend Revision Or Version Checking
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 Replies !   View Related
Setting Database Properties (i.e., Revision Number)
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 Replies !   View Related
Customer History
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 Replies !   View Related
Patient History Db
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 Replies !   View Related
Calling History
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 Replies !   View Related
Help Creating A History...
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 Replies !   View Related
Keep History Of Changes To Fields
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 Replies !   View Related
History Words
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 Replies !   View Related
Adding Price History
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 Replies !   View Related
Help With Data Structure And History Log!
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 Replies !   View Related
Database History File
Is there a way to create or access a history file of all events in a database?

View Replies !   View Related
Maintaining History Of Orders
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 Replies !   View Related
How Do I Keep History Log Of Record Before It Is Overwritten?
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 Replies !   View Related
Building History Table...
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 Replies !   View Related
Creating A History Table
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 Replies !   View Related
Generating A History In Access
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 Replies !   View Related
Track Changes
Hallo Everyone,

I am working with the database that consists of more than 15 tables with more than 50000 records and the problem is that more than 10 people use the database. So in this case I am not sure whether who has the changed the data in it.

So for this reason i need to create a logfile to determine what are all the changes made in the data in the database with the user who uses that.
(I need to create a history and save it.)

is that possible in MS ACCESS? if so can anyone tell me some suggestion


aravind.s

View Replies !   View Related
Am I On The Right Track?
Hi! I am new to Access and am trying to create a database for my customers that tells me what product/service they avail of and the status of their payments.

My tables are

Customer Info
CustomerID
LastName
FirstName
Address
ContactNo

Products (A,B,C) ( A customer can only purchase one product)
CustomerID
ProductID


PaymentSchedule (Annual, Quarterly, Monthly)
CustomerID
PaymentSchedule

Annual Product Fees
Product
Fee

Quarterly Product Fees
Product
TotalFee
QuarterlyFee

Monthly Product Fees
Product
TotalFee
Downpayment
MonthlyFee

Am I doing ok so far? I wasn't sure How to make my tables since the amount payable is dependent on both the product type and the payment schedule. Any suggestions?

View Replies !   View Related
History/Audit Of Logged Users
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

Danson

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved