Query to count 2 columns and total

  • Thread starter Thread starter Nedlog
  • Start date Start date
N

Nedlog

Hi

I am having difficulty with generating a query in MS Access 2003 so I
hope i've come to the right place!

I have a table with the following format:

ProjectCode ProjectName LastMonthFund CurrentMonthFund
---------------------------------------------------------------------
FWP1976 Network upgrade Low High
FWP1211 Hardware acquisition High On Track
FWP1691 TRG On Track No Report
FWP1822 Quality Review No Report No Report
.....

I want to produce a count of the funding status for the last and
current months and also a total row at the bottom. I would like the
query to generate something like the following:

Fund Last Month Current Month
------------------------------------
Low 31 34
High 24 21
On Track 17 15
No Report 3 2
Total 75 72


Can you anyone help me with this.

Many thanks in advance,
D.
 
Let's try this...

1. Below query pulls your required count rows EXCEPT total

Select DistWithLastMonthCount.*,CurrentFund.CurrentMonthCount From
(Select Fund,LastfundCount from
(Select distinct LastMonthFund as Fund from TestTable
Union
Select distinct CurrentMonthFund as Fund from TestTable) DistFund
Left Join (Select LastMonthFund,Count(*) as LastFundCount from
TestTable Group by LastMonthFund) as LastFund On LastFund.LastMonthFund
= DistFund.Fund) DistWithLastMonthCount
Left Join (Select CurrentMonthFund,Count(*) as CurrentMonthCount from
TestTable Group by CurrentMonthFund) as CurrentFund On
CurrentFund.CurrentMonthFund = DistWithLastMonthCount.Fund

2. Below query pulls ONLY total row

Select "Total" as Fund, Sum(LastMonthCount),Sum(CurrentMonthCount) From

(Select DistWithLastMonthCount.*,CurrentFund.CurrentMonthCount From
(Select Fund,LastMonthCount from
(Select distinct LastMonthFund as Fund from TestTable
Union
Select distinct CurrentMonthFund as Fund from TestTable) DistFund
Left Join (Select LastMonthFund,Count(*) as LastMonthCount from
TestTable Group by LastMonthFund) as LastFund On LastFund.LastMonthFund
= DistFund.Fund) DistWithLastMonthCount
Left Join (Select CurrentMonthFund,Count(*) as CurrentMonthCount from
TestTable Group by CurrentMonthFund) as CurrentFund On
CurrentFund.CurrentMonthFund = DistWithLastMonthCount.Fund)
LastCurrentMerged

3. This last query will MERGE both and pulls as you shown in your
requirement.

Select DistWithLastMonthCount.*,CurrentFund.CurrentMonthCount From
(Select Fund,LastfundCount from
(Select distinct LastMonthFund as Fund from TestTable
Union
Select distinct CurrentMonthFund as Fund from TestTable) DistFund
Left Join (Select LastMonthFund,Count(*) as LastFundCount from
TestTable Group by LastMonthFund) as LastFund On LastFund.LastMonthFund
= DistFund.Fund) DistWithLastMonthCount
Left Join (Select CurrentMonthFund,Count(*) as CurrentMonthCount from
TestTable Group by CurrentMonthFund) as CurrentFund On
CurrentFund.CurrentMonthFund = DistWithLastMonthCount.Fund
Union
Select "Total" as Fund, Sum(LastMonthCount),Sum(CurrentMonthCount) From

(Select DistWithLastMonthCount.*,CurrentFund.CurrentMonthCount From
(Select Fund,LastMonthCount from
(Select distinct LastMonthFund as Fund from TestTable
Union
Select distinct CurrentMonthFund as Fund from TestTable) DistFund
Left Join (Select LastMonthFund,Count(*) as LastMonthCount from
TestTable Group by LastMonthFund) as LastFund On LastFund.LastMonthFund
= DistFund.Fund) DistWithLastMonthCount
Left Join (Select CurrentMonthFund,Count(*) as CurrentMonthCount from
TestTable Group by CurrentMonthFund) as CurrentFund On
CurrentFund.CurrentMonthFund = DistWithLastMonthCount.Fund)
LastCurrentMerged

Hope this could help you

Thanks,
Saran.
 
Back
Top