Subquery problem

[Les Isaacs]

Hi All

I am fairly new to using subqueries, but have been able to use some recently that have been very useful. I have now hit a problem though which I suspect is down to my inexperience with them

I had a main query with 2 subqueries, and a report based on the main query, and all was well. I then added a 3rd subquery to the main query, and still all was well - the main query ran perfectly (correctly returning the 3rd subquery field), and the report ran perfectly. I then added the 3rd subquery field to the report, and now the report won't open - I get "Multi-level GROUP BY clause is not allowed in a subquery".

What I don't get is that the main query with the new subquery field runs fine - the problem only occurs when I add that field to the report!

Hope someone can enlighten me. The sql of the main query is below, and the 3rd subquery field is 'AW8date'.

Many thanks
Les

The main query:

SELECT [qry x all staff].practice, (SELECT employee_submissions.submission_entered
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP - AW8" & "*"))) AS AW8date, Exists (SELECT employee_submissions.employee, employee_submissions.submission_type
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP - AW8" & "*"))) AS [AW8submitted?], Exists (SELECT employee_submissions.employee, employee_submissions.submission_type
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP SD55(E)" & "*"))) AS [SD55Esubmitted?], [qry x all staff].[ref order], [qry x all staff].nhsp_leaving_date, [qry x all staff].NHSP_updated_to
FROM [qry x all staff]
WHERE ((([qry x all staff].practice)<>"The Colours Practice") AND (((SELECT employee_submissions.submission_entered
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP - AW8" & "*"))))=True) AND ((Exists (SELECT employee_submissions.employee, employee_submissions.submission_type
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP - AW8" & "*"))))=True) AND ((Exists (SELECT employee_submissions.employee, employee_submissions.submission_type
FROM employee_submissions
WHERE (((employee_submissions.employee)=[qry x all staff].staff_name) AND ((employee_submissions.submission_type) like "NHSP SD55(E)" & "*"))))=False) AND (([qry x all staff].[ref order])=3 Or ([qry x all staff].[ref order])=4) AND (([qry x all staff].nhsp_leaving_date)>#3/31/2012#) AND (([qry x all staff].NHSP_updated_to)=CDate("31 March " & Right([forms]![frm x main]![year],4)-1)));

 

[Guus2005]

First create the subquery. Make sure that the ouput given is the output you need.

select table1.id from table1 where Orderstatus='Open'

The main query:

select * from table2 where table2.ID = (select table1.id from table1 where Orderstatus='Open')

if you are not sure how to use it, don't use EXISTS.

When the subquery is used several times you might want to save it (qrySub1) and reference to it like this:

select * from table2 where table2.ID = (select id from qrySub1)

You can connect the subquery with the main query like this:
The main query:

select * from table2 where table2.ID = (select table1.id from table1 where table1.Orderstatus='Open' and table1.UserID = table2.UserID)

Enjoy!

 

[Les Isaacs]

Hi Guus2005

Many thanks for your response. I have tried at length to make my query 'fit' the framework you gave - which I think I can follow, but have now got myself in a right tangle and now just cannot see the way forward.

If you could have a look at the query I've got and let me know how I need to amend it I'd be extremely grateful.

Thanks again
Les

 

[Guus2005]

Can you post a sample database with the tables and queries mentioned?
Empty preferably.