Complex SQL Subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
 
You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
 
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
Is this not the JOIN part of the SQL. Are you saying I need two FROM
statemets or I have two FROM statements. Please advise.

KARL DEWEY said:
You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
Click to expand...
 
Remove your second FROM statement. I thought that I said that.
--
KARL DEWEY
Build a little - Test a little


Ryan said:
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
Is this not the JOIN part of the SQL. Are you saying I need two FROM
statemets or I have two FROM statements. Please advise.

KARL DEWEY said:
You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
Click to expand...
Click to expand...
 
I get a syntax error if I remove the FROM in this statement
=(SELECT MIN(X.[TRTDAT]) AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
which is the statement I need to only show the oldest TRTDAT for each
MBFAM#. Is there another to show only the oldest transaction date (TRTDAT)
for each accouunt number (MBFAM#)? Thank you very much for sticking with me
Remove your second FROM statement. I thought that I said that.
--
KARL DEWEY
Build a little - Test a little


Ryan said:
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
Is this not the JOIN part of the SQL. Are you saying I need two FROM
statemets or I have two FROM statements. Please advise.

KARL DEWEY said:
You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


:

Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
Click to expand...
Click to expand...
 
Try it this way ---
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
WHERE C47FILES_TRANS.[MBFAM#] =[C47FILES_MEMBR].[MBFAM#]))) ORDER BY
C47FILES_TRANS.TRTDAT;
--
KARL DEWEY
Build a little - Test a little


Ryan said:
I get a syntax error if I remove the FROM in this statement
=(SELECT MIN(X.[TRTDAT]) AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
which is the statement I need to only show the oldest TRTDAT for each
MBFAM#. Is there another to show only the oldest transaction date (TRTDAT)
for each accouunt number (MBFAM#)? Thank you very much for sticking with me
Remove your second FROM statement. I thought that I said that.
--
KARL DEWEY
Build a little - Test a little


Ryan said:
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
Is this not the JOIN part of the SQL. Are you saying I need two FROM
statemets or I have two FROM statements. Please advise.

:

You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


:

Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
Click to expand...
Click to expand...
 
It says undefined function 'HAVING' in expression.

KARL DEWEY said:
Try it this way ---
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
WHERE C47FILES_TRANS.[MBFAM#] =[C47FILES_MEMBR].[MBFAM#]))) ORDER BY
C47FILES_TRANS.TRTDAT;
--
KARL DEWEY
Build a little - Test a little


Ryan said:
I get a syntax error if I remove the FROM in this statement
=(SELECT MIN(X.[TRTDAT]) AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
which is the statement I need to only show the oldest TRTDAT for each
MBFAM#. Is there another to show only the oldest transaction date (TRTDAT)
for each accouunt number (MBFAM#)? Thank you very much for sticking with me
Remove your second FROM statement. I thought that I said that.
--
KARL DEWEY
Build a little - Test a little


:

JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
Is this not the JOIN part of the SQL. Are you saying I need two FROM
statemets or I have two FROM statements. Please advise.

:

You have two FROM statements. You neeed to include C47FILES_TRANS in you
JOIN part of the SQL.

--
KARL DEWEY
Build a little - Test a little


:

Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...
Click to expand...
Click to expand...
 
It says undefined function 'HAVING' in expression.

KARL DEWEY said:
Try it this way ---
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
WHERE C47FILES_TRANS.[MBFAM#] =[C47FILES_MEMBR].[MBFAM#]))) ORDER BY
C47FILES_TRANS.TRTDAT;
Click to expand...
Click to expand...

Karl was suggesting that you use this in the SQL, not in your VBA code.

John W. Vinson [MVP]
 
Ryan said:
Below is an SQL asks me to enter a perameter value for X.MBFAM# every time I
try to run this query. I have written several other SQL's just like this
that have worked nicely. The PK for the C47FILES_TRANS table is TRFAM# and
the PK for the C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship
from MBFAM# to TRFAM#. My goal is this, there can be multiple TRTDAT (which
is a date field in text format) for one MBFAM#. I only want to see the
oldest TRTDAT for each MBFAM#. I was sure I wrote this part of the SQL
correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.

SELECT C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY") AS MonthYear,
Format$([TRTDAT],"dd") AS [Day], Format$([TRTDAT],"MM") AS [Month],
Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY INNER JOIN C47FILES_FCLAS ON C47FILES_FAMLY.FMCLAS =
C47FILES_FCLAS.FCNUM) INNER JOIN (C47FILES_DOCTR INNER JOIN (C47FILES_LOC
INNER JOIN C47FILES_MEMBR ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC) ON
(C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR) AND (C47FILES_DOCTR.DRLOC =
C47FILES_LOC.LONUM)) ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#]) INNER
JOIN C47FILES_TRANS ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT, C47FILES_MEMBR.[MBFAM#],
C47FILES_MEMBR.MBFRST, C47FILES_MEMBR.MBLAST, C47FILES_FCLAS.FCNUM,
C47FILES_FCLAS.FCDESC, C47FILES_MEMBR.MBLOC, C47FILES_LOC.LONAME,
C47FILES_TRANS.TRAMT, Format$([TRTDAT],"MMMM YYYY"), Format$([TRTDAT],"dd"),
Format$([TRTDAT],"MM"), Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)=(SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS
AS X WHERE X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;

This SQL asks me to enter a perameter value for X.MBFAM# every time I try to
run this query. I have written severl other SQL's just like this that have
worked. The PK for the C47FILES_TRANS table is TRFAM# and the PK for the
C47FILES_MEMBR table is MBFAM# and has a 1 to 1 relationship from MBFAM# to
TRFAM#. My goal is this, there can be multiple TRTDAT (which is a date field
in text format) for one MBFAM#. I only want to see the oldest TRTDAT for
each MBFAM#. I was sure I wrote this part of the SQL correctly,
=SELECT MIN(X.[TRTDAT]) FROM C47FILES_TRANS AS X WHERE
X.[MBFAM#]=[C47FILES_MEMBR].[MBFAM#])
but I guess I was wrong. I hope this is not to much information to read and
any help is greatly apprieciated.
Click to expand...

Ryan,

Your Original Query, reformatted for legibility:

SELECT C47FILES_TRANS.TRTDAT
,C47FILES_MEMBR.[MBFAM#]
,C47FILES_MEMBR.MBFRST
,C47FILES_MEMBR.MBLAST
,C47FILES_FCLAS.FCNUM
,C47FILES_FCLAS.FCDESC
,C47FILES_MEMBR.MBLOC
,C47FILES_LOC.LONAME
,C47FILES_TRANS.TRAMT
,Format$([TRTDAT],"MMMM YYYY") AS MonthYear
,Format$([TRTDAT],"dd") AS [Day]
,Format$([TRTDAT],"MM") AS [Month]
,Format$([TRTDAT],"YYYY") AS [Year]
FROM ((C47FILES_FAMLY
INNER JOIN
C47FILES_FCLAS
ON C47FILES_FAMLY.FMCLAS = C47FILES_FCLAS.FCNUM)
INNER JOIN
(C47FILES_DOCTR
INNER JOIN
(C47FILES_LOC
INNER JOIN
C47FILES_MEMBR
ON C47FILES_LOC.LONUM = C47FILES_MEMBR.MBLOC)
ON (C47FILES_DOCTR.DRNUM = C47FILES_MEMBR.MBATDR)
AND (C47FILES_DOCTR.DRLOC = C47FILES_LOC.LONUM))
ON C47FILES_FAMLY.FMNUM = C47FILES_MEMBR.[MBFAM#])
INNER JOIN
C47FILES_TRANS
ON (C47FILES_MEMBR.[MBFAM#] = C47FILES_TRANS.[TRFAM#])
AND (C47FILES_FAMLY.FMNUM = C47FILES_TRANS.[TRFAM#])
GROUP BY C47FILES_TRANS.TRTDAT
,C47FILES_MEMBR.[MBFAM#]
,C47FILES_MEMBR.MBFRST
,C47FILES_MEMBR.MBLAST
,C47FILES_FCLAS.FCNUM
,C47FILES_FCLAS.FCDESC
,C47FILES_MEMBR.MBLOC
,C47FILES_LOC.LONAME
,C47FILES_TRANS.TRAMT
,Format$([TRTDAT],"MMMM YYYY")
,Format$([TRTDAT],"dd")
,Format$([TRTDAT],"MM")
,Format$([TRTDAT],"YYYY")
HAVING (((C47FILES_TRANS.TRTDAT)
= (SELECT MIN(X.[TRTDAT])
FROM C47FILES_TRANS AS X
WHERE X.[MBFAM#] = [C47FILES_MEMBR].[MBFAM#])))
ORDER BY C47FILES_TRANS.TRTDAT;


From your description, it looks like MS Access is thinking that the
table C47FILES_TRANS does not have a column named [MBFAM#] (when you
try and execute the query, it asks you to enter a parameter by the
name of "X.MBFAM#").

I do notice something in particular.

You have a subquery on your HAVING clause. Subqueries typically
require "correlation" to work correctly. Correlation requires table
aliases to work.

Your subquery refers to a table in itself via the X alias, and then
refers to another table [C47FILES_MEMBR].[MBFAM#], which is not
defined on the FROM clause and is not correlated to the outer query.
In my opinion, this makes MS Access believe that the right-hand side
of the = sign is a parameter, and not a tablename.columnname reference
as you intended.

Essentially, there is no correlation for the subquery to work with.

You need to give the C47FILES_MEMBR table an alias in the outer query,
and then use that alias in the inner query when referring to [MBFAM#]
on the right-hand side of the = sign so that the inner query can "see"
the table in the outer query.

Example:

(C47FILES_LOC
INNER JOIN
C47FILES_MEMBR AS M1
ON C47FILES_LOC.LONUM = M1.MBLOC)

HAVING (((C47FILES_TRANS.TRTDAT)
= (SELECT MIN(X.[TRTDAT])
FROM C47FILES_TRANS AS X
WHERE X.[MBFAM#] = M1.[MBFAM#])))

You will need to change all instances of "tablename.columnname" for
this table to "M1.columnname".

I hope I am right about that. If I am wrong, my apologies.


Sincerely,

Chris O.
 
Back
Top