Creating a Macro to run multiple Queries - Help

[lwilt]

When making a new macro to run a list of queries, let's say 5 queries for example, I pick the action to be OpenQuery but I can't seem to figure out what I need to pick for the drop downs for View and Data mode so that the Query will just run to the make table I have in design mode.

Is choosing OpenQuery correct? If not what action should I use? And what do I pick for the drop downs?

 

[Joe4]

You only need to pick the Query name argument, and leave the rest "as-is".
Action queries (like Make Table, Append, Update, Delete) will just run (and not physically open up and be displayed).
Select queries would open up and be displayed in Access.

 

[lwilt]

if all of my Queries are using certain date ranges(for example I run this every month and each time the date ranges for all the queries move by one month but they are not all the same range) is there a way a macro can be set up to run and will update the date ranges?

 

[Joe4]

I would create a one record table where you store these dates, and then link the table into your queries in order to set the date criteria. Then all you need to do is update this one record and it will affect all queries.

I use this method a lot. I usually create a Form where I can update these values, and change the Properties on this Form to not allow any record additions or deletions. This ensure that this table will always have exactly one record in it.

 

[lwilt]

do you have a database I could see where you did this?

 

[Joe4]

Can you post the SQL code of one of these queries that has the date criteria in it (just change your Query to SQL View and copy and paste the code here)?
If you can, I might be able to help you incorporate something like this later tonight.

 

[lwilt]

INSERT INTO [RFM Test Table] ( [RFM Code], Cust_NO, [Last Order], [Avg Order], [Number of Orders], [First Contact Method] )
SELECT "A1B" AS [RFM Code], [RFM Customers and Orders].Cust_NO, Last([RFM Customers and Orders].[Order Date]) AS [Last Order], Avg([RFM Customers and Orders].Sales) AS [Avg Order], Count([RFM Customers and Orders].[Order Number]) AS [Number of Orders], First([RFM Customers and Orders].[First touch]) AS [First Contact Method]
FROM [RFM Customers and Orders]
GROUP BY "A1B", [RFM Customers and Orders].Cust_NO
HAVING (((Last([RFM Customers and Orders].[Order Date]))>=#6/1/2014# And (Last([RFM Customers and Orders].[Order Date]))<#12/1/2014#) AND ((Avg([RFM Customers and Orders].Sales))<220) AND ((Count([RFM Customers and Orders].[Order Number]))>=16) AND ((First([RFM Customers and Orders].[First touch]))<>"Internet" And (First([RFM Customers and Orders].[First touch]))<>"WEB"));


There would be 7 different date ranges that would be used throughout the 50-70 queries so doing a macro and being able to update the dates would really speed this up

 

[lwilt]

Also....is there a limit to the number of queries that can be put in one macro?

 

[Joe4]

So, let's say that you have a one record table named "GlobalSettings" which the following fields: StartDate, EndDate
Then if you populate those values, you could replace the following section of your query:

...FROM [RFM Customers and Orders]
GROUP BY "A1B", [RFM Customers and Orders].Cust_NO
HAVING (((Last([RFM Customers and Orders].[Order Date]))>=#6/1/2014# And (Last([RFM Customers and Orders].[Order Date]))<#12/1/2014#)...

with

...FROM [RFM Customers and Orders], [GlobalSettings]
GROUP BY "A1B", [RFM Customers and Orders].Cust_NO
HAVING (((Last([RFM Customers and Orders].[Order Date]))>=[GlobalSettings].[StartDate] And (Last([RFM Customers and Orders].[Order Date]))<[GlobalSettings].[EndDate]) ...

 

[lwilt]

could you copy that into the whole piece of SQL I posted to I could see how the whole thing would look....when I try to insert that section it's not copying in correctly