inserting data into table from another table?

G

Geoff Cox

Hello

I am trying to add data from one table to another in the same database
(Access 2003).

The table called main has these field names and values

email_address (e-mail address removed) (e-mail address removed) (e-mail address removed)
group 1 2 4
test1 2 3 4
test2 3 4 2
name
play
how_long_play

the table called main-extra has the data which I wish to move into
main

name (e-mail address removed) (e-mail address removed) (e-mail address removed)
play yes yes no
how_long_play 1-5 6-10

and I want to move the data for (e-mail address removed) in main-extra into the
record for (e-mail address removed) in main etc etc.

I have tried

INSERT INTO main ( email, name, play, how_long_play )
SELECT [main-extra].email, [main-extra].name, [main-extra].play,
[main-extra].how_long_play
FROM [main-extra]
WHERE (((main.email)=[mail-extra].email_address));

but this is wrong.

How do I do this?!

Cheers

Geoff
 
W

Wayne-I-M

UPDATE [main-extra] INNER JOIN main ON [main-extra].name = main.email_address
SET main.how_long_play = [main-extra]!how_long_play, main.play =
[main-extra]!play;

I assume that the e mail address in the same in both tables - if this is so
then link the tables in the query on this.
 
G

Geoff Cox

UPDATE [main-extra] INNER JOIN main ON [main-extra].name = main.email_address
SET main.how_long_play = [main-extra]!how_long_play, main.play =
[main-extra]!play;

I assume that the e mail address in the same in both tables - if this is so
then link the tables in the query on this.
Click to expand...

Wayne,

Thanks for this but when I run the query I get the message "about to
update 0 rows"?

Yes, the email addresses are the same but have different field names.

Remembering that I am wanting to add data to the table called main
from the main-extra table should the line begin

UPDATE [main-extra] ...?

I would have thought I am updating main not main-extra - or have I
just not understood whatis happening?!

Cheers

Geoff

The table called main has these field names and values

email_address (e-mail address removed) (e-mail address removed) (e-mail address removed)
group 1 2 4
test1 2 3 4
test2 3 4 2
name
play
how_long_play

the table called main-extra has the data which I wish to move into
main

name (e-mail address removed) (e-mail address removed) (e-mail address removed)
play yes yes no
how_long_play 1-5 6-10

and I want to move the data for (e-mail address removed) in main-extra into the
record for (e-mail address removed) in main etc etc.
 
G

Geoff Cox

UPDATE [main-extra] INNER JOIN main ON [main-extra].name = main.email_address
SET main.how_long_play = [main-extra]!how_long_play, main.play =
[main-extra]!play;

I assume that the e mail address in the same in both tables - if this is so
then link the tables in the query on this.
Click to expand...

Ah! all is well - i changed the link as you say above and used

UPDATE [main-extra] INNER JOIN main ON
[main-extra].email=main.email_address SET main.how_long_play =
[main-extra]!how_long_play, main.play = [main-extra]!play;

which did the job!

Many thanks,

Geoff
 
W

Wayne-I-M

did you add the link in the query ?


--
Wayne
Manchester, England.



Geoff Cox said:
UPDATE [main-extra] INNER JOIN main ON [main-extra].name = main.email_address
SET main.how_long_play = [main-extra]!how_long_play, main.play =
[main-extra]!play;

I assume that the e mail address in the same in both tables - if this is so
then link the tables in the query on this.
Click to expand...

Wayne,

Thanks for this but when I run the query I get the message "about to
update 0 rows"?

Yes, the email addresses are the same but have different field names.

Remembering that I am wanting to add data to the table called main
from the main-extra table should the line begin

UPDATE [main-extra] ...?

I would have thought I am updating main not main-extra - or have I
just not understood whatis happening?!

Cheers

Geoff

The table called main has these field names and values

email_address (e-mail address removed) (e-mail address removed) (e-mail address removed)
group 1 2 4
test1 2 3 4
test2 3 4 2
name
play
how_long_play

the table called main-extra has the data which I wish to move into
main

name (e-mail address removed) (e-mail address removed) (e-mail address removed)
play yes yes no
how_long_play 1-5 6-10

and I want to move the data for (e-mail address removed) in main-extra into the
record for (e-mail address removed) in main etc etc.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Passing data from a form to another form! 0
Checking if data exists another table and, if yes, extracting a value 0
Checking for data with the same number in another table 5
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Merge data from one table into another table 2
Data from Form is adding into table as 2 rows 3
Copy data from one databse tabel to other database table 1
How do I input data with forms based from one table to another tab 1

Top