Writing a Sub Query help !!

[adwaitjoshi]

I want to write a query which will extract data from two tables. The tables has 5 fields in all and I have to pull out the data in a specific combination of the variables I know that I need to write a sub query I tried writing one by reading some help but it gives errors here is the query can someone help me debug it???

SELECT Demograph.[Participant ID], Demograph.[Name of the Participant], Demograph.Gender, Demograph.Age, Demograph.Handedness, [Actual Data].[Participant ID], [Actual Data].[Iteration ID], [Actual Data].[Measurement ID], [Actual Data].[Value ID], [Actual Data].[Study ID], [Actual Data].[Actual Value]
FROM Demograph INNER JOIN [Actual Data] ON Demograph.[Participant ID]=[Actual Data].[Participant ID]
WHERE [Actual Data].[Value ID]='X' EXISTS IN
(
SELECT [Actual Data].[Iteration ID], [Actual Data].[Measurement ID], [Actual Data].[Value ID], [Actual Data].[Study ID], [Actual Data].[Actual Value]
FROM Demograph INNER JOIN [Actual Data] ON Demograph.[Participant ID]=[Actual Data].[Participant ID]
WHERE [Actual Data].[Measurement ID]='M_Wheelchair_Ref_LeftA' EXISTS IN
(
SELECT [Actual Data].[Iteration ID], [Actual Data].[Measurement ID], [Actual Data].[Value ID], [Actual Data].[Study ID], [Actual Data].[Actual Value]
FROM Demograph INNER JOIN [Actual Data] ON Demograph.[Participant ID]=[Actual Data].[Participant ID]
WHERE [Actual Data].[Study ID] = 'Structural' EXISTS IN
(
SELECT [Actual Data].[Iteration ID], [Actual Data].[Measurement ID], [Actual Data].[Value ID], [Actual Data].[Study ID], [Actual Data].[Actual Value]
FROM Demograph INNER JOIN [Actual Data] ON Demograph.[Participant ID]=[Actual Data].[Participant ID]
WHERE [Actual Data].[Iteration ID] = '1' AND Dempgraph.[Participant ID]=12
)
)
);

 

[The_Doc_Man]

You might well be correct to use the sub-queries but you will confuse yourself terrible if you keep it all as one humongous SQL string.

My advice is to divide and conquer this problem.

Build your subqueries as separate and distinctly named queries.

Then, in your main query, where you have your embedded

IN (SELECT ... from ...) ...

you can make that

IN ( SELECT * FROM stored-query-name) ...

Also, as Pat Hartman so often points out, if you store the subqueries separately, Access pre-evaluates the best way to manage the query. If you toss in the sub-query "out of the blue" embedded in the SQL like that, you take longer to evaluate it.

Also, and this should not be ignored, it makes the length of the master query shorter. And probably easier to read.

 

[Pat Hartman]

I'm having trouble trying to figure out why you are taking this approach. Looks to me like the outer query with a compound where clause would achieve the results that you are after:


SELECT Demograph.[Participant ID], Demograph.[Name of the Participant], Demograph.Gender, Demograph.Age, Demograph.Handedness, [Actual Data].[Participant ID], [Actual Data].[Iteration ID], [Actual Data].[Measurement ID], [Actual Data].[Value ID], [Actual Data].[Study ID], [Actual Data].[Actual Value]
FROM Demograph INNER JOIN [Actual Data] ON Demograph.[Participant ID]=[Actual Data].[Participant ID]
WHERE [Actual Data].[Value ID]='X' AND [Actual Data].[Measurement ID]='M_Wheelchair_Ref_LeftA' AND [Actual Data].[Study ID] = 'Structural' AND [Actual Data].[Iteration ID] = '1' AND Dempgraph.[Participant ID]=12;

I also wonder why you are hard-coding all the data values that you are searching for. A parameter query would be much more useful. If you need help turning this into a parameter query, add that question to this post.

One more thing, It is really poor practice to use embedded spaces and special characters in your column and table names or in the names of ANY user defined objects.

 

[adwaitjoshi]

Thanx a lot This thing really worked and I will try not to use spaces inmy next assignment. As I am new to databasing I did not know this.