asked on Acccess CrossTab Query?
Not sure if this should be a crosstab query or not, but I can't make it work.
I have extracted some data from a table which contains two values. One is the source of the business, we'll call that field SOURCE, the other is the date the enquiry was taken. We'll call that DATE.
There are around 17 different sources of business.
The object of the exercise is to create a table where the columns are the SOURCE value and the rows are the DATE value.
Each row of DATE information has to be collated by year, so the first row is all leads taken in 2015 by source of business, the next row is 2014, etc..
How to create such a query?
I have extracted some data from a table which contains two values. One is the source of the business, we'll call that field SOURCE, the other is the date the enquiry was taken. We'll call that DATE.
There are around 17 different sources of business.
The object of the exercise is to create a table where the columns are the SOURCE value and the rows are the DATE value.
Each row of DATE information has to be collated by year, so the first row is all leads taken in 2015 by source of business, the next row is 2014, etc..
How to create such a query?
Microsoft Access
Last Comment
snooflehammer
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Outstanding answer. Thank you. Next time you're in Eumundi I'll buy you a beer :)
ASKER
This guy is the man
ASKER
Just FYI, I had a separate table with the sources and was just using the ID in the main table, so my SQL statement looked like this:
TRANSFORM Count(tblSources.Source) AS CountOfSource
SELECT Year([DateTaken]) AS [Year]
FROM BiilsSourceOfLeads INNER JOIN tblSources ON BiilsSourceOfLeads.Source = tblSources.SourceID
GROUP BY Year([DateTaken])
ORDER BY Year([DateTaken]) DESC
PIVOT tblSources.Source;
Thanks again
TRANSFORM Count(tblSources.Source) AS CountOfSource
SELECT Year([DateTaken]) AS [Year]
FROM BiilsSourceOfLeads INNER JOIN tblSources ON BiilsSourceOfLeads.Source = tblSources.SourceID
GROUP BY Year([DateTaken])
ORDER BY Year([DateTaken]) DESC
PIVOT tblSources.Source;
Thanks again
Glad i could help
ASKER
Now the client wants to get the results by financial year (July 1 - June 30) instead of calendar year. I'll post that as a new question and notify you when I do so.
It is just a matter of transforming the DateTaken field by adding or subtracting the appropriate number of months using the DateAdd( ) function.
If for example the financial year of 2015 actually began on 1 Oct, 2014, then you would do something like:
TRANSFORM Count(tblSources.Source) AS CountOfSource
SELECT Year(DateAdd("m", 3, [DateTaken])) AS [Year]
FROM BiilsSourceOfLeads
INNER JOIN tblSources ON BiilsSourceOfLeads.Source = tblSources.SourceID
GROUP BY Year(DateAdd("m", 3, [DateTaken]))
ORDER BY Year(DateAdd("m", 3, [DateTaken])) DESC
PIVOT tblSources.Source;
If for example the financial year of 2015 actually began on 1 Oct, 2014, then you would do something like:
TRANSFORM Count(tblSources.Source) AS CountOfSource
SELECT Year(DateAdd("m", 3, [DateTaken])) AS [Year]
FROM BiilsSourceOfLeads
INNER JOIN tblSources ON BiilsSourceOfLeads.Source = tblSources.SourceID
GROUP BY Year(DateAdd("m", 3, [DateTaken]))
ORDER BY Year(DateAdd("m", 3, [DateTaken])) DESC
PIVOT tblSources.Source;
ASKER
Ah! Sweet. Thank you