Sub Query help

R

RC

Hi,

I have the following queries, is it possible to place one inside the
other (sub query)

Test1
SELECT TableC.PID_No, Trim([PID_End_Date]) AS End_Date
FROM TableC
GROUP BY TableC.PID_No, Trim([PID_End_Date])
HAVING (((Trim([PID_End_Date])) Is Not Null)) AND (((Trim
([PID_End_Date]))<>""));

Test2
SELECT TableC.PID_No, TableC.PID_End_Date
FROM TableC INNER JOIN test1 ON TableC.PID_No=test1.PID_No
WHERE (((TableC.PID_End_Date) Is Null));

The first query groups by PID No where End date is not null
The second query looks at the first query but brings back end date
that are null

TableC contains multiple PID_No some of which don’t have end date and
they need to , so I need a query that displays 'missing' end dates

Test1
PID_No PID_End_Date
855180-454 20090831
854121-21 20090831
669027-1666 20090831
826760-15 20090831
733195-4 20090831
885674-14 20090831
731699-1317 20090831
727250-326 20090831
675083-5 20090831
896534-1451 20090831
682339-1 20090831
892051-284 20090831


Test2
PID_No PID_End_Date
1899348-2
1729598-12
839213-9
839213-9
260898-4
260898-4
260898-4
260898-4

Any help would be great, thank you

R
 
K

KARL DEWEY

I have the following queries, is it possible to place one inside the other
(sub query)
For what purpose?

If you want to split them but in one output list just do this --
SELECT TableC.PID_No, Trim([PID_End_Date]) AS End_Date
FROM TableC
ORDER BY TableC.PID_No, IIF([PID_End_Date] Is Null OR Trim([PID_End_Date])
="", 0, 1);

Why do you need to trim [PID_End_Date]? Is It that it is not a DateTime field?
 
J

John Spencer

Yes, it is possible. If you Remove all the square brackets in the sub-query.
Also, change the HAVING to a WHERE clause for better performance.

And you really don't need a to GROUP by when you should be able to use
DISTINCT keyword instead.

AND I don't understand why you are using TRIM on PID_END_DATE field unless it
is a text field. Trim will not really change the value if this is a date
field, but it will force the value to a string instead of a date.

SELECT TableC.PID_No
, TableC.PID_End_Date
FROM TableC INNER JOIN
(SELECT TableC.PID_No
, Trim(PID_End_Date) AS End_Date
FROM TableC
WHERE Trim(PID_End_Date) Is Not Null
AND Trim(PID_End_Date)<>""
GROUP BY TableC.PID_No, Trim(PID_End_Date))
as test1
ON TableC.PID_No=test1.PID_No
WHERE TableC.PID_End_Date Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
R

RC

Yes, it is possible.  If you Remove all the square brackets in the sub-query.
  Also, change the HAVING to a WHERE clause for better performance.

And you really don't need a to GROUP by when you should be able to use
DISTINCT keyword instead.

AND I don't understand why you are using TRIM on PID_END_DATE field unless it
is a text field.  Trim will not really change the value if this is a date
field, but it will force the value to a string instead of a date.

SELECT TableC.PID_No
, TableC.PID_End_Date
FROM TableC INNER JOIN
   (SELECT TableC.PID_No
   , Trim(PID_End_Date) AS End_Date
    FROM TableC
    WHERE Trim(PID_End_Date) Is Not Null
    AND  Trim(PID_End_Date)<>""
    GROUP BY TableC.PID_No, Trim(PID_End_Date))
   as test1
ON TableC.PID_No=test1.PID_No
WHERE TableC.PID_End_Date Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


