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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
So, is the value in the matrix supposed to be a count by source and year?

That query might look like:

TRANSFORM Count([Source]) as Leads
SELECT Year([DateFieldName]) as LeadYear
FROM yourTableName
GROUP BY Year([DateFieldName])
PIVOT [Source]

To create this query in the query designer, you would:

1. create a new query
2. Add your table to the query designer
3. Drag the date field into the query grid once.  Change the field to look like Year([DateFieldName])
4. Drag the Source field to the query grid twice.
5. Change the query from a SELECT query to a Cross-Tab query.
6.  In the Crosstab row of the query grid, set:
     a.   the Year([DateFieldName]) to RowHeading
     b.   the first [Source] field to ColumnHeading
     c.  the 2nd [Source] field to Value, then change the value Row to Count
7.  Add a Descending sort to the Year([DateFieldName]) column
8.  Run the query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
snooflehammerAuthor Commented:
Outstanding answer. Thank you. Next time you're in Eumundi I'll buy you a beer :)
snooflehammerAuthor Commented:
This guy is the man
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

snooflehammerAuthor Commented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
Glad i could help
snooflehammerAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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;
snooflehammerAuthor Commented:
Ah! Sweet. Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.