Sum certain fields based on a date field in a query

[cmorten]

So I have a table that I created a query from and the fields that I pulled into that query are Name and each month of the year (i.e. July, August, etc.) and the year is a fiscal year beginning in July and ending in June I also created a calculated field that gives me the date in the three letter month format (i.e. "Mar") so for any day between 3/1/16 and 3/31/16 every row contains "Mar" in it and when the date changes to 4/1/2016 that field will contain "APR" and so on. My name field contains employee names and my month fields contain their monthly pay. I want to create a calculated field in that same query that says If the Date field says MAR then sum the July field thru February field and if it says APR then sum July thru March and so on. I have tried a similar formula that does not work, probably because it is to complex. As you can probably tell I have no idea what I am doing



IIf([Date]="AUG",[JUL],IIf([Date]="SEP",[JUL]+[AUG],IIf([Date]="OCT",[JUL]+[AUG]+[SEP],IIf([Date]="NOV",[JUL]+[AUG]+[SEP]+[OCT],IIf([Date]="DEC",[JUL]+[AUG]+[SEP]+[OCT]+[NOV],IIf([Date]="JAN",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC],IIf([Date]="FEB",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC]+[JAN],IIf([Date]="MAR",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC]+[JAN]+[FEB],IIf([Date]="APR",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC]+[JAN]+[FEB]+[MAR],IIf([Date]="MAY",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC]+[JAN]+[FEB]+[MAR]+[APR],IIf([Date]="JUN",[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC]+[JAN]+[FEB]+[MAR]+[APR]+[MAY],[JUL:JUN])))))))))))

[Micron]

Not sure if you will appreciate this sort of 'help'. You really should read up on database normalization before you go further. People info should be in one table, pay info in another. In the pay table, the field should be named like "PayMth" or Mth if you don't like that. This data should be in rows, not columns. Which brings me to reserved words. Month is a reserved word you should not use in a table, and there is a fairly large list of those kinds of words. Lastly, storing calculated values in a table should be avoided except in rare cases, and this is not one of them. Such calculations should be done in forms or reports. It would be well worth your while to do as I advise, otherwise you will likely find yourself sinking deeper and deeper into the swamp of bad database design. Here's a reserved word link:
http://allenbrowne.com/AppIssueBadWord.html
Google database normalization

[cmorten]

I absolutely appreciate it, I knew nothing of normalization and the 5 normal forms I always like learning something new. Here's the piece that I didn't mention earlier that might help. So on the first of every month I pull a download from our Oracle system and that specific download has the headings "Name", and each Month of the year plus a couple other fields like "index" and "fund", so this is how the data comes. I then just import that data into Access and that's it. I let the query do it's thing and than I have that query linked to some excel spreadsheets that contain some custom reports that are built from the query. I am not sure if normalization would help here because it seems that I would have to split the data up and then bring it back together which seems redundant. My reports use the data as is with just a couple of calculated fields that I use to do in excel but realized it may be more efficient to do in Access. However I am building another database that will be about our personnel and all of their employee information so normalization I think would be beneficial to that, so thanks and thanks for the quick reply.

[Micron]

Maybe an option for you is to simply link the spreadsheets as tables in Access.
If not, when you are importing the data you have the option of renaming your fields by using aliases in the query. If this will impact how it is dealt with later, you likely have to live with it the way it is. Take a look at the Totals query. You should be able to summarize by a month range using that and the DateAdd function to add x months to whatever the month is in the records. One could add a number to Date() but it needs to be expressed as a number of days. DateAdd allows you to deal in months.
http://www.techonthenet.com/access/functions/

[cmorten]

I think I figured it out. I used a date formula in the query adding a new field and put this in that field which returns the three letter month: ( Format(Now(),"mmm") ). Next I redid the formula on the next field that Sums the totals by year-to-date based on the current date. and this is the formula I used. It's huge and ugly but it worked and I was able to keep it within the limits of the string. It says if The Date field I created says "AUG" then sum the July Field and if it is "SEP" then Sum July and August and it worked. I imagine there is a cleaner way to do it but I'll keep learning and figure it out. Thanks, for the help

YTD: IIf([Date]="Aug",[SumOfJUL],IIf([Date]="Sep",[SumOfJUL]+[SumOfAUG],IIf([Date]="Oct",[SumOfJUL]+[SumOfAUG]+[SumOfSEP],IIf([Date]="NOV",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT],IIf([Date]="Dec",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV],IIf([Date]="Jan",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC],IIf([Date]="Feb",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC]+[SumOfJAN],IIf([Date]="Mar",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC]+[SumOfJAN]+[SumOfFEB],IIf([Date]="Apr",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC]+[SumOfJAN]+[SumOfFEB]+[SumOfMAR],IIf([Date]="May",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC]+[SumOfJAN]+[SumOfFEB]+[SumOfMAR]+[SumOfAPR],IIf([Date]="Jun",[SumOfJUL]+[SumOfAUG]+[SumOfSEP]+[SumOfOCT]+[SumOfNOV]+[SumOfDEC]+[SumOfJAN]+[SumOfFEB]+[SumOfMAR]+[SumOfAPR]+[SumOfMAY],[1st Qtr]+[2nd Qtr]+[3rd Qtr]+[4th Qtr])))))))))))

[Micron]

I imagine there is a cleaner way to do it

Like maybe

 WHERE someDate BETWEEN DatePart("m", MyDate) AND DateAdd("m", 6, MyDate)

6 being the number of months I want to span, someDate coming from say, a totals query. Not sure based on the limited information, but it could be that simple.