Solved

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

Posted on 2011-05-08
529 Views
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;
``````
Ccon-Logic-Sample.mdb
0
Question by:bobby6055

LVL 51

Expert Comment

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;
``````
0

LVL 3

Author Comment

Mark:
Give me some time to review this to be sure with the logic. I'll get to you very soon.
0

LVL 3

Author Comment

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;
``````
Ccon-Logic-Sample.mdb
CCon-Logic-Sample.xls
0

LVL 51

Expert Comment

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

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 51

Expert Comment

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);
``````

0

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 51

Expert Comment

Well, we are now using "group by" so they will be distinct. Previously, using distinct was to ensure no duplicate values.
0

LVL 3

Author Comment

Please give me sometime to test your last posted solution as it's a little more complex than other questions.
0

LVL 3

Author Comment

ok for your response under ID:35719611
Thanks
0

LVL 3

Author Comment

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.

0

LVL 51

Expert Comment

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

LVL 3

Author Comment

What is the remedy?
0

LVL 51

Expert Comment

*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

LVL 3

Author Comment

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

LVL 51

Expert Comment

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

LVL 3

Author Comment

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);
``````
Ccon-Latest-test.xls
0

LVL 51

Expert Comment

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)
``````
0

LVL 3

Author Comment

Mark:

Thank you.
Ccon-Wrong-Result.xls
0

LVL 51

Expert Comment

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)
``````

0

LVL 3

Author Comment

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

LVL 51

Accepted Solution

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] & "'"  &

" 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)
``````
0

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 51

Assisted Solution

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
``````
0

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 3

Author Closing Comment

Brillian SQL guy
0

LVL 51

Expert Comment

A pleasure, and thanks for your kind words. Great to see the routine works for you.
0

## Featured Post

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…