By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Help for Xbase to Access -Allen Browne may be

P: n/a
Hi,

I want to update the data in the master table with data from another
table. For example the following tables.

Table1 - Account_No
Name
Last_Transaction_Date

Table2 - Account_No
New_Transaction_Date

For account nos that appear in Table2, I need to replace
Last_Transaction_Date in Table1 with the value of New_Transaction_Date
of Table2

In Xbase I would do (bare code)

select a
use Table1 order accountno
select b
use table2 order accountno

scan
select a
seek b.accountno
replace a.Last_Transaction_Date with b.New_Transaction_Date
select b
endscan

What is the technique in Access?

(I have seen Allen Browne's tips for xbase users. I can see about
looping through one recordset, but not 2)

Sunil Korah
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You don't need any code!

Include both tables in a query and join the tables on Account_No. The query
will only return the records where Account_No is in both tables. Only
include Last_Transaction_Date from Table1 in your query. Change the query to
an Update query. Where it says Update To under Last_Transaction_Date, enter:
Table2.Last_Transaction_Date

Run the query to change Last_Transaction_Date in Table1 to
Last_Transaction_Date in Table2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72*************************@posting.google.co m...
Hi,

I want to update the data in the master table with data from another
table. For example the following tables.

Table1 - Account_No
Name
Last_Transaction_Date

Table2 - Account_No
New_Transaction_Date

For account nos that appear in Table2, I need to replace
Last_Transaction_Date in Table1 with the value of New_Transaction_Date
of Table2

In Xbase I would do (bare code)

select a
use Table1 order accountno
select b
use table2 order accountno

scan
select a
seek b.accountno
replace a.Last_Transaction_Date with b.New_Transaction_Date
select b
endscan

What is the technique in Access?

(I have seen Allen Browne's tips for xbase users. I can see about
looping through one recordset, but not 2)

Sunil Korah

Nov 13 '05 #2

P: n/a
Hi,

Thanks a lot. So simple. Actually I had considered this. But thought
it wont work across 2 table. Serves me right for getting stuck in the
xbase world.

Thanks once again.

Sunil Korah
"PC Datasheet" <no****@nospam.spam> wrote in message news:<RZ****************@newsread3.news.atl.earthl ink.net>...
You don't need any code!

Include both tables in a query and join the tables on Account_No. The query
will only return the records where Account_No is in both tables. Only
include Last_Transaction_Date from Table1 in your query. Change the query to
an Update query. Where it says Update To under Last_Transaction_Date, enter:
Table2.Last_Transaction_Date

Run the query to change Last_Transaction_Date in Table1 to
Last_Transaction_Date in Table2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72*************************@posting.google.co m...
Hi,

I want to update the data in the master table with data from another
table. For example the following tables.

Table1 - Account_No
Name
Last_Transaction_Date

Table2 - Account_No
New_Transaction_Date

For account nos that appear in Table2, I need to replace
Last_Transaction_Date in Table1 with the value of New_Transaction_Date
of Table2

In Xbase I would do (bare code)

select a
use Table1 order accountno
select b
use table2 order accountno

scan
select a
seek b.accountno
replace a.Last_Transaction_Date with b.New_Transaction_Date
select b
endscan

What is the technique in Access?

(I have seen Allen Browne's tips for xbase users. I can see about
looping through one recordset, but not 2)

Sunil Korah

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.