Getting Record Status From A History Table

Oct 22, 2007

Dear All,

I have a MAIN table which stores the most recent info of a record with
following details:

Unique_Ref_Num|Status|Dept
1 |6 | 1

and a second table called history which records changes in the main table

HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2 | 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2 | #22/10/2007 16:10#


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)

Any help will be greatly appreciated
--

Many Thanks
Baapi
:confused:

View Replies


ADVERTISEMENT

Modules & VBA :: Copy Record To History Table And Then Delete It From Main Table

Jul 9, 2014

I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.

what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.

I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.

'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next

[Code] ....

View 8 Replies View Related

Update A Record Of One Table And Save The History Of Changes In Another.

Jun 10, 2007

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

View 4 Replies View Related

General :: Creating History Record From Another Table Values?

Jan 20, 2014

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)?

View 9 Replies View Related

How Do I Keep History Log Of Record Before It Is Overwritten?

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

Modules & VBA :: How To Record Username In History Comments

Nov 26, 2013

I have a field that records comments call house comments i wonder is it possible to record which user leaves what comment?

Code:
=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([txtID],0))

View 1 Replies View Related

Progress Status For Record Completetion...

Dec 4, 2006

hi everyone,

just wondering if it was possible to have a box that returns the % completion of the current record depending on how much data/how many of the fields of the record have been filled out.

e.g. fields with containing data / total number of fields * 100

possible or not? if so...how is it done?

thanks, james

View 4 Replies View Related

Queries :: Latest Record If Status Is AA?

Dec 22, 2014

I have a query with a record id, report date and status.

How do I pull the latest record if the status is AA?

For latest record in report date I used Max in Totals. With just this max it is pulling the latest date for each set of records with the same record id.

This is a start now how do i pull the latest record that has a AA status?

View 14 Replies View Related

How To Record Length Of Time With Status Value Of 3

Mar 20, 2013

I have a database which contains a field 'status'. This status takes a numerical value (1, 2, 3, etc...). Everything works great.

What I want is to monitor the length of time a field spends on particular status. Specifically, I want to record the length of time that the record spends with a status value of 3. So if the record has its status changed to 3, the system 'starts the clock' and once the status changes away from 3, the system 'stops the clock'. This must also continue if the status subsequently returns to 3 at a later date (for the same record) and cumulatively produce the total amount of time that the record spent on status 3.

Practical example

March 19th: Record is set to status 3 (for the first time)
March 21st: Record is set to status 1
March 25th: Record is set to status 3 (again)
March 27th: Record is set to status 2

In this scenario, I want visibility that the record has spent four days on status 3 altogether. I don't need exact times (nearest day will do). Ideally the system will only count working days (although this is not a must-have).

View 2 Replies View Related

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

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

Record Lock Status & Exist Query

Feb 12, 2007

Afternoon.

I have had a search through the forum however am unable to find anything...

I have two questions:

a) How do I check whether someone else is currently accessing a record / form?


i.e. Opening the form (SchemeDetails) like this:

DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal

Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".

b) How do I check to see if a table exists?

i.e. If DataTable2.Exists = True then ...
Or something like this?


Hope someone can help me!

View 6 Replies View Related

Active Table And History Table, How To Copy On Deletion?

Feb 14, 2006

My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.

I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.

Any help or examples are very much appreciated.

**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.

Thanks a Bunch!


EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone

Customer History (Table 2):
CustomerID
Name
Phone

That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.

View 14 Replies View Related

Building History Table...

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

Creating A History Table

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

Form Background Color To Signify Record Status

Jul 20, 2006

I have a form that contains a toggle button. When a record is complete and considered closed I click the toggle button. I want to make the form background turn red if I enter into a record that has been closed. :confused:

If this is not possible, is there something similiar that would stand out:rolleyes:

Can someone tell me how to do this? Thanks!

View 8 Replies View Related

Access Table Design Help - Franchise History

Oct 27, 2006

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

TeamID = 2
TeamName = LA
Historical Team ID = 1

etc.

I hope this makes sense. Any suggestions?

Thanks
Rob

View 4 Replies View Related

Append Value To History Table And Update Current Value

May 18, 2015

I have form1 that has the current commodity When i need to change the commodity I want the following to happen when they click a button: open the commodity change formthat form has a drop down to select a new commodity and a date of commodity change date fieldWhen the user submits it will move existing value to history along with the Date of change (separate table)Next it will take the value in the new commodity box and make it the current commodity

My thinking is that when i click the button to open the change form I can save the commodity at that time but I wont have the date yet. Just not sure of they best way to go about this.

View 6 Replies View Related

Modules & VBA :: Show Lock Status Of A Record When Other User Is Editing Data?

Mar 27, 2015

I have a form and I want show a message on the form when the it is locked as another user is editing the data in a particular record.

I know the record selectors show the records lock status but it a very tiny symbol which will mean nothing to the users of the database and anyway I don't want record selector bar on the form. How I would do this???

View 2 Replies View Related

Tables :: Setting Up History Table For Contacts And Companies

May 15, 2013

I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.

Here is what I need to do:

When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.

So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.

I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.

Maybe I don't need a history table but something else?

I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.

View 5 Replies View Related

Tables :: Hide Inactive Status From Dropdown Box In Other Table

Nov 20, 2014

I have some tables with data where in one column the user will choose a status between 30-40 different options. They are choosing this option to show a reason why a trade was pended and not approved immediately. These reasons seem to change frequently and sometimes we no longer want to use an old reason.

I do reporting back several years, so I cannot just 'remove' a reason or it will be removed from the table and I will have blank reasons. When selecting the specific reason, the table takes the dropdown from another table that simply lists every possible reason. I want to know how I can make it so that the reason that is no longer in use remains on that connected table, but when the person entering data clicks the dropdown button, the removed or inactive reason no longer shows up as an option.

Previously I had seen people add a column to the connected table of reasons and use a "yes/no" check box to show if the reason is now Inactive. When you checked YES on inactive, the reason would disappear from the dropdown list. My issue is that I do not know what language or formula to use (or where to use it) so that when I click Inactive = Yes, the reason disappears from the dropdown menu.

View 4 Replies View Related

Forms :: Add A Command That Changes Contact Status In Archived Field Of Table

Jun 18, 2013

I have a query that gets it's data from tblContacts. In this table is a yes/no field for archiving and the query gets only those records with a No in the field. The default is no. Next I have a form based on this query, which I am using to hold a set of command buttons which act on a contact chosen from an unbound list. So far so good but now I would like to add a command that changes the contact's status in the archived field of the table to Yes, so that I can use the form to view/edit, or email, or archive.

View 1 Replies View Related

Okay To Flag Status Of One Table In Another Table?

Jul 8, 2012

If you've got a library database with three tables: books, clients, borrowings, would it be okay to have a field in 'books' that flags whether or not the book is currently on loan - or should that fact always be elicited from a search of the 'borrowings' table?

View 2 Replies View Related

Tables :: Update A Field In One Table Based On Status Of A Field In Another Table

Aug 2, 2013

I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.

I want to prepare a report from the projects table that lists projects IF they have any open tasks.

In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'

View 7 Replies View Related

Customer History

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

Patient History Db

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

Calling History

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







Copyrights 2005-15 www.BigResource.com, All rights reserved