Solved

Question on Grouping in Access table

Posted on 2011-03-24
25
387 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:bobby6055
  • 13
  • 8
  • 4
25 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I am sorry but I can't help you more because I don't know those functions.
0
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
Can I upload my own GetLNos routine?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Sure
0
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
Mark:
Any luck with this thread?

Any assistance on the subject will be appreciated.

Bobby
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
*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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
And just following on, in your spreadsheet, why wouldnt 3234 be consolidated if 3828 and 4325 are ?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 3

Author Closing Comment

by:bobby6055
Comment Utility
Excellent Solution
0
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
Thanks
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 3

Author Comment

by:bobby6055
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now