bobby6055
asked on
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.
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;ASKER
Mark:
Give me some time to review this to be sure with the logic. I'll get to you very soon.
Give me some time to review this to be sure with the logic. I'll get to you very soon.
ASKER
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','D
AND ((tblMain.SCTypeID) In ("MTH","LA","RC"))
AND ((tblMain.STypeID) In ("SA","CM")) '<---------------- Added this line
AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
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)
CCon-Logic-Sample.xls
I modified your query (to further restrict how data should be pulled) by adding this line:
WHERE ((tblMain.GroupID) In ('BOE','LT','DDC','DEC','D
AND ((tblMain.SCTypeID) In ("MTH","LA","RC"))
AND ((tblMain.STypeID) In ("SA","CM")) '<---------------- Added this line
AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
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.xls
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 ?
ASKER
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.
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.
ASKER
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.
Are there any columns in the DConcat call that shouldnt be there ?
My response: No
The columns are correct as it's currently defined.
ASKER
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.
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.
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 :
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);ASKER
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.
ASKER
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.
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.
Well, we are now using "group by" so they will be distinct. Previously, using distinct was to ensure no duplicate values.
ASKER
Please give me sometime to test your last posted solution as it's a little more complex than other questions.
ASKER
ok for your response under ID:35719611
Thanks
Thanks
ASKER
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.
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.
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).
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).
ASKER
What is the remedy?
*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 ?
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 ?
ASKER
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, ......?
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, ......?
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 ?
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 ?
ASKER
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','D
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.
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','D
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);
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) ASKER
Mark:
Upon trying your last posted query, the output had several duplicates(see the attached spreadsheet).
Thank you.
Ccon-Wrong-Result.xls
Upon trying your last posted query, the output had several duplicates(see the attached spreadsheet).
Thank you.
Ccon-Wrong-Result.xls
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 :)
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) ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
ASKER
Brillian SQL guy
A pleasure, and thanks for your kind words. Great to see the routine works for you.
So, maybe we go back to the UNION query that once existed
Open in new window