Track Changes Of Raw Table Data Information?
Dec 19, 2012
Im working in MS Access 2003.Im creating an audit trail for users actions in a form, to monitor and keep track of what fields were updated/entered/removed from the database, these actions are essentially done from command buttons (and the audit trail, is coded on the button click)
However, I have a few search screens, that return a subform listing results. These datasheets returned are effectivly like opening the raw table and hence are editable, (permission to change is granted as they are admin only searches, yes, they can change the raw table)
How can I track the change to a raw table field, ie, if someone updates the field SURNAME from 'Smth' to 'Smith', is there something that triggers this (obviously if it was in an interface textbox, you could audit this) is their a field change. The only way i can suggest it make a recordset of the intial results, then compare that to the results in the table as they leve and compare the two.
View Replies
ADVERTISEMENT
Dec 26, 2007
I am re-designing a database for 2008 and trying to eliminate my Make Table Queries as I have found them to be somewhat consistant over the last year, particularily when the users do not open the database on a given date. It seems there should be a simple way to accomplish what I want but I am struggling and need some assistance.
I have attached a sample of a few tables from my database, Open Cases, Closed Cases, and Date Today. The Open and Closed tables change daily due to a Corporate download and contain several date fields which have different meanings. As new cases are opened, they go on the open table, and as an open case is closed, it moves to the closed table. The tbl_Date Today is pre-populated with dates of working days only. I have a query called "Count Of Shelf Comb" that counts the number of open cases as of today, which in truth is for all activity through the previous business day. What I want is to have a query that will show each date on the tbl_date today as well has what the total count of open cases was for that date......a permanent history of the amounts.
How can I accomplish this without using a "Make Table Query".
View 2 Replies
View Related
Oct 12, 2006
I've read over and over that calculated data is bad, that's fine with me... but here is my dillema...
I have four fields that interact with my calculation in a single form. There is only one table in the database.
AmountF (The stored data in the table) (amount financed)
FC (calculated by =[amountf]*.25) (finance charge)
Amount Paid (used in calculation of total due)
total due (calculated by =[amountf]+[fc]-[amount paid])
The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.
All four of these fields have a text box in the form for themselves.
View 4 Replies
View Related
Mar 11, 2008
I have created a database that I am happy with, and I am about to receive information to populate it with. I know I am about to be asked to about security and logging of changes made to the data, which I know nothing about!!
As a result before I get into this part of my project I would like to know myself what is and what is not possible!!
Currently I have 2 tables and 3 forms, one of which is a sub form........ All changes will be done via the forms, adding new records, editing existing records or even deleting obsolete records.
•Is it possible to record any changes made to the live data?
oFor this to happen I presume that I need to setup users and access rights?
•What type of information can be saved if any?
oWhat form would this be saved in and where (a temp table?)?
While typing this a change of plan – if it is possible I am going to start building it regardless, as it will enhance my database.
Thanks
View 5 Replies
View Related
Apr 24, 2013
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
View 7 Replies
View Related
Aug 16, 2014
I want to keep track of when data in a form was last changed.I only need to track this when the form is closed. not every time a record is changed.
View 5 Replies
View Related
Apr 13, 2014
I am looking for a data model to define and track proficiency/currency.
Example:
You earn a licence to do X. You need to renew your licence every 2 years or so. But also, to maintain currency, you need to do what the licence gives you the right to do often enough, or else you need to do a test.
Specifically, you need to do X a specified number of times N within, say, last 3 months at any one time to maintain currency. If you have lost your currency, you need to do a test/check. So at all times, you need to have N times on record within last 3 months, or a test.
In addition, to do X legally, you also need to document that you have done X at least 10 hours within, say, last 6 months, or that you have another test within those last 6 months. This is independet of the previous requirement (concerning the number of times you did X).
Both requirements must be fulfilled at all times for you to do X legally.
So, any suitable model out there? Essentially, something capable of defining and tracking currency for various ratings and license privileges of pilots would do just fine.
View 2 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
Oct 3, 2014
In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?
Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?
View 9 Replies
View Related
Jan 22, 2015
I'm building a report which should highlight/change text color in field values in a modified table which have changed relative to field values in an original table. I'm using the same conditional formatting on all my fields: e.g. on the field named [Primary Instructor] in the report on the modified table, I have "Value <> Reports![F2015original]![Primary Instructor]", where I reference an identical report on the original table. Both are loaded.
My problem: it's reading all values as changed/different, even when both visual inspection and copy/paste indicate that the values are identical. Thus my report highlights all values for the field, rather than only those which have changed. The reports have identical non-conditional formatting and data type.
View 3 Replies
View Related
Dec 13, 2014
How to generate either a form or report able to show me the history of data changes made by users in the fields of Access Objects. I'm more interested in Forms, since the users will use Forms to change data in the db.
Let me give an example:
My db has, for instance, a form named Frm1 and a form named Frm2. Both were built including several fields such as text boxes, combo boxes, etc.
So, if the user Paul first changes the field Color of the record Id 235 in the Frm1 from Green to Yellow, and then, down the road George changes the field Size of the record Id 14 in the Frm2 from Big to Small, I need to know what changes were made, by who, and when changes happened. Also, it is important to know if a record Id is added or excluded.
My final intent is to have a report/form that shows some sort of user activity log which I think could be like this:
Date--------Time----------User-----Form------RecId------Action------Field-----From-----------To
Dec12,14---15:12:35-----Paul-----Frm1--------235------Changed---Color----Green-----------Yellow
Dec18,14---07:05:19-----George--Frm2---------14------Changed---Size-----Big--------------Small
Dec22,14---09:23:59-----Sean----Frm2--------116------Deleted
Dec23,14---11:07:03-----Paul-----Frm2--------321------Added
etc...
By sorting this output by date, form and field it will easy to check/audit for user processing mistakes.
I know enough to build tables, queries, forms and reports, but don't know how to structure such thing. What kind of form event should I setup?
View 2 Replies
View Related
Aug 22, 2005
i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.
i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.
i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.
i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.
All Help will be appreciated.
I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.
thanks,
sam
View 7 Replies
View Related
Dec 3, 2012
We have a db to register permits that count for one calendar year. This since 2012. For next year, permits must be renewed. How can we keep track of all permits per year per user in the same table ? We would like to produce annual statistics as well ? Easy solution would have been to copy the existing table for the year 2012 and paste as a new table for year 2013 but we don't know yet who will renew his permit ...
View 3 Replies
View Related
Mar 31, 2014
I have two tables (one is a query)
Table 1 (query based)
EMP_ID
Prev_Emp_ID
EMP_ID_DDSK
332-123
1
500
332-133
1
501
332-144
0
332-156
1
502
332-654
1
503
332-456
1
504
332-967
0
Table 2
Res_Numbers
Num_Of_Employees
500
10
505 - after 5 numbers are placed
Will be added for next hire
My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)
Example: If field "Prev_Emp_ID" = false
Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".
Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".
View 4 Replies
View Related
Nov 13, 2007
Hi everyone,
I’m trying to understand building databases correctly; therefore, I am going back over Running Microsoft Access 2000 book.
I’m having some trouble understanding a section in the book on Building a Database, page 86.
“Data or information?
You need to know the difference between data and information before you proceed any further. This bit of knowledge makes it easier to determine what you need to store in your database.
The difference between data and information is that data is the set of static values you store in the tables of the database, while information is data that is retrieved and organized in a way that is meaningful to the person viewing it. You store data and you retrieve information. The distinction is important because of the way that you construct a database application. You first determine the tasks that are necessary (what information you need to be able to retrieve), and then you determine what must be stored in the database to support those tasks (what data you need in order to construct and supply the information).
Whenever you refer to or work with the structure of your database or the items stored in the tables, queries, macros, or code, you’re dealing with data. Likewise, whenever you refer to or work with query records, filters, forms, or reports, you’re dealing with information. The process of designing a database and its application becomes clearer once you understand this distinction....”
To me “data” is synonymous to information, facts, figures, numbers and so on. I guess this is why I am having a hard time understanding the above paragraphs.
Can anyone explain it in a way that a dummy can understand it?
Thanks!
View 14 Replies
View Related
Mar 22, 2013
I have 3 table table; Invoice table, Product table and Sale_product table. Sale product table records all sale from the product table
Invoice table has these fields
ID
TOTAL
CASH_TENDERED
CHANGE
Product table has
ID
CODE
QUANTITY
NAME
PRICE
and Sale_Product table has these
ID
PRODUCT_CODE
QUANTITY
PRODUCT_NAME
PRICE
SUBTOTAL
INVOICE
I did main form from Invoice table and sub form from Sale_product table. I want to use DLOOKUP function to load the name and price, quantity and calculate subtotal automatically from the product table based on the product code entered. i have being trying hard and i keep on getting "Name? error"
View 1 Replies
View Related
Nov 15, 2013
I am trying to get a form to display certain information based on the data in another box. I have to input some exam results and want to assign a level to them. So, if they score < 13, the result would read "Below Entry 3" if between 13 and 21 "Entry 3" etc If this was a spreadsheet I would use an if function, but I am not sure that I can do this here
View 14 Replies
View Related
Apr 15, 2015
I am creating a database to house all the information for a box content label. For any given product it could have multiple items that go on the label.
For example:
My_Part_1
1. Part_1
2. Electrical Assembly
3. Instructions
4. Warning Label
I imagine my database being setup as follows:
ID Model# Contents
1 Part_1 Part _1
2 Part_1 Electrical Assembly
3 Part_1 Instructions
4 Part_1 Warning Label
5 Part_2 Part_2
6 Part_2 Electrical Housing
7 Part_2 Housing instructions
8 Part_3 Part_3
9 Part_3 Instructions
I would like to setup my form so that I could type in the Model # once and then type in each item that would go in the box. I don't mind typing in each item individually and then pressing an "add" button. But I would like it to show everything that is currently on that box content label and be able to delete items out of the label.
Is this possible? What would be the best way to do this?
View 1 Replies
View Related
Apr 14, 2005
i am trying to create a form with a sub-form in it. i have got my client details in it - address, phone, etc then within that table i would like to keep a track of treatments for each time they visit in decending order. can anyone help me out with this? hope someone understands what i mean
View 3 Replies
View Related
Aug 3, 2005
Hey everyone,
Ive got a bit of a problem with my database at the moment. Here are the two tables im specificly having problems with:
Item Stock
Item size
Serial Number
Delivery Date
Date out
Stock level
Item Fitted
Item Size
Date fitted
Reg number
Date Removed
Serial Number
Position
Here I have Item stock as my base table. Item Size and Serial Number info is collected from the base table and put into a combo box.
What im trying to do is when a specific item has been fitted, for access to note the size, serial number and date fitted, and place it into the correct record in the base table. serial number and size to be put in its respective table, and date fitted to be put in the "date out" field.
This is rather annoying i know and a bit difficult to explain, if anyone could help over msn that would be great - david_4321@hotmail.com
Thanks
David
View 1 Replies
View Related
Sep 12, 2005
I have been looking all over for the answer to this and can't find the slightest thing about it.
I need to know how I can query information about what tables are in a MS Access database. Lets say for example I can't access the database with the MS Access application and I want to write a web query in asp to list the tables in the database. For example, I may not know what tables are in there and need to find out.
I know there is a way with mySQL using the "show tables" command but it doen't appear to work with Access
I also came accross some code with OpenSchema(adSchemaTables) that is suppose to do the trick, but it either doesn't work or my shared-hosting provider doen't have it set up to work.
If anyone could PLEASE help me out with the T-SQL query code to get table info I would be greatly thankful!
Thanks in advance,
Dan
dan@innovativead.com
View 1 Replies
View Related
Feb 7, 2007
Hello, I have been looking around trying to gather as much access info as I can. I could really use some help with what seems like it should be pretty basic to me. I am making database for excavation estimation and have a table with my equipment and a field for default costs. I then have a table where I assign costs to a specific job. So I would choose a piece of equipment from the equipment table and then would like the default values entered in from the equipment table into the job table for the default costs. So a default value is put there that can be edited for the specific job. I am guessing I need to enter something into the default data area on either the table or on a field on a form? Can anyone give me the format for putting this in. The primary key in the equipment table (auto-number) is linked to a foreign key in the job table. Thanks in advance.
View 1 Replies
View Related
Feb 14, 2007
Im using Access 2003.
I have a form with a combo box that has a list of all the names that are contained in a table.
I am trying allow the user to select a name but then when the user selects that name it pops up the email in another text box. The name that is being selected is in the same record that the email is in.
whats the easiest way to do this? vba?
View 1 Replies
View Related
Aug 9, 2005
I am not sure if this has been done in a table before. I have seen it in forms but not in tables.
Here is my problem.
I have created a database for a planespotters club. This database has the tables.
ac_logged
ac_maker
ac_model
country
airport
I am trying to display selected information in the ac_logged tbl that is related to other cells. For example: If someone selects boeing in the maker field they will only boeing models in the model field. Also, if someone enters Ireland in the country field only the airports for that country would be listed.
I don’t want to do this in a form but I could settle for a query if I have to.
I was just wondering if this could be done in a table as opposed to any other way.
Hope I have made myself clear on this one. :confused:
View 6 Replies
View Related
Jan 25, 2015
I'm trying to understand how data is passed using foreign keys.
I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....
I've also downloaded his sample DB, RelationBasics, to use as a guide.
Attached is my version of the Student / Sport DB as described on the webpage.
I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.
The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.
I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.
View 2 Replies
View Related
Sep 3, 2014
I'm trying to figure out a way to get my access database to auto-populate data into a field based on the information I type into the first record.
So what I'm looking to do is that when I type a name into the UserName Field it will automatically fill in the rest of the field with the same information.
To clarify I'm looking for a way so that when I type something into one record it will fill the rest of the field in with that same information.
View 8 Replies
View Related