Displaying summary level results from a query

[Scott K]

I have a query that returns accounts and balances. I want the query results
to return each account once, but total the activity of the accounts appears
multiple times in the database. An example of the data currently returned by
teh query is below:

331100 Accrued Payroll (109,636.11)
331100 Accrued Payroll 2,163.67
331100 Accrued Payroll (12,608.11)
331100 Accrued Payroll (1,577.99)
331100 Accrued Payroll (1,510.71)
331100 Accrued Payroll (14,225.05)
331100 Accrued Payroll 430,000.00

How can I get the query to return one line with a total instead of 7 lines
in this example?

Thanks!

 

[Amy Blankenship]

I have a query that returns accounts and balances. I want the query
results
to return each account once, but total the activity of the accounts
appears
multiple times in the database. An example of the data currently returned
by
teh query is below:

331100 Accrued Payroll (109,636.11)
331100 Accrued Payroll 2,163.67
331100 Accrued Payroll (12,608.11)
331100 Accrued Payroll (1,577.99)
331100 Accrued Payroll (1,510.71)
331100 Accrued Payroll (14,225.05)
331100 Accrued Payroll 430,000.00

How can I get the query to return one line with a total instead of 7 lines
in this example?

If you look at the toolbar when you're in the query builder, you should see
a sum button. It kind of looks like a funny capital "E". If you click
that, then you can select Sum for the field you want to total.

HTH;

Amy

 

[Scott K]

Hi Amy,

I have done that already. All fields are set to Group By except for Amount
which is set to Sum. Any other ideas?

Scott

 

[Amy Blankenship]

Hi Amy,

I have done that already. All fields are set to Group By except for
Amount
which is set to Sum. Any other ideas?

It looks like there's some other factor in play that is causing these to be
considered separate rows, or is interfering with Access's ability to sum the
rows. Where are the parentheses coming from in some of the rows?

 

[Conan Kelly]

Scott K,

Please post your SQL statement.

To do what you are describing, follow Amy's advice, but make sure there are
only 2 fields in the query: your account field and your balance field. Set
your account field to "Group By" and set your balance field to "Sum". That
should return what you are asking for. Any other column/field you add to
the query will increase the number of records in your results.

HTH,

Conan

 

[Scott K]

The parentheses are simply Excel formatting. The raw data looks like this:

331100 Accrued Payroll -109636.11
331100 Accrued Payroll 2163.67
331100 Accrued Payroll -12608.11
331100 Accrued Payroll -1577.99
331100 Accrued Payroll -1510.71
331100 Accrued Payroll -14225.05
331100 Accrued Payroll 430000

I have a number of fields in the query itself, but only these three are set
to Show. Could one of my field criteria be driving this problem?

Thanks,

Scott

 

[Scott K]

Hi Conan,

The SQL statement is as follows:

