Displaying summary level results from a query

S

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!
 
A

Amy Blankenship

Scott K said:
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?
Click to expand...

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
 
S

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
 
A

Amy Blankenship

Scott K said:
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?
Click to expand...

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?
 
C

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
 
S

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
 
S

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
 
C

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




Scott K said:
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 said:
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
Click to expand...
Click to expand...
 
J

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
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summing multiple input records into one output rcd query 2
Level Organized DB - Is Query the Answer? 1
Query producing different results each time it is run 2
Limiting multiple results in query 7
Help with a Monthly Summary Report 3
Summary query running slow 5
Data with no relationship, fall-out data from query 2
Summary Query based on First Occurrence 12

Top