Hi,
Click to expand...
I have the following queries, is it possible to place one inside the
other (sub query)
Click to expand...
Test1
SELECT TableC.PID_No, Trim([PID_End_Date]) AS End_Date
FROM TableC
GROUP BY TableC.PID_No, Trim([PID_End_Date])
HAVING (((Trim([PID_End_Date])) Is Not Null)) AND  (((Trim
([PID_End_Date]))<>""));
Click to expand...
Test2
SELECT TableC.PID_No, TableC.PID_End_Date
FROM TableC INNER JOIN test1 ON TableC.PID_No=test1.PID_No
WHERE (((TableC.PID_End_Date) Is Null));
Click to expand...
The first query groups by PID No where End date is not null
The second query looks at the first query but brings back end date
that are null
Click to expand...
TableC contains multiple PID_No some of which don’t have end date and
they need to , so I need a query that displays 'missing' end dates
Click to expand...
Test1
PID_No     PID_End_Date
855180-454         20090831
854121-21          20090831
669027-1666        20090831
826760-15          20090831
733195-4           20090831
885674-14          20090831
731699-1317        20090831
727250-326         20090831
675083-5           20090831
896534-1451        20090831
682339-1           20090831
892051-284         20090831
Click to expand...
Test2
PID_No     PID_End_Date
1899348-2
1729598-12
839213-9
839213-9
260898-4
260898-4
260898-4
260898-4
Click to expand...
Any help would be great, thank you
Click to expand...
R- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I am using trim because the end date field can contain 1 space, 4
spaces or nulls, as well as actual dates.

In TableC I have PID_No's (PID No is not primary key, each PID_No is
present at least twice in table) with no date, which is fine unless
the same PID_No contains a date on another row - which is what I need
to find out. The first query captures all PID_No's (grouped) and the
second query I want to look at the 1st queries PID_No's and then check
if any of these do not contain a date.

Example.
PID_No End Date
123456 20090812
123456 20090812
123456 20090812
123456
123456 20090812
999999
999999
456799 20090915
456799
456799 20090915

I want to capture the PID No's 123456 and 456799 but not 999999.

Thank you
 
J

John Spencer

Does this give you the desired results?

SELECT TableC.PID_No
, TableC.PID_End_Date
FROM TableC INNER JOIN
(SELECT DISTINCT TableC.PID_No
FROM TableC
WHERE Trim(PID_End_Date & "")<>""
) as test1
ON TableC.PID_No=test1.PID_No
WHERE Trim(TableC.PID_End_Date & "") = ""


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Yes, it is possible. If you Remove all the square brackets in the
sub-query. Also, change the HAVING to a WHERE clause for better
performance.

And you really don't need a to GROUP by when you should be able to use
DISTINCT keyword instead.

AND I don't understand why you are using TRIM on PID_END_DATE field
unless it is a text field. Trim will not really change the value if
this is a date field, but it will force the value to a string instead of
a date.

SELECT TableC.PID_No
, TableC.PID_End_Date
FROM TableC INNER JOIN
(SELECT TableC.PID_No
, Trim(PID_End_Date) AS End_Date
FROM TableC
WHERE Trim(PID_End_Date) Is Not Null
AND Trim(PID_End_Date)<>""
GROUP BY TableC.PID_No, Trim(PID_End_Date))
as test1
ON TableC.PID_No=test1.PID_No
WHERE TableC.PID_End_Date Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have the following queries, is it possible to place one inside the
other (sub query)

Test1
SELECT TableC.PID_No, Trim([PID_End_Date]) AS End_Date
FROM TableC
GROUP BY TableC.PID_No, Trim([PID_End_Date])
HAVING (((Trim([PID_End_Date])) Is Not Null)) AND (((Trim
([PID_End_Date]))<>""));

Test2
SELECT TableC.PID_No, TableC.PID_End_Date
FROM TableC INNER JOIN test1 ON TableC.PID_No=test1.PID_No
WHERE (((TableC.PID_End_Date) Is Null));

The first query groups by PID No where End date is not null
The second query looks at the first query but brings back end date
that are null

TableC contains multiple PID_No some of which don’t have end date and
they need to , so I need a query that displays 'missing' end dates

Test1
PID_No PID_End_Date
855180-454 20090831
854121-21 20090831
669027-1666 20090831
826760-15 20090831
733195-4 20090831
885674-14 20090831
731699-1317 20090831
727250-326 20090831
675083-5 20090831
896534-1451 20090831
682339-1 20090831
892051-284 20090831


Test2
PID_No PID_End_Date
1899348-2
1729598-12
839213-9
839213-9
260898-4
260898-4
260898-4
260898-4

Any help would be great, thank you

R
Click to expand...
Click to expand...
 
Top