[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Question on Grouping in Access table

Posted on 2011-03-24
25
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
  • 4
25 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 35215488
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
ID: 35219662
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 52

Expert Comment

by:Vitor Montalvão
ID: 35230384
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Author Comment

by:bobby6055
ID: 35231007
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 52

Expert Comment

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

Author Comment

by:bobby6055
ID: 35245000
Can I upload my own GetLNos routine?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 35252796
Sure
0
 
LVL 3

Author Comment

by:bobby6055
ID: 35254007
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
ID: 35346777
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
ID: 35347286
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
ID: 35391275
Mark:
Any luck with this thread?

Any assistance on the subject will be appreciated.

Bobby
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35498304
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35498312
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
ID: 35498586
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
ID: 35498722
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
ID: 35499198
*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
ID: 35499250
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 2000 total points
ID: 35499306
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
ID: 35500088
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
ID: 35500247
Excellent Solution
0
 
LVL 3

Author Comment

by:bobby6055
ID: 35500265
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
ID: 35501631
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
ID: 35502194
Thanks
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35714445
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
ID: 35715579
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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