Designing Queries using SQL

P

PowerPoint Jedi

I designed the following query a couple weeks ago. It takes from an existing
table and creates another to my specified parameters. It works fine with no
problems when running it. Now I want to change it slightly but no matter
what I do when I go to save it it gives me a syntax error in from clause. I
have no idea why, even if i don't change anything it still gives me the error
but if I run it outright it still works. Below is the sql version of the
query and all the names used are correct. After I get the error message the
cursor is located on the fifth line right after the '(' it actually looks
like the parenthesis is highlighted. Anyone have any idea why I am getting
this. Thanks in advance.

SELECT * INTO [Year and monthly failures1]
FROM [SELECT
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=6) AND (([all so raw].MONTH)<=9) AND (([all so
raw].PARTNO)<>"0000-00-0000-00") AND (([all so raw].CHGCDE)="1A" Or ([all so
raw].CHGCDE)="1G")) OR ((([all so raw].YEAR)=5) AND (([all so raw].MONTH)>9)
AND (([all so raw].PARTNO)<>"0000-00-0000-00") AND (([all so
raw].CHGCDE)="1A" Or ([all so raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [year] LEFT JOIN [select
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=5) AND (([all so raw].MONTH)=9) AND (([all so
raw].PARTNO)<>"0000-00-0000-00") AND (([all so raw].CHGCDE)="1A" Or ([all so
raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [month] ON (year.model = month.model) AND (year.PARTNO = month.PARTNO)
ORDER BY year.model, year.PARTNO DESC;
 
R

Rick Brandt

PowerPoint said:
I designed the following query a couple weeks ago. It takes from an
existing table and creates another to my specified parameters. It
works fine with no problems when running it. Now I want to change it
slightly but no matter what I do when I go to save it it gives me a
syntax error in from clause. I have no idea why, even if i don't
change anything it still gives me the error but if I run it outright
it still works. Below is the sql version of the query and all the
names used are correct. After I get the error message the cursor is
located on the fifth line right after the '(' it actually looks like
the parenthesis is highlighted. Anyone have any idea why I am
getting this. Thanks in advance.
Click to expand...

I've never used this syntax, but I'm pretty sure that if you create a
"virtual table" in your SQL statement with the square brackets [ ] then
there can be no other square brackets inside of the ones that define the
virtual table.
SELECT * INTO [Year and monthly failures1]
FROM [SELECT
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=6) AND (([all so raw].MONTH)<=9) AND
(([all so raw].PARTNO)<>"0000-00-0000-00") AND (([all so
raw].CHGCDE)="1A" Or ([all so raw].CHGCDE)="1G")) OR ((([all so
raw].YEAR)=5) AND (([all so raw].MONTH)>9) AND (([all so
raw].PARTNO)<>"0000-00-0000-00") AND (([all so raw].CHGCDE)="1A" Or
([all so raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [year] LEFT JOIN [select
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=5) AND (([all so raw].MONTH)=9) AND
(([all so raw].PARTNO)<>"0000-00-0000-00") AND (([all so
raw].CHGCDE)="1A" Or ([all so raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [month] ON (year.model = month.model) AND (year.PARTNO =
month.PARTNO) ORDER BY year.model, year.PARTNO DESC;
Click to expand...
 
P

PowerPoint Jedi

First thanks for the help.
Second should I replace all the [selects...] with (select...) or just the
first one? I have tried different combinations but have been unsuccessfull.
This is my first time programing with SQL so i realize I am in over my head
but any help is appreciated.

David F Cox said:
(SELECT...) not [SELECT ....]

PowerPoint Jedi said:
I designed the following query a couple weeks ago. It takes from an
existing
table and creates another to my specified parameters. It works fine with
no
problems when running it. Now I want to change it slightly but no matter
what I do when I go to save it it gives me a syntax error in from clause.
I
have no idea why, even if i don't change anything it still gives me the
error
but if I run it outright it still works. Below is the sql version of the
query and all the names used are correct. After I get the error message
the
cursor is located on the fifth line right after the '(' it actually looks
like the parenthesis is highlighted. Anyone have any idea why I am
getting
this. Thanks in advance.

SELECT * INTO [Year and monthly failures1]
FROM [SELECT
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=6) AND (([all so raw].MONTH)<=9) AND (([all so
raw].PARTNO)<>"0000-00-0000-00") AND (([all so raw].CHGCDE)="1A" Or ([all
so
raw].CHGCDE)="1G")) OR ((([all so raw].YEAR)=5) AND (([all so
raw].MONTH)>9)
AND (([all so raw].PARTNO)<>"0000-00-0000-00") AND (([all so
raw].CHGCDE)="1A" Or ([all so raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [year] LEFT JOIN [select
[all so raw].model,
[all so raw].PARTNO,
count( [all so raw].PARTNO) as Failures
FROM [all so raw]
WHERE ((([all so raw].YEAR)=5) AND (([all so raw].MONTH)=9) AND (([all so
raw].PARTNO)<>"0000-00-0000-00") AND (([all so raw].CHGCDE)="1A" Or ([all
so
raw].CHGCDE)="1G"))
group by
[all so raw].model,
[all so raw].PARTNO
order by
[all so raw].PARTNO
]. AS [month] ON (year.model = month.model) AND (year.PARTNO =
month.PARTNO)
ORDER BY year.model, year.PARTNO DESC;
Click to expand...
Click to expand...
 
Top