Access: DCount Code help Part 2

[SusanK4305]

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:

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!

[MMcCarthy]

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

[SusanK4305]

SQL:

SELECT [tblAction Log].[Action Type], Count([tblAction Log].[Action Date]) AS [CountOfAction Date]
FROM [tblAction Log]
GROUP BY [tblAction Log].[Action Type];

[MMcCarthy]

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

[SusanK4305]

Sorry This is the SQL:

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

[MMcCarthy]

OK try this ...

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];
 

[Susan Kelly]

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

[MMcCarthy]

Sorry I had an extra semi colon in the 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];

[SusanK4305]

it is also missing a " ) " some where here

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.

[SusanK4305]

ok I found where it goes.

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]; 

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

[MMcCarthy]

Sorry my fault, try this ...

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];

[NeoPa]

Try this :

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 :

WHERE    ([Action Type] <> 'Complete')

[SusanK4305]

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

[SusanK4305]

Thank you both