When Change In Data Add Record in Access

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

 

[Lord Kelvan]

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

 

[dawn_dudley]

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.

 

[NetworkTrade]

and what happens when 88745 has a third record??

 

[dawn_dudley]

Add another line to the result with the old and the new Title and the date it
changed.

 

[Lord Kelvan]

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

 

[dawn_dudley]

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?

 

[pietlinden]

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?

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

 

[Lord Kelvan]

to compare you write a query

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

hope this helps

Regards
Kelvan

 

[dawn_dudley]

Thanks Kalvan, I was having a hard time following you, but what finally
worked was a Dlookup and I used this reference

http://support.microsoft.com/default.aspx?id=210504