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???

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
create a delete query with parameter.. see this link
Dale FyeOwner, Developing Solutions LLCCommented:
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:

WHERE [Period] = Format(DMIN("cdate([Period]", "yourTable"), "m-yyyy")
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
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.
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vpopperAuthor Commented:
Gustav BrockCIOCommented:
You are welcome!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.