Nested Queries

[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

 

[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?

 

[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>

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!)

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

 

[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

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!)

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

 

[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>

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>

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

 

[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

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>

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>


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

 

[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>

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

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>

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