pea brain question

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]
);

re: pea brain question

dhbemis@yahoo.com (D. Bemis) wrote in message news:<4aa53c3.0409171651.3784575b@posting.google.c om>...[color=blue]
> 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]
> );[/color]

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.

re: pea brain question

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0409180453.5ca8c3e6@posting.google. com>...[color=blue]
> dhbemis@yahoo.com (D. Bemis) wrote in message news:<4aa53c3.0409171651.3784575b@posting.google.c om>...[color=green]
> > 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]
> > );[/color]
>
> 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.[/color]


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?

re: pea brain question

Mark Senogles wrote:
[color=blue]
> 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?[/color]

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.
\__\

re: pea brain question

msenogles@yahoo.com (Mark Senogles) wrote in message news:<c469f4a6.0409201330.19243e72@posting.google. com>...[color=blue]
> pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0409180453.5ca8c3e6@posting.google. com>...[color=green]
> > dhbemis@yahoo.com (D. Bemis) wrote in message news:<4aa53c3.0409171651.3784575b@posting.google.c om>...[color=darkred]
> > > 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]
> > > );[/color]
> >
> > 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.[/color]
>
>
> 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?[/color]


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.