Can't get rid of duplicates in my query

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
 
M

Michel Walsh

Hi,



GROUP BY on the fields that define what is unique, aggregate with LAST on
the other fields (decorations):


SELECT f1, f2, f3, LAST(f4), LAST(f5), LAST(f6)
FROM somewhere
GROUP BY f1, f2, f3



Sure, that assumes that the "decoration" are always the same, for a given
group, or if they differ, it does not really matter which record push its
decorative values.


Hoping it may help,
Vanderghast, Access MVP


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
Click to expand...
 
Top