Add design view query to ACCESS macro where condition change is required

I will try to explain this the best I can.  My data sets have periods (i.e 1-2014, 2-2014, 3-2014)
Each month, I append my table with a new period and then delete the oldest.

I created a macro to do most of the updates but I have not figured out how to either incorporate the delete query into the macro.
I would like to either automate the delete query where some formula could be added into the period field criteria and then the oldest would be deleted automatically (query then easily added to the macro)
OR modify the macro to open the delete query in design, then ask for date criteria (oldest) then once changed, the macro will continue.  The delete query has additional queries that need run after this one.



Any ideas???

Thanks
vpopperAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
If you retain records for 13 months back from now, then all you need is to delete those older:

Delete *
From tblYourDashboardTable
Where DateDiff("m", CDate([Period]), Date()) > 13

However, Dale is right, it could be wiser to create a query (like the one you have that fills the table) which selects the most current 13 periods and not just one.

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
create a delete query with parameter.. see this link

http://www.fontstuff.com/access/acctut01.htm
0
 
Dale FyeCommented:
1.  Why are you deleting the oldest data?  Why not just retain it?  I rarely find a good reason for deleting historical data.

2.  Because of the structure of your [period] field, you might first try a query that selects the earliest value from that field, something like:

SELECT [Period], cDate([Period])
FROM yourTable
ORDER BY cDate([Period])

This should return all of your period values, with the earliest at the top.  If that is successful, then you could modify it to:

DELETE FROM yourTable
WHERE [Period] = Format(DMIN("cdate([Period]", "yourTable"), "m-yyyy")
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
vpopperAuthor Commented:
I am retaining the historical data in another table.  This data is being deleted simple for linkage to some EXCEL dashboards (rolling 13 months data in Graphs).

I will give that cdate - that formula makes sense to me.

Thanks
0
 
Dale FyeCommented:
That makes some sense, but I generally store all of that data in a single table, and then create a temporary table for the Excel dashboards.  That way, if you need to query a set of data, you don't have to merge some data from the historical data and other data from your current data.

You might also want to take a look at my article on using temptables in Access.
0
 
vpopperAuthor Commented:
Worked...thanks
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.