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 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.
I would like all the changes made to fields on my form to be automatically entered into a history list on the form. I can't seem to get the right code for this. I would like it to have the date and time the change was made also. Can someone help me. I have Access 2003 and Win XP. My form is unbounded. Thanks, Lisa
I have a table that deals with current data (as in member rentals of items). The normalized tables that we have been given include a rental history table. The idea is that, when the member has returned the product a history entry is made in the completed rental table.
I wish to have a button which is clicked to triggers this event. Would I program a macro to do this? In other words, would I create an event that passes those values to the relevant fields in the other table (Name, date issued, return date etc)?
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 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 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
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.
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.
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.
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.)
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
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.
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 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.
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.
I have created a table for customer bills, and a table for customer profiles which contains rates associated with them specifically. The problem is that after a new rate change has been imposed I want to be able to look at an old bill, via a report, and have it reflect the "old" rate within proper historical context. I'm looking for suggestions on how to best manage ongoing rate changes while maintaining a billing rate history in my reports?
Where Status 1 = Open, 2 = Allocated and 3 = Closed. ---------------------------------------------------------------------------------------------- I want to get the count of number of queries which are not closed (outstanding) at any point in time.
Example: (with a time parameter) Input | Result 22/10/2007 17:00 | 1 22/10/2007 16:05 | 0 22/10/2007 14:00 | 2
I want to achieve this with just 1 query (not by using one query within the other) b,coz I want to further use this query from Excel VBA (write through Excel VBA and not store the query within Access)
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 am developing a new tool which needs to show the history of updates to the users, i.e.:
An observation is entered into a field on the MAIN FORM by user 1.
User 2 reviews the observation on the MAIN FORM and adds a separate comment in another field on the same form.
When the record is saved, the updates are NOT written to the Main Form Table but to the UPDATES TABLE for audit purposes.
I am also using current date/ time as a timestamp, which is protected and needs to be carried over to the UPDATES TABLE Every time a new comment is added in the MAIN FORM, I need it to be written to the UPDATES TABLE as a new record.
What I need to be able to show is:
Each comment added, as a list of readable records, in a table within the main form AND Each time the main form is refreshed/ opened the separate comment field is blank to enable each new update to be applied.
Hello! I am trying to update the current status of an asset, when it was returned or checked out. At the same time, save the changes in a history table to record all the changes in past. I can do individually from different tables and different forms, but I would like to do from one form and one record entry. Is it possible? If so can anybody help? Thanks JVirk
I'm struggling with a table design. My problem is how do I keep the history for one sports franchise no matter how many times they move or change their name? I want individual team info as well as franchise team info.
i.e.
Team #1 Brooklyn Dodgers has many transactions
Brooklyn moves to LA
Team #2 is Los Angeles and they have many transactions
etc, etc.
How would I build my tables if I wanted to write SQL statements to give me all transacation from the entire franchise? I only need help with the teams. I already have a junction for the transactions.
ie All Transactions for all teams who started with Brooklyn. This will grow to 5-6 teams eventually.
My initial idea was a self join in the teams table
Code:tblTeamsTeamID PKTeamNameHistoricalTeamID
TeamID = 1 TeamName = Brooklyn HistoricalTeam ID = 1
