nested query

G

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
 
K

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
 
M

Marshall Barton

geebee said:
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#)
 
J

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#)
 
G

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


John Spencer said:
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
 
J

John Spencer

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


geebee said:
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


John Spencer said:
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
 
G

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


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


geebee said:
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


John Spencer said:
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
 
J

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.

geebee said:
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


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


geebee said:
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
 
G

geebee

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



John Spencer said:
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.

geebee said:
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


John Spencer said:
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
 
J

John Spencer

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

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



John Spencer said:
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.

geebee said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query from form 8
query differences 1
query speed 5
join type not supported 5
delete query 1
day before 1
query addition 1
query error 3

Top