SELECT dbo_vData.AccountX AS [HFM Account #], Sum(dbo_vData.Amount) AS
SumOfAmount INTO [A-12 Test Results]
FROM ((dbo_tPOVPartition INNER JOIN (dbo_tPOVCategory INNER JOIN dbo_vData
ON dbo_tPOVCategory.CatKey = dbo_vData.CatKey) ON
dbo_tPOVPartition.PartitionKey = dbo_vData.PartitionKey) INNER JOIN
dbo_tStructPartitionHierarchy ON dbo_tPOVPartition.PartitionKey =
dbo_tStructPartitionHierarchy.LocationID) INNER JOIN dbo_tDimAccount ON
dbo_vData.AccountX = dbo_tDimAccount.TargAcctKey
GROUP BY dbo_vData.AccountX, dbo_tPOVPartition.PartName,
dbo_vData.CalcAcctType, dbo_vData.PeriodKey, dbo_tPOVCategory.CatKey
HAVING (((dbo_vData.AccountX)>="331100" And (dbo_vData.AccountX)<="339400")
AND ((dbo_tPOVPartition.PartName)="FDM_086000" Or
(dbo_tPOVPartition.PartName)="FDM_110000" Or
(dbo_tPOVPartition.PartName)="FDM_110010" Or
(dbo_tPOVPartition.PartName)="FDM_110020" Or
(dbo_tPOVPartition.PartName)="FDM_110030" Or
(dbo_tPOVPartition.PartName)="FDM_110040" Or
(dbo_tPOVPartition.PartName)="FDM_110050" Or
(dbo_tPOVPartition.PartName)="FDM_110070") AND ((dbo_vData.CalcAcctType)>0)
AND ((dbo_vData.PeriodKey)=#1/31/2008#) AND ((dbo_tPOVCategory.CatKey)=13))
ORDER BY dbo_tPOVPartition.PartName;

Thanks for your help.

Scott

 

[Conan Kelly]

Scott K,

Your "GROUP BY" clause has 5 fields/columns in it, yet you are only
selecting 2 (actually from what you asked, you are really only selecting 1
and performing a SUM aggregate function on the other).

If it is possible, I would change...

GROUP BY
dbo_vData.AccountX,
dbo_tPOVPartition.PartName,
dbo_vData.CalcAcctType,
dbo_vData.PeriodKey,
dbo_tPOVCategory.CatKey

....to...

GROUP BY
dbo_vData.AccountX

Will that cause problems with your query?

HTH,

Conan

Hi Conan,

The SQL statement is as follows:

SELECT dbo_vData.AccountX AS [HFM Account #], Sum(dbo_vData.Amount) AS
SumOfAmount INTO [A-12 Test Results]
FROM ((dbo_tPOVPartition INNER JOIN (dbo_tPOVCategory INNER JOIN dbo_vData
ON dbo_tPOVCategory.CatKey = dbo_vData.CatKey) ON
dbo_tPOVPartition.PartitionKey = dbo_vData.PartitionKey) INNER JOIN
dbo_tStructPartitionHierarchy ON dbo_tPOVPartition.PartitionKey =
dbo_tStructPartitionHierarchy.LocationID) INNER JOIN dbo_tDimAccount ON
dbo_vData.AccountX = dbo_tDimAccount.TargAcctKey
GROUP BY dbo_vData.AccountX, dbo_tPOVPartition.PartName,
dbo_vData.CalcAcctType, dbo_vData.PeriodKey, dbo_tPOVCategory.CatKey
HAVING (((dbo_vData.AccountX)>="331100" And
(dbo_vData.AccountX)<="339400")
AND ((dbo_tPOVPartition.PartName)="FDM_086000" Or
(dbo_tPOVPartition.PartName)="FDM_110000" Or
(dbo_tPOVPartition.PartName)="FDM_110010" Or
(dbo_tPOVPartition.PartName)="FDM_110020" Or
(dbo_tPOVPartition.PartName)="FDM_110030" Or
(dbo_tPOVPartition.PartName)="FDM_110040" Or
(dbo_tPOVPartition.PartName)="FDM_110050" Or
(dbo_tPOVPartition.PartName)="FDM_110070") AND
((dbo_vData.CalcAcctType)>0)
AND ((dbo_vData.PeriodKey)=#1/31/2008#) AND
((dbo_tPOVCategory.CatKey)=13))
ORDER BY dbo_tPOVPartition.PartName;

Thanks for your help.

Scott

Scott K,

Please post your SQL statement.

To do what you are describing, follow Amy's advice, but make sure there
are
only 2 fields in the query: your account field and your balance field.
Set
your account field to "Group By" and set your balance field to "Sum".
That
should return what you are asking for. Any other column/field you add to
the query will increase the number of records in your results.

HTH,

Conan

 

[John Spencer]

Yes, the fields you are not displaying are causing the problem. IF you
are using those fields to just filter the data (apply criteria) - change
the GROUP BY to WHERE on those fields. That should fix the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================