Making a report or query that counts four columns by year, then sums those counts.

J FW used Ask the Experts™

I've been learning Access, and while I am very good with Excel, this is really difficult. I was able to make a query to show counts, but now I would like to total those rows. Here's what I have:

CountOfAPP1        CountOfAPP2      CountOfAPP3      CountOfAPP4      Expr1
80                            22                            5                            29                            2015
223                          34                            6                            47                            2016
60                            7                               2                            0                              2017

Here's what I would like to have:
2015          136
2016          310
2017          69

Also, how can I make that year column show a fiscal year instead of a calendar year? It starts August 1 and runs through July 31.

Thank you in advance!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

The expression to convert to fiscal year is:

IIf(YourDate < Dateserial(Year(yourdate), 8, 1), Year(yourdate), Year(Yourdate) -1)

It compares yourdate field to August 1 of the same year.  If the calculated date is less than Aug 1, sameyear, then it returns Year -1.  Otherwise it returns the year of yourdate.  You can use this expression in a query or in VBA or in the ControlSource of a control on a form or report.

Regarding the other question, what are you counting and why is the number fixed at 4?  Are you always counting the same 4?  Once you denormalize the recordset by counting four things this way, you are stuck and there is no good way to solve the problem.  If the 4 will be variable, there will be better ways to do this.  All you can do us to add the four columns with the hard-coded names.

Select IIf(YourDate < Dateserial(Year(yourdate), 8, 1), Year(yourdate), Year(Yourdate) -1) as FiscalYear, Nz(CountOfAPP1, 0) + Nz( CountOfAPP2, 0) + Nz(CountOfAPP3 , 0) + Nz(CountOfAPP4,0) As Amount
From YourTable


The four columns are for applications. People may submit one, two, three or four, and to different places. So if you have done three, you'll have the name in column one, two, and three. So there's a column for each. I wondered if that should be organized differently, but I can't see how.
Distinguished Expert 2017

You should always think twice (and then an additional 10 times at least) before you hard code unnecessary limits.  If your user decides in the future to allow 5, what are you going to have to change?

The best solution is to do the limit at the data entry but nowhere else.  Your query will sum the amounts as rows and then you will use a crosstab to pivot into columns.  The crosstab will automatically calculate the sum of all columns for each row.  You can use a multi-select listbox to choose the projects.

Select ProjectID, ProjectName, Sum(Amount) as SumAmount
From yourtable
Where Project In (567, 489, 6999, 4)
Group By ProjecID, ProjectNamet;

Save the totals query and then use the query wizard to create a crosstab of it.  The crosstab will show the actual ProjectIDs as column headers or it can show the project name.

You will need to build the In() clause with VBA since it cannot be passed as a parameter.  There are other ways to do this also.  I've attached a sample database that shows how to build an In() clause using a Multi-Select Listbox


Now I don't know. I am just starting, as this was an Excel spreadsheet, so if it needs to be set up differently, I can now while there are only about 800 records. So I can reenter the info if I need to. It just was kept in Excel as four columns, and then they would just cram the name of a fifth app into the fourth column after the name of the fourth app. It's dumb, I know. So if I need to set it up differently, how can I do that, while still keeping the names of each place they applied? I feel like most of my problems stem from this, like you say. I have three other areas where this same thing happens, like Acceptance, where they might get accepted by all four. I was thinking something with a drop down menu to choose where they applied (and got accepted), but then if I do four of those, it's just the same thing. Is there a way to rearrange that that makes sense?

I can set this up however I like, as long as I get all the info in there. I'm going to go poke around in your sample database for a bit - thank you so much for that.
Distinguished Expert 2017

It's time to step away from the keyboard and do some reading on normalization.  Start by understanding First Normal Form since most spreadsheets violate that.  Usually the spreadsheet will have one or more repeating groups.  The four projects are what we call a repeating group - 1 piece of data that occurs in an arbitrary fixed number of instances.  I's like saying you can't hire an employee if he has more than four children if your Payroll master file is set up with a repeating group to hold dependents rather than using a separate table.  The operative concept is that if you have more than one of something, you have MANY and MANY requires a separate table even if the business rule currently says that there will only ever be 4 ( or 9 or 30 or whatever).   When you go from an Excel spreadsheet to a real database application, the worksheet invariably becomes multiple tables.  You need to understand the data and understand normalization to get this correct.  If you post the spreadsheet with some valid data, I'll take a first cut at how I think it should be split if I can understand the sheet.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial