totals

[Guest]

I have a table with following fields Ref.no, Name, region, Amount, Legacy
amount, Acorn ,etc

what I want is total of amount and legacy amount by regions

How can I do that?

 

[Arvin Meyer [MVP]]

I have a table with following fields Ref.no, Name, region, Amount, Legacy
amount, Acorn ,etc

what I want is total of amount and legacy amount by regions

How can I do that?

With an aggregate or "Totals" query:

SELECT MyTable.region, Sum(MyTable.[Amount]) AS Total, Sum(MyTable.[Legacy
Amount]) AS LegacyTotal
FROM MyTable
GROUP BY MyTable.region;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks it works

just one more... I have another field called legacy pledge which is a y/n

I want a count of field (legacy pledge) of all where its "Y"
How do I amend the sql below to incorporate it...

SELECT [Total merged supporters].Region, Sum([Total merged
supporters].Amount) AS Total, Sum([Total merged supporters].[Legacy total])
AS LegacyTotal, Avg([Total merged supporters].[Legacy total]) AS [Legacy
average], Count([Total merged supporters].[Legacy Pledge]) AS [Legacy
Pledgers]
FROM [Total merged supporters]
GROUP BY [Total merged supporters].Region;

I have a table with following fields Ref.no, Name, region, Amount, Legacy
amount, Acorn ,etc

what I want is total of amount and legacy amount by regions

How can I do that?

With an aggregate or "Totals" query:

SELECT MyTable.region, Sum(MyTable.[Amount]) AS Total, Sum(MyTable.[Legacy
Amount]) AS LegacyTotal
FROM MyTable
GROUP BY MyTable.region;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

That one's really easy. a boolean field (Y/N) has values of True (-1) and
False (0). So if you add up the -1s and multiple them by -1 you'll get a
positive result. Just add the following expression to your quey's select
statment:

Sum([legacy pledge])*-1 AS [Legacy Pledge Total]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Thanks it works

just one more... I have another field called legacy pledge which is a y/n

I want a count of field (legacy pledge) of all where its "Y"
How do I amend the sql below to incorporate it...

SELECT [Total merged supporters].Region, Sum([Total merged
supporters].Amount) AS Total, Sum([Total merged supporters].[Legacy total])
AS LegacyTotal, Avg([Total merged supporters].[Legacy total]) AS [Legacy
average], Count([Total merged supporters].[Legacy Pledge]) AS [Legacy
Pledgers]
FROM [Total merged supporters]
GROUP BY [Total merged supporters].Region;

I have a table with following fields Ref.no, Name, region, Amount, Legacy
amount, Acorn ,etc

what I want is total of amount and legacy amount by regions

How can I do that?

With an aggregate or "Totals" query:

SELECT MyTable.region, Sum(MyTable.[Amount]) AS Total, Sum(MyTable.[Legacy
Amount]) AS LegacyTotal
FROM MyTable
GROUP BY MyTable.region;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access