compare two tables and make changes

[rjobaan]

I do not know if you can do this with a query or macro. But let me first start to explain what I want to do
how can i do the following:

i have two tables
Table A

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	--
Joe	3-2-2017	200	500	--
Joe	4-4-2017	100	200	--
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	--

Table B

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999

Table A has all event results of Joe
table B has initial only all distance Joe can compete and time 9999
Now i want to do the following
I want to check every row of table A with B and insert new record in table B if new personal best
So first row
Table B should be updated as follows: because his 100 is faster then his current time 9999 a new record should be inserted

Table B

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999
Joe	100	250	P1	1-1-2017

Table A should also be updated, because he beat his personal best, so first event Pr --- change to PR

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	PR
Joe	3-2-2017	200	500	--
Joe	4-4-2017	100	200	--
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	--

Second row
Table B should be updated as follows, because his 200 is faster then his current time 9999 a new record should be inserted

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999
Joe	100	250	P1	1-1-2017
Joe	200	500	P1	3-2-2017

Table A should also be updated, because he beat his personal best, so second event Pr --- change to PR

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	PR
Joe	3-2-2017	200	500	PR
Joe	4-4-2017	100	200	--
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	--

Third row
Table B should be updated as follows because his 100 is faster then his current fastest time 250 a new record should be inserted

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999
Joe	100	250	P1	1-1-2017
Joe	200	500	P1	3-2-2017
Joe	100	200	P2	4-4-2017

Table A should also be updated, because he beat his personal best, so second event Pr --- change to PR

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	PR
Joe	3-2-2017	200	500	PR
Joe	4-4-2017	100	200	PR
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	--

Fourth row
Table B should be not be updated because his 100 is slower then his current fastest time 200

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999
Joe	100	250	P1	1-1-2017
Joe	200	500	P1	3-2-2017
Joe	100	200	P2	4-4-2017

Table A should also not be updated, because no personal record

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	PR
Joe	3-2-2017	200	500	PR
Joe	4-4-2017	100	200	PR
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	--

Fifth row
Table B should be updated as follows, because his 200 is faster then his current time 500 a new record should be inserted

Member	Distance	Time	PR	EvenDate
Joe	100	9999	P0	31-12-9999
Joe	200	9999	P0	31-12-9999
Joe	100	250	P1	1-1-2017
Joe	200	500	P1	3-2-2017
Joe	100	200	P2	4-4-2017
Joe	200	490	P2	12-5-2017

Table A should also be updated, because he beat his personal best, so second event Pr --- change to PR

Member	EventDate	Distance	Time	PR
Joe	1-1-2017	100	250	PR
Joe	3-2-2017	200	500	PR
Joe	4-4-2017	100	200	PR
Joe	6-4-2017	100	210	--
Joe	12-5-2107	200	490	PR

How can i do this?
Or should do it different to make it easier, by creating new tables or something. But i hope this examples shows what i want to accomplish

[ranman256]

ALL records would be added ,then the best PR would be marked.

[rjobaan]

What do You mean? Not all records are adres. And the have all different pr marks because i need to says the history of his pr's