Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Logic of "Yes" and "No" and consolidation of numbers

This post has a related question. Please check it out.

I recently found out that the current CCON logic in my query has flaws - "ccon logic" and the application of "Dconcat function" MUST work hand in hand.

Solution obtained for the new query should explore the following logic.

LOGIC of ccon (“YES” or “No”) - 3 digit text field - This is "not" a checkbox field:

(1). For all LNos that matches BNo, if ccon is “Yes” then ….
     (a).  Consolidate all LNos and HouseNos matching the BNo into one cell  

     (b).  sum(tblMain.qFlow); sum(tblMain.qFlowA) for the  all LNos and HouseNos matching
     (c). ccon populated data MUST indicate “Yes” that justifies the consolidation of the LNos and HouseNos.

(2). For all LNos that matches BNo, if ccon is “No” then ….
      (a). List each LNo and HouseNo separately  - not consolidated
      (b). list each qFlow and qflowA separately since LNos and HouseNos are listed separately.
      (c).  ccon populated data for each LNo / HouseNo MUST indicate “No” that justifies non-consolidation.

I have placed the current working query in code snippet - I will appreciate the tweaking of this query to
synchronize with the above defined logic.
PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;
SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((((SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 

    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID;

Open in new window

Ccon-Logic-Sample.mdb
0
bobby6055
Asked:
bobby6055
  • 20
  • 12
2 Solutions
 
Mark WillsTopic AdvisorCommented:
so we need to handle the dconcat differently depending on ccon = "Yes"

So, maybe we go back to the UNION query that once existed

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;
SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((((

    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"


    UNION ALL


    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, tblMain.STypeID, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 
LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID;

Open in new window

0
 
bobby6055Author Commented:
Mark:
Give me some time to review this to be sure with the logic. I'll get to you very soon.
0
 
bobby6055Author Commented:
Mark:
I modified your query (to further restrict how data should be pulled) by adding this line:

   WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'))
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
   AND     ((tblMain.STypeID) In ("SA","CM"))                                                     '<---------------- Added this line
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate]
    AND      (tblMain.Appdate)  Is Not Null)
    AND     nz(tblmain.ccon,"No")  = "No"

....after modified the query I tested it and found that some of the consolidated rows were duplicated. Instead of one row I obtained
two rows of duplicated record for BNo 3487 for example (see the attached excel file for your reference).

I will appreciate it if your query can further be tweaked in such a way that unique LNos and HouseNos matching BNo 3487 will be populated into just one rows
- not two rows.

I hereby attach my updated sample db, Output Excel file and the modified Query (in the code snippet)
PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;
SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((((SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
   AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, tblMain.STypeID, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
   AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID;

Open in new window

Ccon-Logic-Sample.mdb
CCon-Logic-Sample.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mark WillsTopic AdvisorCommented:

Well, they do have different gtype's and we are grouping by gtype, so not sure what you are asking.

Do you want to ignore groupid ? Are there any columns in the DConcat call that shouldnt be there ?
0
 
bobby6055Author Commented:
Yes, Mark,
There were flaws in my original GetLnos and GetHNos and I am now trying to correct it now using the dconcat.
I am trying to specify the specific type of records needed in this test with a view to having control on the output.
Essentially, GroupID, SCTypeID and STypeId are very relevant here.
0
 
bobby6055Author Commented:
You also asked:
 Are there any columns in the DConcat call that shouldnt be there ?

My response: No
The columns are correct as it's currently defined.
0
 
bobby6055Author Commented:
Mark:
Taking a hard look at the tblMain and different GTypes, Let us eliminate the test for GroupID in dconcat and see what result we would get.
0
 
Mark WillsTopic AdvisorCommented:
We would also need to remove it from the group by otherwise we will definitely have duplicates with the only thing different being the groupID in the select.

as per :

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT  Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo

FROM ((((SELECT  tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, tblMain.STypeID, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 
LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID);

Open in new window


0
 
bobby6055Author Commented:
In the GetLNos and GetHNos routine, the word DISTINCT was agged to the select query. Is there any reason why we did not use that here? I am just curious.
0
 
bobby6055Author Commented:
ooops typos...
In the GetLNos and GetHNos routine, the word DISTINCT was utilized in the SELECT query. Is there any reason why we did not use that here? I am just curious.
0
 
Mark WillsTopic AdvisorCommented:
Well, we are now using "group by" so they will be distinct. Previously, using distinct was to ensure no duplicate values.
0
 
bobby6055Author Commented:
Please give me sometime to test your last posted solution as it's a little more complex than other questions.
0
 
bobby6055Author Commented:
ok for your response under ID:35719611
Thanks
0
 
bobby6055Author Commented:
Mark:
I conducted a test of your last posted query based my new sample under ID:35717007.

Using BNo 3487, I consolidated all LNos and HouseNos, your query is pulling far less data than expected. For example, in tblMain, BNo 3487 totals about 32 but in your query, I am getting about 16 which shows that there are still some problems with the query based on the ccon logic and dconcat.

Please download my latest sample under ID:35717007 and test using 12/01/09 and 12/31/09 respectively.
0
 
Mark WillsTopic AdvisorCommented:
Well, that is about right. We are not differentiating by group, so it is the number of differnet Lno + Houseno that can be consolidated.

For example, if we very simply do : SELECT distinct houseno, lno FROM tblMain where bno = 3487

then we find a total of 18 rows.

I dont think it is a problem with ccon logic and dconcat - just that we now ignore groupid (from everything).
0
 
bobby6055Author Commented:
What is the remedy?
0
 
Mark WillsTopic AdvisorCommented:
*laughing* I am not so sure of what the problem really is anymore :) So, dont really know how to resolve.

But you can either have it by groupid or not. The above queries show both with groupid (and the three rows for 3487) or without groupid (with two rows).

In the non-groupid dependant (ie not used in group by) It is not going to show 32 different numbers, because there are only 18 different numbers. Or, do you want to see the number 122 for LNo because it appears for both groupid DEC and DOC ?

In your spreadsheet (from 35717007) you have two lines almost the same - one for group Test5 and one for group Test7 (row numbers 46 and 47 highlighted in yellow) - how do you want them to appear ?

0
 
bobby6055Author Commented:
Based on the GetLNos and GetHNos routine, definitely, the GroupID must be utilized   I think the problem is in my sample data. Let me reconfigure my tblMain first, then I will re-use your query under ID:35717007.

Secondly, why are these different in your posted queries?

Before the UNION ALL ...the following is set to "No"  

....  AND     nz(tblmain.ccon,"No")  = "No"


but.....
After the UNION ALL it is set to "Yes"

....  AND     nz(tblmain.ccon,"No")  = "Yes"


What is the logic behind different use of  "No" and "Yes" with tblMain.ccon, ......?
0
 
Mark WillsTopic AdvisorCommented:
OK, first we need to set the same assumption for a default value for CCON. We use nz(tblmain.ccon,"No") to handle a NULL condition (so we are further assuming that there are only three values : yes, No or NULL).

In using the NZ() function, we are saying if it is NULL then lets pretend it is the same as having a "No" in there.

The select above the union are all details, those that are not subject to consolidation. Hence we want to check for nz(tblmain.ccon,"No")  = "No"
However, the select statement after the union we do need to use the dconcat function / take into account the consolidation and so we only want to process nz(tblmain.ccon,"No")  = "Yes"

I guess the select above the union could have also been written : nz(tblmain.ccon,"No")  <> "Yes"

Does that make sense ?

0
 
bobby6055Author Commented:
Mark:
I have decided to stick to your previous query under ID:35717007 (see reposting at code snippet) as your best on the subject.......
but it requre just a minor tweak to make it perfect as it handled the GROUPID very well.

Based on my test result, I hav arrived at the following conclusion - if this is tweaked as shown below,
it should correct the logic.

   When we added this line ".................AND     ((tblMain.STypeID) In ("SA","CM")).........."
      It was designed to pull specific group of data shown below...

         WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'))
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))                        <------- Therefore ("SA","CM")) from STYPEID  should be recognized as part of
                                                                                                         the GROUPID, SCTypeID combined ONLY if ccon = "Yes"

In that case, the logic of ccon = "Yes" should  do the following:
(a). In ccon is "Yes" and records indicated both the "SA" and CM" (- that is OnCampus and OffCampus) then.......
       "GROUP ALL records matching BNo on "LNos" and "HouseNos" in one ROW.

For example, BNo 2444 has 2 different ROWS in the attached output spreadsheet (using date range 12/01/09 and 12/31/09),
there were two ROWS unstead of one in my output result - so by eliminating column "STYpe" (in the EXPECTED RESULT)
- and combining the two ROWS of BNo 2444 into one FINAL single ROW of BNo 2444 should do the trick.

However (if ccon is "No" for any of the two rows of BNo 2444 -  that is if one row out of the two ROWS of BNo 2444 has it's ccon = "Yes"
and the other ROW has it's ccon = "No" then leave the rows separate as two different ROWS.

Take note that in order for the 2 ROWS of BNo 2444 to be combined together - both ccon for the two different ROWS MUST be set to "Yes"
otherwise threat thedata as if it's ccon = FALSE.

I think this should resolve all concerns if you would kindly assist.
If you reqquire that I upload my modified Access tblMain, please let me know - I will do so.
PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT  Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo

FROM ((((SELECT  tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, tblMain.STypeID, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[BNo] = " & [BNo] &
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 
LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID);

Open in new window

Ccon-Latest-test.xls
0
 
Mark WillsTopic AdvisorCommented:
OK, then we need to remove stypeid from our query, and put in the correct selection for the dconcat function :

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo

FROM ((((SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, tblMain.STypeID, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 

Open in new window

0
 
bobby6055Author Commented:
Mark:
Upon trying your last posted query, the output had several duplicates(see the attached spreadsheet).

Thank you.
Ccon-Wrong-Result.xls
0
 
Mark WillsTopic AdvisorCommented:
Rats, missed one of the instances of tblMain.STypeID - it needs to be removed from the group by on line 43 above.

Now, I dont really have any data to test this particular condition with, but, pretty sure the only problem was the group by infestation of that pesky stypeid column. So, if there is still a problem, then would like to grab some of that data you mentioned before :)

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo

FROM ((((SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 

Open in new window


0
 
bobby6055Author Commented:
Mark:
Final question.

I finally tested your query against a typographical Streetname - the result produced duplicate records (see the attachjed spreadsheet)

I amended tblMain to create a scenerio WHERE user types in a wrong streetname. That throws the grouping into disaray.
For example, in tblMain

GroupID      BNo      LNo      HouseNo      StreetName      CCon      SCTypeID      STypeID
STU      2296      49      55      James Street      Yes      MTH      CM                            '<----- James Street (is a wrongly entered Street name)
STU      2296      10      346      East 134th Street      Yes      MTH      CM
STU      2296      9      344      East 134th Street      Yes      MTH      CM

Upon entering the range dates and the data is populated, it duplicated BNo 2296 and the consolidated LNos and HouseNos.

I have provided a sample data of what is expected in a case of a wrong streetname.

This is my final question on this thread. If we resolve this - that should put this question to rest.

Again, it has been a very challenging post. Thank you.
 
Ccon-Wrong-Result-2.xls
0
 
Mark WillsTopic AdvisorCommented:
Well, streetname is not being passed into the dconcat function.

So, we have to add that in.

ie after the line in (every instance of dconcat) of :

 " and [sctypeid] = '" & [sctypeid] & "'"  &

we add

 " and [streetname] = '" & [streetname] & "'"  &

then that should do it...

Basically we need to make sure that the various elements of the group by are somehow being accounted for (even if that means deliberately ignoring it) in the dconcat function otherwise we can run into situations where it might look duplicated. Conversely we might need to remove elements from the group by (like we did with stypeid).

Does that make sense ?

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.ccon="Yes","TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo

FROM ((((SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, tblmain.LNo, tblmain.HouseNo, 
            tblMain.StreetName, tblMain.QFlow ,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "No"

    UNION ALL


    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
 " and [streetname] = '" & [streetname] & "'"  &
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,
            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
 " and [streetname] = '" & [streetname] & "'"  &
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
            tblMain.StreetName, sum(nz(tblMain.QFlow,0)) as qflow,nz(tblmain.ccon,"No") as ccon, nz(tblmain.SerialNo,"") as serialno, tblMain.LocalID
    FROM    tblMain
    WHERE   ((tblMain.GroupID)  In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) 
    AND     ((tblMain.SCTypeID) In ("MTH","LA","RC"))
    AND     ((tblMain.STypeID) In ("SA","CM"))
    AND     ((tblMain.Appdate)  Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate)  Is Not Null) 
    AND     nz(tblmain.ccon,"No")  = "Yes"


    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.StreetName, nz(tblmain.ccon,"No"), nz(SerialNo,""), tblMain.LocalID,
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
 " and [streetname] = '" & [streetname] & "'"  &
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", "),

            DConcat("HouseNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],"&""&") = '" & nz(tblmain.SerialNo,"") & "'" & 
                    " And nz([CCon],"&"No"&") = '" & nz(tblmain.ccon,"No") & "'" &
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
 " and [streetname] = '" & [streetname] & "'"  &
                    " and (tblMain.STypeID) In ('SA','CM')" & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ")


) AS Q3 
LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 

Open in new window

0
 
bobby6055Author Commented:
Mark:
I tested your last posted query but the previously reported duplicates did not go away.

I have put together another sample db and included another spreadsheet for illustration.
The sample I now provide illustrate the way my office db data look like with wrong streetnames

If we can resolve it - it will make it possible to spot bad streetname problems and correct them where necessary
in order to get accurate report. Thank you

Range dates to use for this new test: 07/01/09 and 07/31/09.


Ccon-Logic-Sample-4.mdb
Ccon-Result-3.xls
0
 
bobby6055Author Commented:
Mark:
Thank you for assiting me on this post. My last post under ID:35745097 was because the issue of duplicated record still persisted despite you latest query under ID:35743532.

If you think that the problem could be not be resolved or if you believe that I should post a new question on the subject matter, please let me know in your response. I will then close this post according to your advise.

Cheers !

Bobby
0
 
Mark WillsTopic AdvisorCommented:
Ummmm... I get those results, with the addition of SID 588 (ie LNo 49) being added to Lno's 1, 11

Maybe it is the sort order  ? They dont appear together...

Would be worthwhile adding down the very bottom an ORDER BY clause e.g.

ORDER BY tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName

Open in new window

0
 
bobby6055Author Commented:
Mark:
We are very, very close to solving the problem.

After I added your latest suggestion, there were still duplication of LNo and HouseNo for the matching BNo 2296 (see the attached spreadsheet).

Do you think that the "Dconcat function" is somehow flawed in principle?
Ccon-Result-4.xls
0
 
bobby6055Author Commented:
Mark:
My mistake. When I first tested, I mistakenly combined your last suggested idea with the query under ID:35737592.

One hour later, I revisited it again but this time I tried your last suggested idea with the query under ID:35743532.

It works fine as expected. My apologies.

You were in deed a great SQL guy. I must agree the thread has been a brain tasking one and only talented person and thorough person like you can take the pain of solving a complex question. I am impressed.

Bravo !!!  Keep it up.

Cheers
Bobby
0
 
bobby6055Author Commented:
Brillian SQL guy
0
 
Mark WillsTopic AdvisorCommented:
A pleasure, and thanks for your kind words. Great to see the routine works for you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 20
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now