Using SQL sub-queries across multiple tables

Edit: I looked at the mysql tag and assumed you were using that database. You're not, you're using Access. This answer is mostly worthless. Sorry

You're close. You have a few related problems in your query. Gordon already flagged the extra semicolons.

First, instead of subqueries like

   (select count(accountnumber) 
    from ActivityTable 
    where Description like 'Outgoing Call%') 

use conditional aggregation like this

   SUM( IF(Description LIKE 'Outgoing Call%'), 1, 0)

It sums up a bunch of 1 values for rows with the matching criterion.

Second, you used FROM AccountTable m, ActivityTable a to identify the tables in your query. For one thing, that tells the database to use every possible pairwise combination of rows. That's far too many rows and will give wrong results. For another, it's called the "comma join" syntax and has been obsolete, seriously, since 1992.

You want to JOIN your two tables together so you get the Activity rows associated with each Account row. I guess each separate row in ActivityTable relates back to exactly one row in AccountTable. But that could be wrong. I think you want this.

FROM AccountTable m
JOIN ActivityTable a ON m.AccountNumber=a.AccountNumber

Third, I believe you want to do COUNT(DISTINCT m.AccountNumber) to get each case manager's number of accounts. And you want COUNT(*) for the activity count.

Putting it all together, I guess it looks like this, in the MySQL dialect of SQL. But I cannot test this because I don't have your setup. The VBA in your spreadsheet should pass it through to MySQL unchanged, but it might not. Microsoft's testers don't really care whether VBA works properly with non-Microsoft products.

SELECT m.CaseManager,
       SUM(m.Lead) AS 'Total Leads',
       COUNT(DISTINCT m.AccountNumber) AS 'Total Accounts',
       COUNT(*) AS 'Total Activity',
       SUM(IF(Description LIKE 'Outgoing Call%'), 1, 0) AS 'Outbound Calls',
       SUM(IF(Description LIKE 'Correspondence Sent%'), 1, 0) AS 'Correspondence Issued',
       SUM(IF(Description LIKE 'Credit%'), 1, 0) AS 'Credit Reviews'
  FROM AccountTable m
  JOIN ActivityTable a ON m.AccountNumber=a.AccountNumber
 GROUP BY m.CaseManager;

There's a significant amount of business logic in this aggregating query. SQL offers you a lot to learn. Don't give up!

Your trying to do a subquery inside a totals query. That is too complicated for me to figure out: instead consider adding a calculated variable to determine if a record is also a outgoing call and then letting the totals query sum that up to: assuming your access tables are structured like:

Outgoing Calls: IIf([ActivityTable].[Description] Like "Outgoing Call*",1,0)
Correspondence Sent: IIf([ActivityTable].[Description] Like "Correspondence Sent*",1,0)
Credit Reviews: IIf([ActivityTable].[Description] Like "Credit*",1,0)

here is the generated sql you can just paste into the sql pane if you have the corresponding tables

SELECT AccountTable.CaseManager, Sum(AccountTable.Lead) AS [total leads], Count(AccountTable.AccountNumber) AS [Total Accounts], Count(ActivityTable.AccountNumber) AS [Total Activity], Sum(IIf([ActivityTable].[Description] Like "Outgoing Call*",1,0)) AS [Outgoing Calls], Sum(IIf([ActivityTable].[Description] Like "Correspondence Sent*",1,0)) AS [Correspondence Sent], Sum(IIf([ActivityTable].[Description] Like "Credit*",1,0)) AS [Credit Reviews]
FROM AccountTable INNER JOIN ActivityTable ON AccountTable.AccountNumber = ActivityTable.AccountNumber
GROUP BY AccountTable.CaseManager;

note: for this example to work AccountTable.Accountnumber has to be set as unique or access will not allow the relationship as AccountNumber is not set up as the primary key.