November 19, 2018 at 9:06 am
I have an Access front end database with a SQL Server front end. I need to produce in Access a report that shows figures and a graph of the revenue of the last 10 years.
In SQL Server I can easily find the figure for one year but i need a query that will serve as a datasource showing all 10 years. Any ideas?
SELECT sum([amount]) as '2014'
FROM [BCC_DB].[dbo].[Payments] where [transactionDate]
between '2014-01-01 00:00:00.000' and
'2014-12-31 00:00:00.000'
November 19, 2018 at 11:38 am
briancampbellmcad - Monday, November 19, 2018 9:06 AMI have an Access front end database with a SQL Server front end. I need to produce in Access a report that shows figures and a graph of the revenue of the last 10 years.
In SQL Server I can easily find the figure for one year but i need a query that will serve as a datasource showing all 10 years. Any ideas?
SELECT sum([amount]) as '2014'
FROM [BCC_DB].[dbo].[Payments] where [transactionDate]
between '2014-01-01 00:00:00.000' and
'2014-12-31 00:00:00.000'
I found this works :SELECT sum([amount]), year([transactionDate])
FROM [BCC_DB].[dbo].[Payments]
GROUP BY year([transactionDate])
WITH CUBE
November 19, 2018 at 11:40 am
briancampbellmcad - Monday, November 19, 2018 9:06 AMI have an Access front end database with a SQL Server front end. I need to produce in Access a report that shows figures and a graph of the revenue of the last 10 years.
In SQL Server I can easily find the figure for one year but i need a query that will serve as a datasource showing all 10 years. Any ideas?
SELECT sum([amount]) as '2014'
FROM [BCC_DB].[dbo].[Payments] where [transactionDate]
between '2014-01-01 00:00:00.000' and
'2014-12-31 00:00:00.000'
SQL Server back end, I presume – as it doesn't work as a front end.
You need to switch your logic around a little. Maybe start with something like this (untested):
SELECT Yr = YEAR(p.transactionDate), Total = SUM(p.Amount)
FROM [BCC_DB].[dbo].[Payments] p
WHERE p.transactionDate >= DATEFROMPARTS(YEAR(GETDATE())-10,1,1)
GROUP BY YEAR(p.transactionDate)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply