Question on Grouping in Access table

Below is a link to an artticle on concatenating values associated with a group:
http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

If we add the DConcat function from that article (code below), we can write a query like this:

SELECT tblS.BNo, tblS.SNo,
DConcat("LNo","tblS","[BNo] = " & [BNo] & " And [SNo] = '" & [Sno] & "'",", ") AS xLNo,
DConcat("HNo","tblS","[BNo] = " & [BNo] & " And [SNo] = '" & [Sno] & "'",", ") AS xHNo,
DConcat("Street","tblS","[BNo] = " & [BNo] & " And [SNo] = '" & [SNo] & "'",", ","",True) AS xSTreet
FROM tblS
GROUP BY tblS.BNo, tblS.SNo;

Using this routine, I will like to amend the QUERY in the Code Snippet such that it returns the desired result based on the DConcat function.

In other words, get rid of my own previous routine Function "GetLNos" and replace my own routine with the DConcat function in it's place.

This post therfeore is only for changing routine from Function GetLNos to a DConcat Function.

If you require a working sample db of my working previous routine inside that query, please let me know and I'll upload it.
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=-1,"TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((((

    SELECT  DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.LNo,  tblMain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, [CCon], tblMain.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,0)= 0)

    UNION

    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow), tblMain.CCon, tblMain.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,0)= -1)

    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,
              GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

) 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

LVL 3
bobby6055Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
You can compare the following query which ignores the union bit (and slightly updated / fixed use of dconcat function as well)

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

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Get ride of functions in GROUP BY.
GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

Open in new window

0
 
bobby6055Author Commented:
Below are the two sections of my SQL with GetHNos Function (which I want to replace with DConcat function).

I will appreciate a full suggested SQL post of your idea using DConcat function....

(1).
 SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID,
            GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow), tblMain.CCon, tblMain.SerialNo, tblMain.LocalID

(2).
         GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,
              GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
              GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
              tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What I wanted to say is that UNION statement should be like this
SELECT  DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.LNo,  tblMain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, [CCon], tblMain.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,0)= 0)

    UNION

    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow), tblMain.CCon, tblMain.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,0)= -1)

    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,
              tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

Open in new window

0
 
bobby6055Author Commented:
Thank you for your posting.

Here is what I wanted:
(a). I want to get rid of my call for the "GetLnos" in the query
(b). and replace it with DConcat function

This is all I wanted.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am sorry but I can't help you more because I don't know those functions.
0
 
bobby6055Author Commented:
Can I upload my own GetLNos routine?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure
0
 
bobby6055Author Commented:
VMontalvao

See one of my previously accepted solution for the query above at this link. You will also see a link to download the accepted sample solution. This link will give you additional information on how the above query was derived.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25015936.html#ID:26335419
0
 
Mark WillsTopic AdvisorCommented:
Hi boby,

A little birdie tapped me on the shoulder about your question :)

Do you mean the download in http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25015936.html#a26336014 is still the same solution in terms of changing the "get?nos" to the "dconcat" function ?
0
 
bobby6055Author Commented:
Yes... you are right...it's ID:26336014 form your referenced link.

What is required here is simply substituting GetLNos with DConcat function. This is all I am requesting in this question.
0
 
bobby6055Author Commented:
Mark:
Any luck with this thread?

Any assistance on the subject will be appreciated.

Bobby
0
 
Mark WillsTopic AdvisorCommented:
Hi Bobby,

Have finally included the DConcat function. Sincere apologies about the delay.

Please see attached V3c MDB and the query : Bobby_GetLnos_with_Dconcat_Query

Also a "testing" query showing just the old Lno and the new xLno modules.

It includes both old "Lno" and new (using dconcat) "xLno" just need to replace the old with the new rather than having both.
GetH-LNos-V3c-outer-join.mdb
0
 
Mark WillsTopic AdvisorCommented:
New MDB now attached, so (if Bobby doesnt mind) will object to the current close request. If Bobby does not need the solution above, can close again.
0
 
bobby6055Author Commented:
Mark:
Of course, I dont intend to cancel this question with your latest assistance. I will test extensively and provide you with a feedback later.

Thanks for squeezing out some time to assist despite your busy schedule.

Bobby
0
 
bobby6055Author Commented:
Mark:
The objective of this question is to be able to GROUP records of the same type "groupings".
First we need to get rid of the GetLNos and GetHNos Routine completely
...and apply DConCat function to BNo / xLNo; and HouseNo / Streetname

For example, I utilized your query "Bobby_GetLnos_with_Dconcat_Query" for this example
as shown in the code snippet below.

If user types in BEGINDATE as 07/01/09 and the ENDDATE is 07/31/09
...then the following is the result the user SHOULD obtain (all things being equal) in the scenerio below

(1). group ALL xLNos that matches a single BNo, followed by HouseNo, StreetName, GType, SCType, SType, CCon, Local, SerialNo
(2). If LNo / xLno and HouseNo are grouped (per the attached EXCEL file), then by all means skip QFlow cell but show the result of QFlowA in the next cell.
(3). Also to be considered in the grouping are other fields like Ccon, Local an serialNo such that grouping fields to be considred are:
      BNo, xLno, HouseNo, Streetname, Ccon,Local and SerailNo .....then show the result of the calculated QFlowA for each group.

..otherwise show the result of both the QFlow and QFlowA in each cell (see the attached sample excel file Grouped-Excel-Sample-01.xls

I will appreciate your further assistance with this
PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;
SELECT Q3.BNo, Q3.LNo, Q3.xLNo, Q3.HouseNo, Q3.StreetName, tblGroupS.GType, tblSCType.SCType, tblSType.SType, Q3.QFlow, Q3.QFlow/4 AS QFlowA, IIf(Q3.CCon=-1,"TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((([SELECT  DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.LNo, tblMain.LNo as xLNo, tblMain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, [CCon], tblMain.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,0)= 0)

    UNION

    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            DConcat("LNo","tblmain","[GroupID] = '" & [GroupID] & "'" & 
                    " AND [BNo] = " & [BNo] & 
                    " And nz([SerialNo],'0') = '" & nz([SerialNo],0) & "'" & 
                    " and [sctypeid] = '" & [sctypeid] & "'"  & 
                    " and [stypeid] = '" & [stypeid] & "'" & 
                    " And nz([CCon],'0') = '" & nz([CCon],0) & "'"  & 
                    " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS xLNo,

            GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow), tblMain.CCon, tblMain.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,0)= -1)

    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,
              GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

]. 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
ORDER BY Q3.BNo;

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
*laughing*

I kinda knew this wouldnt be the end result :)

Just as a basis... your question asked to replace the getlnos module with the dconcat module. So, I used the same criteria in the dconcat as was used in the getlnos.

I know I added it as an extra column and left the original in there - the reason was for testing and comparison to make sure it was reporting the same results.

Now, the original was doing a union query ie select from ( "first_part" union "second_part" )  where the "first_part" was only rows that had CCON = 0 and the "second_part" are rows where CCON <> 0

We can see in the spreadsheet that all those entries have ccon reported as "FALSE" which (according the the IIF statement) means that CCON was 0 and also means that all rows were returned by "first_part" and no rows returned by "second_part" which contains the group bys.

So, even your original query doesnt quite match the spreadsheet.

Is there a slightly newer refinement that we need to consider for when we do the aggregation for lno and houseno ?
0
 
Mark WillsTopic AdvisorCommented:
And just following on, in your spreadsheet, why wouldnt 3234 be consolidated if 3828 and 4325 are ?
0
 
bobby6055Author Commented:
Mark:
What can I say. Y=ou are the best. This is exactly what I am looking for.

... by the way, you handled the "Dconcat function" very well.

Question:
Is "cdate" a reserved word?
....Looking at your last query design, I could not find "cdate" in the design however it was part of the SQL view that was utilized.

Thanks
Bobby
0
 
bobby6055Author Commented:
Excellent Solution
0
 
bobby6055Author Commented:
Mark:
It's quite amazing how you utlized "cdate" here - It's great. Would you mind explaining the logic behind it - just for the understanding of the logic?

(a). " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS LNo,

(b). " And appdate between cdate('" & format([Forms]![ReportDateRange]![BeginDate],"dd MMM yyyy") & "') and cdate('" & format([Forms]![ReportDateRange]![EndDate],"dd MMM yyyy") & "') ",", ") AS HouseNo,
0
 
Mark WillsTopic AdvisorCommented:
Well, cdate() is a function to convert a string to a date. Being unsure of your dateformats / datetimes etc thought it was "safer" to express the date in a universal string and then convert it within the dynamic SQL so we can compare with appdate.  Probably would have also worked using the #<datestring># type construct as well.
0
 
bobby6055Author Commented:
Thanks
0
 
Mark WillsTopic AdvisorCommented:
Hey Bobby,

Following on from one of your other questions, if CCON is a text field then the above code is still treating it like a checkbox.

Not sure if it is wanted, but, the text version of the code should be something like :

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

0
 
bobby6055Author Commented:
Mark:
Thank you for your assistance with this question.

I have some some flaws in my current ccon logic. As such, I will like to explore the "ccon logic and the dconcat function" in specific terms.

To this end, I have created a related question at:
http://www.experts-exchange.com/index.jsp?qid=27025047

I will appreciate further assistance from you.

Cheers !
Bobby
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.