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

pea brain question

P: n/a
MS Access SQL question: Using MS Access, I'm trying to
take data from two different tables and dump it to a new
table. Below are a couple of examples of what I have tried
and was unsuccessful.

Thanks,
pea brain

Option 1: MS Access blows up when I try this

SELECT *
INTO xrmdurtn_auto_new
FROM
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);

Option 2: MS Access complains about the syntax for the
create table but doesn't give me a hint of what is wrong
with it.

CREATE TABLE xrmdurtn_auto_new
(
[RXC CODE] Text,
PXC Text,
DAY Text
);

INSERT INTO xrmdurtn_auto_new
VALUES
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
dh*****@yahoo.com (D. Bemis) wrote in message news:<4a*************************@posting.google.c om>...
MS Access SQL question: Using MS Access, I'm trying to
take data from two different tables and dump it to a new
table. Below are a couple of examples of what I have tried
and was unsuccessful.

Thanks,
pea brain

Option 1: MS Access blows up when I try this

SELECT *
INTO xrmdurtn_auto_new
FROM
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);

Option 2: MS Access complains about the syntax for the
create table but doesn't give me a hint of what is wrong
with it.

CREATE TABLE xrmdurtn_auto_new
(
[RXC CODE] Text,
PXC Text,
DAY Text
);

INSERT INTO xrmdurtn_auto_new
VALUES
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);


You're making this way harder than it should be. Since you want UNION
ALL and not just regular UNION (no duplicates), then just use two
append queries and be done with it.
Nov 13 '05 #2

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
dh*****@yahoo.com (D. Bemis) wrote in message news:<4a*************************@posting.google.c om>...
MS Access SQL question: Using MS Access, I'm trying to
take data from two different tables and dump it to a new
table. Below are a couple of examples of what I have tried
and was unsuccessful.

Thanks,
pea brain

Option 1: MS Access blows up when I try this

SELECT *
INTO xrmdurtn_auto_new
FROM
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);

Option 2: MS Access complains about the syntax for the
create table but doesn't give me a hint of what is wrong
with it.

CREATE TABLE xrmdurtn_auto_new
(
[RXC CODE] Text,
PXC Text,
DAY Text
);

INSERT INTO xrmdurtn_auto_new
VALUES
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);


You're making this way harder than it should be. Since you want UNION
ALL and not just regular UNION (no duplicates), then just use two
append queries and be done with it.

Please bear with me, but I am a rookie with MS Access and don't have
any books to work with. How do you write an append query in SQL
within Access?
Nov 13 '05 #3

P: n/a
Mark Senogles wrote:
Please bear with me, but I am a rookie with MS Access and don't have
any books to work with. How do you write an append query in SQL
within Access?


You need to specify the fields individually AFAIK, e.g.

Insert into table (field1, field2) values (value1, value2)

Insert into table (field1, field2) select field1, field2 from othertable

Or you can use that query grid thingy.

That reminds me of:
Lister: There's got to be a way out. There hasn't been a prison built
that could hold Derek Custer. Why don't we scrape away this mortar here,
slide one of these bricks out, then using a rope weaved from strands of
this hessian, rip up a kind of a pulley system so that when a guard
comes in, using it as a trip wire, gets laid out, and we put Rimmer in
the guard's uniform, he leads us out, we steal some swords, and fight
our way back to the 'bug.
Kryten : Or we could use the teleporter.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #4

P: n/a
ms*******@yahoo.com (Mark Senogles) wrote in message news:<c4**************************@posting.google. com>...
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
dh*****@yahoo.com (D. Bemis) wrote in message news:<4a*************************@posting.google.c om>...
MS Access SQL question: Using MS Access, I'm trying to
take data from two different tables and dump it to a new
table. Below are a couple of examples of what I have tried
and was unsuccessful.

Thanks,
pea brain

Option 1: MS Access blows up when I try this

SELECT *
INTO xrmdurtn_auto_new
FROM
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);

Option 2: MS Access complains about the syntax for the
create table but doesn't give me a hint of what is wrong
with it.

CREATE TABLE xrmdurtn_auto_new
(
[RXC CODE] Text,
PXC Text,
DAY Text
);

INSERT INTO xrmdurtn_auto_new
VALUES
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);


You're making this way harder than it should be. Since you want UNION
ALL and not just regular UNION (no duplicates), then just use two
append queries and be done with it.

Please bear with me, but I am a rookie with MS Access and don't have
any books to work with. How do you write an append query in SQL
within Access?

Umm... how about using the QBE grid? Create a select query, then
turn it into an append query and specify where you want the data to
go. Since you don't have a book, make friends with the help file.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.