Dynamically update an append query

[clancy76]

Hi all - I am working with an append query that appends records to a forecasting table. The forecasting table has columns like "CurrentMonth +1" (which would represent March 2016 now that we are in Feb 2016, but next month it would represent April 2016 - essentially the data for the NEXT month gets placed in that column, whatever the current month is).

Here is my problem - I can create an append query this month that would populate the "CurrentMonth +1" field with March's data - but when March comes around, I'll have to recreate the query to make sure April's data gets inserted into that column. Is there any way I can dynamically update the query, so it always next month's data that gets inserted into the "CurrentMonth +1" field? I'm assuming that it would involve the "Now" function, I'm just not sure of the best way to use it in this particular scenario.



Appreciate the help, thanks.

[CJ_London]

one way would be to design your tables properly - Access is not Excel and tables are 'tall and thin' rather than excels 'short and wide' worksheets.

[ssanfu]

Adding to what Ajax said:

Your table design is common for people who use spreadsheets a lot. In fact, this is so common it is called "committing spreadsheet". (this was me.... )

"Committing spreasheet" is creating column names that are actually data points. A lot of times, people who are used to working with spreadsheets just assume databases should be configured the same way, but to take advantage of the relational aspects of a database, you have to turn that on its head. Another good example is budgeting. Many people create spreadsheets that have expenditure categories down the left column of a spreadsheet, and have months listed across the top, and they enter either estimated or actual expenses in the cells of the matrix. In a relational database, you would generally set this up as 3 columns (ExpenseType, ExpenseDate, and Amount). Then you can use queries to present the data in a wide variety of formats (including a crosstab query, which looks like a spreadsheet).

So instead of Feb 2016, Mar 2016 and Apr 2016 as field names, you would have
a field named (maybe) "forecastDate", and have records
Feb 2016
Mar 2016
Apr 2016


Another reason why a spreadsheet type design is bad, when you add a new field (ie currentmonth+1), you have to redesign your forms, queries, reports and possible code.



Here is a start on normalization: http://rogersaccessblog.blogspot.com...1_archive.html

[clancy76]

Thanks so much guys - I am indeed familiar with normalization(I don't think the database even meets first normal form standards), unfortunately its a really old legacy database that has myriad existing queries dependent on the current(terrible) table design. Will try to figure something out, may just end up using a make table query to create a temp table that actually makes some kind of sense. Thanks again for you help.