Access: DCount Code help Part 2

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • SusanK4305
    New Member
    • Sep 2010
    • 88

    #1

    Access: DCount Code help Part 2

    Please note: Action Log (table name), Action Type(dropdown options w/ in Action Log), Complete (Criteria),and Action Date (Date time field w/in Action Log).

    As a Field Name I place this in my Query:

    Code:
    Expr1: DCount("*","Action Log","[Action Type] <> 'Complete' AND [Action Date] IS NOT NULL")
    And Expression in the Total type

    Now I need to make this Expr1 only work for the Action type Delayed.

    Example: Total 17
    Delayed = 5
    Dropped = 1
    New = 7
    Complete = 4

    What I get w/ the Expr1 is 13 for all. What I need is

    Example: Total 17
    Delayed = 1 (this should be 5(Delayed)-4 (Complete))
    Dropped = 1
    New = 7
    Complete = 4

    This will end up in a report. I posted that question as well. So if you need info on the end game you may wish to read that one too. Thank you!
    Last edited by NeoPa; Oct 6 '10, 11:44 PM. Reason: Please use the [code] tags provided.
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    Susan

    Can you change the view of the query to SQL and post the full SQL code for this query. Then explain which results are not showing correctly.

    At the moment I'm confused.

    Mary

    Comment

    • SusanK4305
      New Member
      • Sep 2010
      • 88

      #3
      SQL:
      Code:
      SELECT [tblAction Log].[Action Type], Count([tblAction Log].[Action Date]) AS [CountOfAction Date]
      FROM [tblAction Log]
      GROUP BY [tblAction Log].[Action Type];
      Last edited by NeoPa; Oct 6 '10, 11:46 PM. Reason: Please use the [code] tags provided.

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        Susan

        This doesn't tell me what's wrong. This query will give you a count of the different dates for each action type. What do you want it to do?

        Mary

        Comment

        • SusanK4305
          New Member
          • Sep 2010
          • 88

          #5
          Sorry This is the SQL:

          Code:
          SELECT [Action Log].[Action Type], Count([Action Log].[Action Date]) AS [CountOfAction Date], DCount("*","Action Log","[Action Type] <> 'Complete' AND [Action Date] IS NOT NULL") AS Expr1
          FROM [Action Log]
          GROUP BY [Action Log].[Action Type];
          What I get is 13. 17 (total)- 4 (Complete)= 13
          I need it to only subtract Complete from the Delayed total.

          Should look like this

          Example: Total 17
          Delayed = 1 (this should be 5(Delayed)-4 (Complete))
          Dropped = 1
          New = 7
          Complete = 4
          Last edited by NeoPa; Oct 6 '10, 11:47 PM. Reason: Please use the [code] tags provided.

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            OK try this ...

            Code:
            SELECT "Total", Count([Action Date]) AS [CountOfAction Date]
            FROM [Action Log]
            GROUP BY [Action Type];
            UNION 
            SELECT [Action Type], 
            IIf([Action Type]="Delayed",Count([Action Date])-DCount("[Action Date]","Action Log","[Action Type]='Complete'"),Count([Action Date]) AS [CountOfAction Date]
            FROM [Action Log]
            GROUP BY [Action Type];

            Comment

            • Susan Kelly

              #7
              No didn't work. "Syntax error in union query"

              Comment

              • MMcCarthy
                Recognized Expert MVP
                • Aug 2006
                • 14387

                #8
                Sorry I had an extra semi colon in the code.

                Code:
                SELECT "Total", Count([Action Date]) AS [CountOfAction Date]
                FROM [Action Log]
                GROUP BY [Action Type]
                UNION 
                SELECT [Action Type], 
                IIf([Action Type]="Delayed",Count([Action Date])-DCount("[Action Date]","Action Log","[Action Type]='Complete'"),Count([Action Date]) AS [CountOfAction Date]
                FROM [Action Log]
                GROUP BY [Action Type];

                Comment

                • SusanK4305
                  New Member
                  • Sep 2010
                  • 88

                  #9
                  it is also missing a " ) " some where here
                  Code:
                  IIf([Action Type]="Delayed",Count([Action Date])-DCount("[Action Date]","Action Log","[Action Type]='Complete'"),Count([Action Date]) AS [CountOfAction Date] 
                  FROM [Action Log] 
                  GROUP BY [Action Type];
                  and my self not great at reading this kind of stuff.... not sure where, but I will try some places I think it goes and let you know.
                  Last edited by Frinavale; Oct 7 '10, 05:39 PM. Reason: Please post code in [code] ... [/code] tags. Added code tags

                  Comment

                  • SusanK4305
                    New Member
                    • Sep 2010
                    • 88

                    #10
                    ok I found where it goes.
                    Code:
                    SELECT "Total", Count([Action Date]) AS [CountOfAction Date] 
                    FROM [Action Log] 
                    GROUP BY [Action Type] 
                    UNION  
                    SELECT [Action Type],  
                    IIf([Action Type]="Delayed",Count([Action Date])-DCount("[Action Date]","Action Log","[Action Type]='Complete'"),Count([Action Date])[B])[/B] AS [CountOfAction Date] 
                    FROM [Action Log] 
                    GROUP BY [Action Type];

                    This is what I get: Why do I have all the " at the end "Toatl"?

                    Expr1000 CountOfAction Date
                    0
                    Delayed 0
                    Discontinued 1
                    E-Qip Initiation 3
                    New Security Packet 9
                    Other 2
                    Complete 6
                    Total 0
                    Total 1
                    Total 2
                    Total 3
                    Total 6
                    Total 9
                    Last edited by MMcCarthy; Oct 8 '10, 07:59 AM. Reason: Adding Code tags

                    Comment

                    • MMcCarthy
                      Recognized Expert MVP
                      • Aug 2006
                      • 14387

                      #11
                      Sorry my fault, try this ...

                      Code:
                      SELECT "Total" AS [Action Type], Count([Action Date]) AS [CountOfAction Date] 
                      FROM [Action Log] 
                      GROUP BY "Total" 
                      UNION  
                      SELECT [Action Type],  
                      IIf([Action Type]="Delayed",Count([Action Date])-DCount("[Action Date]","Action Log","[Action Type]='Complete'"),Count([Action Date])) AS [CountOfAction Date] 
                      FROM [Action Log] 
                      GROUP BY [Action Type];

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32669

                        #12
                        Try this :
                        Code:
                        SELECT   'Total' AS [Action Type]
                               , Count([Action Date]) AS [CountOfAction Date]
                        FROM     [Action Log]
                        UNION ALL
                        SELECT   [Action Type]
                               , Count(*) - 
                                 IIf([Action Type] = 'Delayed'
                                   , DCount('[Action Date]'
                                          , '[Action Log]'
                                          , '[Action Type] = ''Complete''')
                                   , 0)
                        FROM     [Action Log]
                        GROUP BY [Action Type]
                        I notice the total in your example is still 17 even though the items displayed that make up the total are missing the equivalent of the Complete items. Should you wish this to be reflected in your total (13 rather than 17 in your example) then add the following line after line #3 :
                        Code:
                        WHERE    ([Action Type] <> 'Complete')

                        Comment

                        • SusanK4305
                          New Member
                          • Sep 2010
                          • 88

                          #13
                          Both McCarty and NeoPA work the same the only diff. is
                          WHERE ([Action Type] <> 'Complete') and how it shows.

                          Comment

                          • SusanK4305
                            New Member
                            • Sep 2010
                            • 88

                            #14
                            Thank you both

                            Comment

                            Working...