Month + 1

[Epona]

I am busy putting together a mini MRP.



I need to generate a table that provide a monthly projected factory demand. The table will not be static as it will change for each month it is run rolling forward.

My access skills are a bit rusty and I am feeling like a bit of a dunce. I have generated a query that selets the 1st month of the period and another that returns all the data for the dates greater than the first period. I now need to summarise it into "monthly" coloumns.

Was thinking of using the month + 1 concept but cant seem to get it to work. any bright ideas?

[June7]

Could you describe the 'month + 1' concept? Not familiar with that.

Have you tried a crosstab query?

[Epona]

I cant use a cross tb as there are subsequent calculations that have to be done on the monthly data and the colomn names will change every time the report is run in a new month or if additional forecast data is added.

Month+1 that I am using is essentialy to look at the month in the filter table - that is always month 1 of the period and then return the data that matches to that month+1. The next column would then be the same but it would be +2. That way it will always roll forward irrespective of what is the starting month or which part of the financial year it is.

I have got it to work using DateAdd('m',1,[Demand M1a]![Mnth]) but it is very clumsy.

[June7]

Get it now, what you mean about the 'rolling forward'. You say you now have the query working? Clumsy it may be but does seem a reasonable approach to stabalize the output. I would need sample data, or the project itself, to analyze and explore alternatives. But probably won't be anything better.

[Epona]

Thanks, as you say, although clumsy it is stable and that is what matters at this point.