Pull YTD and MTD from Table

[Sully38]

I have a table that contains currency data in monthly rows. I need to generate a query that will select just the current Month and sum it as MTD and also sum the previous months up to the current month as YTD.
Example Feb MTD would be Feb and Feb YTD would be Jan + Jeb. Is this possible within a query? or is this something that needs some VBA Coding.

 

[Norie]

Why not create expressions based on your date?

Year:Yearr([YourDate])

and

Month:Year([YourDate])

You could then use Totals queries and group on these to get the sum for each month/year.

You could also use the Date or Now function in the criteria.

 

[Sully38]

YTD and MTD Pulls

Sorry not sure I worded that question well, the data is saaved in monthly columns so if someone grabs June I need to sum the June Column as MTD and I need to sum the Jan-June column as YTD. There is no year to select on either.

 

[Norie]

What fields do you actually have?

Is it just a month field? What type of field is it? Date/Text?

 

[Sully38]

Jan - Dec are currency data

 

[Norie]

I'm sorry I don't think I'm understanding 100%

Is there a field indicating month?

 

[Andrew Fergus]

Hi Sully
Apologies for butting in - I answered this question in your other post. In case that post gets deleted (as a double up) then the contents of my reply in that thread follow :

Calculating MTD and YTD figures can definitely be done in a query. You don't need VBA to do this. Have a read through this post where Brian and I go through a whole variety of reporting MTD and YTD for this year and last year.

HTH, Andrew.

 

[Sully38]

Ahh yes that would work if......

I thank everyone for their help and hopefully I didnt cause much confusion, but my frustration was at all time high yesterday...

Basically the table layout is Comp. Name, Match,NewPoint,Jan,Feb,Mar,etc...through Dec. ( All the month columns contain the currency these companies spent in those months)

My solution was to generate a simple Case statement that used SQL to generate a Temp table with just the data I needed..The owner of this Db hates code I enjoyed the glazed over look when I explaining Select Case to him...:

strmessage = InputBox("Please Enter Month Number")
Select Case strmessage
**Feb Example**

Case 2
strSql = "SELECT [Budget].Match, [Budget].NewPoint, [Budget].February AS [MTD Budget], [Budget].[January] + [February] AS [YTD Budget] INTO [ Budget - Proc 4] From [Budget]"

I am getting perfectly balanced results. Not sure this is greatest coding in the world but it seems stable.

Thank You for all the Help

 

[Sully38]

BTW..

BTW..
I requested date field many moons ago on this table, but was told it wasn't needed and it would only be "noise" on the table.. It's funny it seems every request that comes down the pike would be easier to do with a date field...

 

[Andrew Fergus]

Have you considered creating a semi-date table in the background? The table would have the date in long format (e.g. January etc per your existing data) and a second column of month number (i.e. 1 to 12). By joining this table to your existing structure, you can then use the month numbers to make your life easier (for MTD & YTD etc.), in the absence of a date field.
HTH, Andrew.