Create crosstab Query for each FY

[smoothtracks]

Hello Everyone,



I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form. Thanks to any and all who can help.

 

[plog]

I need a crosstab Query to count the number of records for each FY.

Why a cross-tab query? This sounds like a job for a simple SELECT query.

What have you tried? Where are you stuck?

 

[smoothtracks]

Perhaps you’re right; I’m really not sure what the best approach is. I’ve never had to track anything by FY before. I have created simple and cross-tab queries before, but this seems to be so much more complicated.

 

[plog]

Post some sample data. What the data looks like going into this query and then what it should look like coming out. Be sure to use table and field names and use this format:

TableNameHere
Field1Name, Field2Name, Field3Name...
Accounting, 12/31/2011, 197
Exec, 7/8/2012, 51
Sales, 6/6/2009, 411

 

[smoothtracks]

Thanks for the reply, it really means a lot.


Ok, I hope this makes sense. I have a table with a field named “SARC Notified”. That field has a date format as such: 12-Sep-13. I need to show on a report how many reports/records we had from the previous FY. Would a simple query or a cross-tab work better and how would I go about it.

 

[plog]

This query would count the records by month/year:

SELECT Month([SARC Notified]) AS ReportMonth, Year([SARC Notified]) AS ReportYear, COUNT([SARC NOTIFIED]) AS RecordCount
FROM YourTableNameHere
GROUP BY Month([SARC Notified]), Year([SARC Notified]);

If you search this forum for 'Fiscal Year' you should find some results for writing code to determine which fiscal year a date falls in. I know I've posted some before.

 

[smoothtracks]

I thank you for your efforts; however, that code doesn't work for me. I need a query that will generate one number (Total) that represents how many records I have from 1-Oct-12 to 30-Sep-13 (Army FY13) and will continue to do FY14 and so on.

Whatever help you can give will be much appreciated. I am at my wits end with this one.

 

[plog]

This post has a fiscal year code I wrote:

http://www.access-programmers.co.uk/forums/showthread.php?t=209655&highlight=fiscal

You need to adapt that to a new query similar to the one I posted above. The code will have to be configured to work with the month of your Fiscal Year begins on and the query will have to be configured to replace the Month() and Year() functions with that code.

Try it, if it doesn't work, post back what you are trying.