Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2014
    Posts
    3

    Unanswered: Count multiple colums from the table

    All,

    I want to count data from my table ( Table ) and to display result like a dashboard (Desired result ). To count this what is the better way to do it?
    do i need to write query for every column and then link to final column? i have totally 300 columns to count
    I am attaching Table & desired result images;
    Attached Thumbnails Attached Thumbnails Table.jpg   desired result.jpg  

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    It would be a single summation query that counts each field.
    In the query design, dbl-click the bar on the name of the table,and drag all fields down to the grid.
    Once it is made you never have to make it again.

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    OR...
    Code:
    Public Function BuildCountSqlFromTbl(ByVal pvQry)
    Dim i As Integer
    Dim sSql, sGrpBy, sFld
    Dim rst
    Dim Fld
    'SELECT tStates2Fix.ST, Count(tStates2Fix.State) AS CountOfState, Count(tStates2Fix.TerrID) AS CountOfTerrID
    'From tStates2Fix
    'GROUP BY tStates2Fix.ST;
    Set rst = CurrentDb.OpenRecordset(pvQry)
    i = 1
    For Each Fld In rst.Fields
      If i = 1 Then
         sGrpBy = " From [" & pvQry & "] Group by [" & Fld.Name & "]"
         sSql = "Select [" & Fld.Name & "],"
         I = I + 1
      End If
      
      sFld = sFld & " Count([" & Fld.Name & "]) as " & Fld.Name & "Cnt,"
    Next
    sFld = Left(sFld, Len(sFld) - 1)
    
    BuildCountSqlFromTbl = sSql & sFld & sGrpBy
    End Function

  4. #4
    Join Date
    Aug 2014
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    It would be a single summation query that counts each field.
    In the query design, dbl-click the bar on the name of the table,and drag all fields down to the grid.
    Once it is made you never have to make it again.
    Thanks for your reply and i need to count only the FALSE, so when i tried your idea i dont get the result. any suggestion

  5. #5
    Join Date
    Nov 2012
    Posts
    32
    Quote Originally Posted by prabhus View Post
    Thanks for your reply and i need to count only the FALSE, so when i tried your idea i dont get the result. any suggestion
    Hi Prabhus,

    for query building use the loop ranman256 suggested, but instead of count use this:

    SUM(CASE WHEN YourFieldName = FALSE THEN 1 ELSE 0 END)

    Simple workaround, yet effective.

    Kind regards,

    Marek

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Myshaak View Post
    SUM(CASE WHEN YourFieldName = FALSE THEN 1 ELSE 0 END)
    This is T-SQL. CASE WHEN does not exist in Access, use IIf() instead.

    More fundamentally, the description of your database denotes a total absence of normalization.
    Quote Originally Posted by prabhus View Post
    i have totally 300 columns to count
    Having 300 columns in a table is highly suspect. You do that in Excel, not in a RDBMS. Eek indeed !
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I was suspicious of the month has twelve columns......... just wondering if those columns are summarising or denoting the previous weeks or months.....

    incidentallyt you will not be able to sum 300 columns in one query... you will hit a hard limit within Access (IIRC its around 250 or so destination columns).
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Maybe it's just me, but the image that is supposed to be a Table looks to me to actually be a spreadsheet, especially with columns headers titled 'Effective Months' and A, B, C... ! This is sort of re-enforced by the a Table cannot have more than 255 Fields, whereas this one reportedly has 300!

    Am I wrong here?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •