G
Gary.Crider
I am trying to combine two quries. I am trying to do this to be able
to put all of this info on one report. The problem that I am running
into is that my query is containing duplicate values. It will only
contain the duplicates on the AP tables, i believe because the PO
tables are linked to them and all the values there are different, so
therefore the DISTINCT is not working out, becuase the rows are
different for the second half. Does anyone know how to work around
this? Is there a way that I can keep the two queries seperate, and put
them into the report seperate? The main problem is that the duplicates
on the ApTotal are being added too many times giving false data.
Y = " SELECT DISTINCT AccPacAPInvDetail.CNTBTCH,
AccPacAPInvDetail.CNTITEM, AccPacAPInvHeader.FISCYR,
AccPacAPInvHeader.FISCPER, AccPacAPVendor.VENDNAME,
AccPacAPInvDetail.IDGLACCT, AccPacAPInvDetail.AMTGLDIST,
AccPacAPInvHeader.AMTINVCTOT, AccPacAPInvHeader.IDINVC,
AccPacGLMasterFile.ACCTDESC, AccPacAPInvHeader.TEXTTRX,
IIf([texttrx]=3,[amtgldist]*-1,[amtgldist]) AS ApTotal,
AccPacAPInvHeader.DATEINVC, dbo_POINVAH.DAYENDSEQ,
dbo_POINVAH.INVAHSEQ, dbo_POINVAH.FISCYEAR, dbo_POINVAH.FISCPERIOD,
dbo_POINVAL.GLITEM, dbo_POINVAL.FCEXTTOTAL, dbo_POINVAH.FCAPTOTAL,
dbo_POINVAH.INVNUMBER, dbo_POINVAH.TRANSDATE, dbo_POINVAH.TRANSTYPE,
IIf([dbo_POINVAL.transtype]=3,[fcexttotal]*-1,[fcexttotal]) AS PoTotal,
[ApTotal]+[PoTotal] AS total, dbo_POINVAH.VENDORNAME"
Y = Y & " FROM (dbo_POINVAH INNER JOIN dbo_POINVAL ON
(dbo_POINVAH.INVAHSEQ = dbo_POINVAL.INVAHSEQ) AND
(dbo_POINVAH.DAYENDSEQ = dbo_POINVAL.DAYENDSEQ)) INNER JOIN
(((AccPacAPInvDetail INNER JOIN AccPacAPInvHeader ON
(AccPacAPInvDetail.CNTBTCH = AccPacAPInvHeader.CNTBTCH) AND
(AccPacAPInvDetail.CNTITEM = AccPacAPInvHeader.CNTITEM)) INNER JOIN
AccPacAPVendor ON AccPacAPInvHeader.IDVEND = AccPacAPVendor.VENDORID)
INNER JOIN AccPacGLMasterFile ON AccPacAPInvDetail.IDGLACCT =
AccPacGLMasterFile.ACCTFMTTD) ON (dbo_POINVAH.VENDOR =
AccPacAPVendor.VENDORID) AND (dbo_POINVAL.GLITEM =
AccPacGLMasterFile.ACCTFMTTD)"
Y = Y & " WHERE(((AccPacAPInvHeader.FISCYR) =
[Forms]![mainmenu]![cboIEContractYear]) And
((AccPacAPInvHeader.FISCPER) = [Forms]![mainmenu]![cboIEAccPacMnths])
And ((AccPacAPInvDetail.IDGLACCT) Like '*-" & X & "-*') And
((dbo_POINVAH.FISCYEAR) = [Forms]![mainmenu]![cboIEContractYear]) And
((dbo_POINVAH.FISCPERIOD) = [Forms]![mainmenu]![cboIEAccPacMnths]) And
((dbo_POINVAL.GLITEM) Like '*-" & X & "-*'))"
Y = Y & " ORDER BY AccPacAPInvDetail.IDGLACCT, dbo_POINVAL.GLITEM;"
me.recordsource = Y
to put all of this info on one report. The problem that I am running
into is that my query is containing duplicate values. It will only
contain the duplicates on the AP tables, i believe because the PO
tables are linked to them and all the values there are different, so
therefore the DISTINCT is not working out, becuase the rows are
different for the second half. Does anyone know how to work around
this? Is there a way that I can keep the two queries seperate, and put
them into the report seperate? The main problem is that the duplicates
on the ApTotal are being added too many times giving false data.
Y = " SELECT DISTINCT AccPacAPInvDetail.CNTBTCH,
AccPacAPInvDetail.CNTITEM, AccPacAPInvHeader.FISCYR,
AccPacAPInvHeader.FISCPER, AccPacAPVendor.VENDNAME,
AccPacAPInvDetail.IDGLACCT, AccPacAPInvDetail.AMTGLDIST,
AccPacAPInvHeader.AMTINVCTOT, AccPacAPInvHeader.IDINVC,
AccPacGLMasterFile.ACCTDESC, AccPacAPInvHeader.TEXTTRX,
IIf([texttrx]=3,[amtgldist]*-1,[amtgldist]) AS ApTotal,
AccPacAPInvHeader.DATEINVC, dbo_POINVAH.DAYENDSEQ,
dbo_POINVAH.INVAHSEQ, dbo_POINVAH.FISCYEAR, dbo_POINVAH.FISCPERIOD,
dbo_POINVAL.GLITEM, dbo_POINVAL.FCEXTTOTAL, dbo_POINVAH.FCAPTOTAL,
dbo_POINVAH.INVNUMBER, dbo_POINVAH.TRANSDATE, dbo_POINVAH.TRANSTYPE,
IIf([dbo_POINVAL.transtype]=3,[fcexttotal]*-1,[fcexttotal]) AS PoTotal,
[ApTotal]+[PoTotal] AS total, dbo_POINVAH.VENDORNAME"
Y = Y & " FROM (dbo_POINVAH INNER JOIN dbo_POINVAL ON
(dbo_POINVAH.INVAHSEQ = dbo_POINVAL.INVAHSEQ) AND
(dbo_POINVAH.DAYENDSEQ = dbo_POINVAL.DAYENDSEQ)) INNER JOIN
(((AccPacAPInvDetail INNER JOIN AccPacAPInvHeader ON
(AccPacAPInvDetail.CNTBTCH = AccPacAPInvHeader.CNTBTCH) AND
(AccPacAPInvDetail.CNTITEM = AccPacAPInvHeader.CNTITEM)) INNER JOIN
AccPacAPVendor ON AccPacAPInvHeader.IDVEND = AccPacAPVendor.VENDORID)
INNER JOIN AccPacGLMasterFile ON AccPacAPInvDetail.IDGLACCT =
AccPacGLMasterFile.ACCTFMTTD) ON (dbo_POINVAH.VENDOR =
AccPacAPVendor.VENDORID) AND (dbo_POINVAL.GLITEM =
AccPacGLMasterFile.ACCTFMTTD)"
Y = Y & " WHERE(((AccPacAPInvHeader.FISCYR) =
[Forms]![mainmenu]![cboIEContractYear]) And
((AccPacAPInvHeader.FISCPER) = [Forms]![mainmenu]![cboIEAccPacMnths])
And ((AccPacAPInvDetail.IDGLACCT) Like '*-" & X & "-*') And
((dbo_POINVAH.FISCYEAR) = [Forms]![mainmenu]![cboIEContractYear]) And
((dbo_POINVAH.FISCPERIOD) = [Forms]![mainmenu]![cboIEAccPacMnths]) And
((dbo_POINVAL.GLITEM) Like '*-" & X & "-*'))"
Y = Y & " ORDER BY AccPacAPInvDetail.IDGLACCT, dbo_POINVAL.GLITEM;"
me.recordsource = Y