When Change In Data Add Record in Access

  • Thread starter Thread starter dawn_dudley
  • Start date Start date
D

dawn_dudley

I believe this can be done, but can't figure it out. I have a list of
transactions with a Title as one field and date of transaction in another
field. When this Title changes from Record to Record, I want to add a record
in another table to capture all of the changes with the date of transaction.

I can't seem to overcome the previous record data and matching it to the
current record. Is there a field that is for a previous record?
 
yea it is called the primary key i am taking it you dont have one

you need to add a primary key to the field then you can link the
primary keys together so you can see that record with primary key 3 is
linked to record with primary key 1

table1 (actual data)
primarykey title ...
1 title1 ...
2 title2 ...
3 title31 ...
4 title4 ...
5 title51 ...

table2 (tracking table)
primarykey forigenkey date oldtitle newtitle...
1 3 date1 title3 title31
2 5 date2 title5 title51

so the forigen key is the primary key form table1

so this tells me that the tracked change on date 1 changed record 3 in
table 1 from title3 to title 31
and
on date 2 record 5 was changed from title5 to title51

hope this helps

Regards
Kelvan
 
Hi, thanks, but in table 1, the data I want to see if it changed is the Title
for the same ID like this

ID Title Date
54684 Salesman 6-1-8
88745 Cashier 6-15-8
15974 Driver 4-2-8
65478 Stockperson 9-1-8
75345 Order Taker 8-1-8
88745 Salesman 9-15-8

The table (or report or query) I want to create is

ID Original Title New Title Date
88745 Cashier 6-15-8
88745 Cashier Salesman 9-15-8
54684 Salesman 6-1-8
15974 Driver 4-2-8
65478 Stockperson 9-1-8
75345 Order Taker 8-1-8

So how can I make a primary key in table 1 if there is more than 1 of the
same ID(will not allow duplicates). I am probably just not reading you
correctly. Thanks so much for you help.
 
Add another line to the result with the old and the new Title and the date it
changed.
 
you haev a primary key in table 2 and a forigen key which is the
primary key of table 1

ie

ID table1ID Original Title New Title
Date
1 88745 Cashier
6-15-8
2 88745 Cashier Salesman 9-15-8
3 54684 Salesman 6-1-8
4 15974 Driver
4-2-8
5 65478 Stockperson 9-1-8
6 75345 Order Taker 8-1-8
 
OK, I have my original table with all the transactions in it

ID Title Date
54684 Salesman 6-1-8
88745 Cashier 6-15-8
15974 Driver 4-2-8
65478 Stockperson 9-1-8
75345 Order Taker 8-1-8
88745 Salesman 9-15-8

Now I have a table where I want to put the results with these headings

Primary Key ID (from Table 1) Original Title Current Title Date
Changed

How do I get the changes into table 2, how do I do the compare, like for
88745 from Cashier to Salesman on 09/15/08?
 
I believe this can be done, but can't figure it out.  I have a list of
transactions with a Title as one field and date of transaction in another
field.  When this Title changes from Record to Record, I want to add a record
in another table to capture all of the changes with the date of transaction.

I can't seem to overcome the previous record data and matching it to the
current record.  Is there a field that is for a previous record?
Click to expand...

I might be misunderstanding your question, but are you attempting to
implement an audit trail? If so, try this:
http://www.allenbrowne.com/AppAudit.html
 
to compare you write a query

select * from table1,table2 where table1.id=table2.idfromtable1

hope this helps

Regards
Kelvan
 
Back
Top