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
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?
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
What is the simplest way to track who is making a change to a record and when they made the change? I've tried searching for help but I'm not quite grasping how to do this. Any ideas?
Hi, I am trying to figure out a way to keep record of store hours, and days open for lots of different restaurants. The only thing I could up with is the following. I'm not sure how to keep track of store hours, or even the best way to make a table for hours. Any help is appreciated, thanks.
Hi. I have a form with many fields. I want to track changes av 1 field to know the field value has been changed or not. I know about onchange event, but this only works when user inserts or changes the value. My form is being updated by macro/visual Basic. Is there any way to fin out if the value of a field has been changed automatically?
Is it possible to identify the user and date of a change made to a record in an Access 2003 Database retroactively the built in securiy features were not turned on?
Im fairly new to MS Access and have recently created a simple database to track the details of people in my department.
The database is on a shared drive and the idea was that when the details of the individual change, they open the database themselves and edit the appropiate field.
However, is it possible to track the changes that people make over a certain period (ie every month).
I'm interested in tracking monthly utility bills....monthly charges, units/volumes, costs per unit and so on, by monthly date. Would I be OK using a single table for my data, since it only involves gas, electric and water each month? How would I chart the data, so I could see trends (i.e., cost per volume, etc.)
Are there any sample projects out there that I might be able to adapt to what I'm interested in?
I'd be most appreciative if anyone has a comment, idea or suggestion. Thanks in advance!!
I have been wondering about how I'm going to keep track of revisions for one of my databases.
Currently, I have the database split into a Back End (BE) and Front End (FE). In the work environment, the BE sits on the server, whilst all other 4 machines get a local copy of the FE. All table data in stored in the BE and linked to the FE.
I have recently got my act together and started documenting all the revisions I make to the BE and FE of the database. To do so, I created two tables with the following fields:
RevID (PK Auto increment), RevDate RevNumber RevisionNotes TimeDateStamp
I called them tblRevisions_FE, tblRevisions_BE. I placed both tables in the BE.
Whenever I make changes to BE / FE, I record it in the appropriate table.
However, because I often work on the FE of the database off site, I don't have access to the BE table (tblRevisions_FE) to record the new changes for the FE.
One alternative I thought of was just recording the revisions in a text document, and asking the client to manually record these in the tblRevisions_FE (via a FE form).
I did have tblRevisions_FE located in the FE, but when each db user got a copy of the FE, there would also be 4 copies of the table floating around.
Does anyone have any suggestions about how to manage documentation of FE revisions in this situation?
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.
Hello people. Iam having a problem and i need some help. iam appending data in one table to data in another another. I have two tables(table1 and table2), with the same fields but the records in one table2 are 5 more than those in table1 which are 3. The 3 records in table1 are also in table2. I would like a query which outputs the other 2 records that are in table2 and not in table1.
I want to be able to track users log-ins on my DB, basically when a user enters their username and password I want the the time they log in and their username recorded in table. I tried doing it from the database found here: [URL] .... but I cant get it to work.
I have a table into which users insert records through a user form, I need to track the updates made in a record in a data set and i have a user log in function, it needs to tell me who updated last and what did they update.
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.
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
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.
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:
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 have been searching and found a few great threads about checking users but the codes there were incomplete. Is there a way to effectively log a user's name & computer name when they open your database and also log when they close/exit your database. Without them noticing but just for your own personal security incase something happens and you would like to know who all was actually working in/on the database at that specific time?
Or just something that will show me who is in the database so I dont' have to keep seeing network333 something has it open.
Thanking you all in advance, and I know I am a P.I.T.A.!
I am writing a database to track our (and others) wine collections. i'm wanting to track what bottle is where. so i can look up what i have, look up where i have put it and away i go to get it, simple right?
just to complicate things i want a system in place. where the end user and use forms to add/remove or modify wine racks.
assuming that all racks are rectangular i would like to be able to add a rack and call it rack 1. rack 1 has 4 shelves all able to hold 5 bottles. the database would then say that bottle X is in rack 1 shelf 1 position 1.
if possible there will aslo be a screen that is auto generated by the database showing a simple grid with grrn and red squares denoting if the position if full or empty. the ability to click on a position to see what is in there or to add a bottle there would be amasing but not fussed if that can't be done.
I want a field in my table (called 'ContactTotal') that tracks the total number of times the date was changed in the field 'LastContacted' - through either table or a form, but it should exclude any changes made on the same day.
I'd also like a second field (called 'Popularity') that does the same thing, but multiplies any changes made in the last 3 months by 3, the last 3-6 months by 2, the last 12 months by 1 and anything before that by 0.
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?
All; using Access 2010. I need to have some kind of way to track changes based on different field name.I have a client table with field data like Fname, Lname, SSN, Address, Zip, Phone, DateOfBirth, Current Rate, TotalRate, Status and Importdte. I have an import into the database every month and I update this table with data that is new based on the SSN with an append query. The import also has the names of those that exist in the client table until their status changes. In addition to appending new data every month; I would like to append any changes the existing client has. For ex; If an existing client has an address change, I want to append this data to the table but keep the existing address and have a Change date equal to the Importdte. In essence; I want to build a kind of history table that when I pull up a client; I can have something like a tab on a form with the changes and when it happened.
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.
Is there a way to track the changes made to a record? I have added a timestamp field to my form that indicates when the record was changed but I would also like to know which of my staff has made the change. I found this on line but I'm having no luck with it working:
CODE: Private Sub Form_BeforeUpdate(Cancel As Integer) ' Log the user details to the table Me!UpdatedBy = CurrentUser() Me!TimeStamp = Now() End Sub
What this gives me in the Updated By control is "Admin" instead of the users name.
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.