nested query

[geebee]

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all those
accounts that exist in tbl_masterpop with a [popenterdt] of #5/10/2006# that
had a record in the child tbl_archive, but not for #5/10/2006#, but for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[kasab]

Try concatenating the 2 fields in the subquery for both the not in condition
and the subquery itself, ie something like

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
(tbl_masterpop.[loan acct #] & " " & Tbl_MasterPop.PopEnterDt) not in
(SELECT (tbl_masterpop.[loan acct #] & " " & Tbl_MasterPop.PopEnterDt)
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

HTH
kasab

 

[Marshall Barton]

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all those
accounts that exist in tbl_masterpop with a [popenterdt] of #5/10/2006# that
had a record in the child tbl_archive, but not for #5/10/2006#, but for
#5/9/2006# instead.

When used in an IN expression, the subquery can only return
one field.

(SELECT Tbl_MasterPop.[Loan Acct #]
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/9/2006#)

 

[John Spencer]

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)

 

[geebee]

Thanks John. Now I am thinking more of using this same functionality, but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all those
accounts that exist in tbl_masterpop with a [popenterdt] of #5/10/2006# that
had a record in the child tbl_archive, but not for #5/10/2006#, but for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[John Spencer]

I have no idea if it can be done, since I don't understand your question.

Thanks John. Now I am thinking more of using this same functionality, but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all those
accounts that exist in tbl_masterpop with a [popenterdt] of #5/10/2006#
that
had a record in the child tbl_archive, but not for #5/10/2006#, but for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[geebee]

I am thinking of something like this:

dcount("tbl_MasterPop.[Loan Acct #]",
"Tbl_MasterPop",
"Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

but the syntax is not quite right I suppose, if this can even be done.

Thanks in advance,
geebee

I have no idea if it can be done, since I don't understand your question.

Thanks John. Now I am thinking more of using this same functionality, but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)



geebee wrote:

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all those
accounts that exist in tbl_masterpop with a [popenterdt] of #5/10/2006#
that
had a record in the child tbl_archive, but not for #5/10/2006#, but for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[John Spencer]

Well, I don't have any idea if that would work or not.

You could try this variation
DCount ("[Loan Acct #]",
"Tbl_MasterPop",
"PopEnterDt=#5/10/2006#
AND [loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

Post back and let us know if it worked or if it failed.

I am thinking of something like this:

dcount("tbl_MasterPop.[Loan Acct #]",
"Tbl_MasterPop",
"Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

but the syntax is not quite right I suppose, if this can even be done.

Thanks in advance,
geebee

I have no idea if it can be done, since I don't understand your question.

Thanks John. Now I am thinking more of using this same functionality,
but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee


:

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)



geebee wrote:

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all
those
accounts that exist in tbl_masterpop with a [popenterdt] of
#5/10/2006#
that
had a record in the child tbl_archive, but not for #5/10/2006#, but
for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[geebee]

it does not work. I am getting a #Error?

Well, I don't have any idea if that would work or not.

You could try this variation
DCount ("[Loan Acct #]",
"Tbl_MasterPop",
"PopEnterDt=#5/10/2006#
AND [loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

Post back and let us know if it worked or if it failed.

I am thinking of something like this:

dcount("tbl_MasterPop.[Loan Acct #]",
"Tbl_MasterPop",
"Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

but the syntax is not quite right I suppose, if this can even be done.

Thanks in advance,
geebee

I have no idea if it can be done, since I don't understand your question.


Thanks John. Now I am thinking more of using this same functionality,
but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee


:

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)



geebee wrote:

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all
those
accounts that exist in tbl_masterpop with a [popenterdt] of
#5/10/2006#
that
had a record in the child tbl_archive, but not for #5/10/2006#, but
for
#5/9/2006# instead.

how?

thanks in advance,
geebee

 

[John Spencer]

Then my guess is that DCount is not sophisticated enough to handle nested
subqueries.

it does not work. I am getting a #Error?

Well, I don't have any idea if that would work or not.

You could try this variation
DCount ("[Loan Acct #]",
"Tbl_MasterPop",
"PopEnterDt=#5/10/2006#
AND [loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

Post back and let us know if it worked or if it failed.

I am thinking of something like this:

dcount("tbl_MasterPop.[Loan Acct #]",
"Tbl_MasterPop",
"Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)")

but the syntax is not quite right I suppose, if this can even be done.

Thanks in advance,
geebee


:

I have no idea if it can be done, since I don't understand your
question.


Thanks John. Now I am thinking more of using this same
functionality,
but
within a DCount field. Can this be done, and how?

thanks in advance,
geebee


:

Perhaps the following will work for you.


SELECT Tbl_MasterPop.[Loan Acct #]
, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE Tbl_MasterPop.PopEnterDt=#5/10/2006#
AND tbl_masterpop.[loan acct #] not in
(SELECT T.[Loan Acct #]
FROM Tbl_MasterPop as T
WHERE T.PopEnterDt=#5/9/2006#)



geebee wrote:

hi,

I have the following, in which the syntax is wrong:

SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/10/2006# AND
tbl_masterpop.[loan acct #] not in
(SELECT Tbl_MasterPop.[Loan Acct #], Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt)=#5/9/2006#))));

but I am not sure what to do to fix it. I am trying to get all
those
accounts that exist in tbl_masterpop with a [popenterdt] of
#5/10/2006#
that
had a record in the child tbl_archive, but not for #5/10/2006#,
but
for
#5/9/2006# instead.

how?

thanks in advance,
geebee