Nested Queries

J

John Ortt

Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
 
D

Douglas J. Steele

On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean? Did you
get an error? If so, what was the error message? If you didn't get an error,
what did you get?
 
K

Ken Snell \(MVP\)

He may be seeing the inability of Jet/ACCESS to properly parse an SQL
statement in the FROM clause where that statement contains [ ] characters?

--

Ken Snell
<MS ACCESS MVP>


Douglas J. Steele said:
On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean? Did
you get an error? If so, what was the error message? If you didn't get an
error, what did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John Ortt said:
Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
Click to expand...
Click to expand...
 
J

John Ortt

Thanks for replying Guys,

The message I get is "Syntax error in from clause" and the query simply goes
back to the SQL view.

wrt parsing statements with [] characters is there any way round this?

Ideally I wish the original database author had not used spaces in the field
names but I am kind of stuck with what I have.

Thanks again,

John


Ken Snell (MVP) said:
He may be seeing the inability of Jet/ACCESS to properly parse an SQL
statement in the FROM clause where that statement contains [ ] characters?

--

Ken Snell
<MS ACCESS MVP>


Douglas J. Steele said:
On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean? Did
you get an error? If so, what was the error message? If you didn't get an
error, what did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John Ortt said:
Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

You'll need to create and store a separate query that would be what you're
using as the FROM subquery now. Then change your posted query to use that
new query in the FROM clause instead of using the subquery.


New query -- qryU:

SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null);


Modified query that you'd posted:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM qryU as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

--

Ken Snell
<MS ACCESS MVP>




John Ortt said:
Thanks for replying Guys,

The message I get is "Syntax error in from clause" and the query simply
goes back to the SQL view.

wrt parsing statements with [] characters is there any way round this?

Ideally I wish the original database author had not used spaces in the
field names but I am kind of stuck with what I have.

Thanks again,

John


Ken Snell (MVP) said:
He may be seeing the inability of Jet/ACCESS to properly parse an SQL
statement in the FROM clause where that statement contains [ ]
characters?

--

Ken Snell
<MS ACCESS MVP>


Douglas J. Steele said:
On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean? Did
you get an error? If so, what was the error message? If you didn't get
an error, what did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
Click to expand...
Click to expand...
Click to expand...
 
J

John Ortt

Thanks Ken,

I was kinda figuring the same thing....

It works as is but it would be nice to bundle it into the one query to help
keep the windows uncluttered.

Thanks anyway,

John


Ken Snell (MVP) said:
You'll need to create and store a separate query that would be what you're
using as the FROM subquery now. Then change your posted query to use that
new query in the FROM clause instead of using the subquery.


New query -- qryU:

SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null);


Modified query that you'd posted:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM qryU as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

--

Ken Snell
<MS ACCESS MVP>




John Ortt said:
Thanks for replying Guys,

The message I get is "Syntax error in from clause" and the query simply
goes back to the SQL view.

wrt parsing statements with [] characters is there any way round this?

Ideally I wish the original database author had not used spaces in the
field names but I am kind of stuck with what I have.

Thanks again,

John


Ken Snell (MVP) said:
He may be seeing the inability of Jet/ACCESS to properly parse an SQL
statement in the FROM clause where that statement contains [ ]
characters?

--

Ken Snell
<MS ACCESS MVP>


On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean? Did
you get an error? If so, what was the error message? If you didn't get
an error, what did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Yep, sorry, but there is no workaround for having embedded [] characters
inside a FROM clause's SQL statement....

--

Ken Snell
<MS ACCESS MVP>


John Ortt said:
Thanks Ken,

I was kinda figuring the same thing....

It works as is but it would be nice to bundle it into the one query to
help keep the windows uncluttered.

Thanks anyway,

John


Ken Snell (MVP) said:
You'll need to create and store a separate query that would be what
you're using as the FROM subquery now. Then change your posted query to
use that new query in the FROM clause instead of using the subquery.


New query -- qryU:

SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null);


Modified query that you'd posted:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM qryU as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

--

Ken Snell
<MS ACCESS MVP>




John Ortt said:
Thanks for replying Guys,

The message I get is "Syntax error in from clause" and the query simply
goes back to the SQL view.

wrt parsing statements with [] characters is there any way round this?

Ideally I wish the original database author had not used spaces in the
field names but I am kind of stuck with what I have.

Thanks again,

John


He may be seeing the inability of Jet/ACCESS to properly parse an SQL
statement in the FROM clause where that statement contains [ ]
characters?

--

Ken Snell
<MS ACCESS MVP>


message On the surface, that looks okay.

What version of Access are you using? What does "didn't work" mean?
Did you get an error? If so, what was the error message? If you didn't
get an error, what did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Very quickly can someone tell me how to nest a query please?

For example if I have the following two queries can I combine them:

Part1
-------
SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null

Part2
-------
SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

I tried the following but it didn't work:

SELECT top 10 Count(Part1.[Query Number]) AS [CountOfQuery Number],
Part1.[Query Reason 1]
FROM (SELECT [Query Number], [Date Raised], [Query Reason 1]
FROM qryInfoFilters
UNION SELECT [Query Number], [Date Raised], [Query Reason 2]
FROM qryInfoFilters
WHERE [Query Reason 2] Is Not Null) as Part1
GROUP BY Part1.[Query Reason 1]
ORDER BY Count(Part1.[Query Number]) DESC;

Thanks in advance,

John
Click to expand...
Click to expand...
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

Minimum 1
No Duplication or Data Suppression 3
Vlookup by rows and columns 2
Grouping Problem 1
Help with unweildy query 2
Compare 2 fields and then ouput 1 of 2 fields 2
Data Mismatch in Excel 2010 4
Weekday Name 1

Top