Can't get rid of duplicates in my query

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

.

Relevant Pages

  • Re: Cant get rid of duplicates in my query
    ... contain the duplicates on the AP tables, ... Is there a way that I can keep the two queries seperate, ... (((AccPacAPInvDetail INNER JOIN AccPacAPInvHeader ON ...
    (microsoft.public.access.queries)
  • Can DTS do this?
    ... I have a report which I have to complete every quarter. ... in Excel and is called, ... had a training course in Business Practices, ... FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN ...
    (microsoft.public.sqlserver.dts)
  • Dynamic Stored Procedure
    ... I am trying to build a report application for reporting off of SQL Server. ... FROM Events E INNER JOIN CliCore.dbo.tblClients CC ON E.RegNo = CC.RegNo ... Dim strRecordSource As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Dynamic Stored Procedure
    ... form or report you can define the parameters in the InputParameters property ... that is based off of stored procedure "SP_Alpha" that requires a parameter ... > INNER JOIN ... > Dim strRecordSource As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trouble with parameter for crosstab in report
    ... For column headers, you are right, I want QTY across the top. ... except that I only this report to show data for a single given part. ... What I struggling with is the actual crosstab query that mimics your ... FROM ((tblQUOTECORE INNER JOIN tblQUOTE_VALUES ON ...
    (microsoft.public.access.reports)