Displaying Dsum totals for different values

[adamlaing]

All of my data is stored by primary key Company and Year and each record is this table also has the fields UK, US, Europe, Asia, Australia (which are numerical). I need a query that will export data to excel with the DSum of each country and each of the past five years. I could do this individual region and year DSums, however this would be 5 countries x 5 years = 25 DSums. Is there a way to tell the query e.g. Display the DSum of the Region fields for the following range of data?

Ideally the query results would be the regions in the first column and the DSums of the corresponding years in the following columns. Does this make sense, if not I can try to clarify more.

Thanks for looking.

 

[Jon K]

You can do it with a series of two queries: a Union Query, followed by a Crosstab Query.

qryOne:-
SELECT [Year], "UK" as Region, UK as Data FROM [TableName]
UNION
SELECT [Year], "US", US FROM [TableName]
UNION
SELECT [Year], "Europe", Europe FROM [TableName]
UNION
SELECT [Year], "Asia", Asia FROM [TableName]
UNION
SELECT [Year], "Australia", Australia FROM [TableName];

qryTwo:-
TRANSFORM Sum([Data])
SELECT [Region], Sum([Data]) AS Total
FROM qryOne
GROUP BY [Region]
PIVOT [Year];


Run the second query.
.

 

[adamlaing]

Jon K thanks, those queries worked perfectly - exactly what I was trying to do and a great tool to have for the future.