Help requested with a Parameter Query 1

[VickyC]

greetings

This is a simplified example that will help answer a more general problem I'm having.

Consider two tables....

Table: TEST_X_AllValues               Table: TEST_DataSet
          X_All  (pk)                 ID (pk)   X	Y
            3                          1	   5	32
            4                          2	   5	67
            5                          3	   6	74
            6                          4	   5	67
            7                          5	   6	67
            8                          6	   4	67
                                       7	   6	33 
                                       ...
                                       7500...

I want to run the following query...

SELECT TX.X_All AS X_All, Count(D.X) AS X_Count
FROM TEST_X_AllValues AS TX   LEFT  JOIN TEST_DataSet AS D   ON   TX.X_All = D.X
WHERE D.Y = [State a value of Y:]
GROUP BY TX.X_All;

When I input a value of Y as a parameter (say 67), I want my query results to look like the output below at LEFT. Instead, it looks like the output at RIGHT. (The output seems to work if I use an Append Query to put the results in a table, but I just want the Query output to be as shown below at Right.)

Correct                        Incorrect 
X_All   X_Count                X_All    X_Count
 3                              4        1
 4	1                       5        2
 5	2                       6        1
 6      1
 7
 8

Thanks for any help!

 

[PHV]

SELECT TX.X_All AS X_All, Count(D.X) AS X_Count
FROM TEST_X_AllValues AS TX
LEFT JOIN (SELECT X FROM TEST_DataSet WHERE Y = [State a value of Y:]) AS D ON TX.X_All = D.X
GROUP BY TX.X_All

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Perfect! Thanks, PH