• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Automate make table query with different criterias

Hi Experts,

I have a make make query which I want to cut in four different ways using an equal to criteria based on one field. So for example, fieldname1 equals 'Finance' then the second cut would be 'HR'. Rather than create four different make table queries I would just like to have a macro that can run through the 4 criterias and make the four cuts into another database (into tables).

I need to run the macro off a query because that in itself is made of 2 joined tables.

A pseudo code example would be nice.


  • 2
1 Solution
LucasMS Dynamics DeveloperCommented:
i would put the search criteria in a table then via vba code wrote a function that would loop through the table and execute the make table query with the criteria, and in the macro i would just call the "run code" function to run it.

if you don't want to go that route, then put the criteria in a table, and inside the macro perform a dlookup (if you know for sure that the criteria will not change) and execute the make table 4 times.

you would have:  if you have access 2007 then you can have something like this:

dlookup ... hr
openquery maketable
dlookup ... finance
openquery maketable
.. and so on..
If I understand the question...  The end result is 4 seperate tables 1 for the Finance Result, 1 for the Hr result etc...

 I would write a module that defines the BASE part of the sql query (you can make this easy by creating the first query in the query builder and copy the SQL)

Place the SQL in to a code module
Strip out the Criteria  and the destination target
 then in code

IE: a sql statment copied from the query builder

SELECT DISTINCT Field1, Field2, Dept INTO Finance_tbl
FROM Your_tbl WHERE (((Your_tbl.Dept)=""FINANCE""));"

modified to run in code

    gstrSQL =  "SELECT DISTINCT Field1, Field2, Dept  “ & _
           “INTO Finance_tbl " & _
                      "FROM Your_tbl " & _
                      "WHERE (((Your_tbl.Dept)=""FINANCE""));"

Now rip out all reference to Finance and build a variable into the string

    gstrSQL =  "SELECT DISTINCT Field1, Field2, Dept  “ & _
           “INTO “ & strDept & “_tbl " & _
                      "FROM Your_tbl " & _
                      "WHERE (((Your_tbl.Dept)=’" & strDept & "’));"

NOTE: the change of the double quotes around Finance to a single & double quote
A single quote will work as a double quote when defining a string and I find it easier to understand than a series of double quotes (and I always get the syntax wrong when I try)

From here there are a couple ways to go… but it boils down to changing the variable strDept and building the sql string and running it.

Before I get bogged down into the details …   is this something you think will suite your needs and you would be comfortable doing?
onesegunAuthor Commented:
Excellent. Good man

It took a bit of fiddling about with the qoutes and table name variables that had spaces in them. In the end I got it to work by creating the array and looping through that for each department
Great...   Glad to help.
Again... My changing to single quotes is a "Personal Style" to make my life easier and easier to read...  
With spaces in the name... IF I have control over the naming convention...  I avoid spaces in any object or field name...  Again as personal style but more over to make my life easier....

The loop/array process sounds perfect...   either keeping the SQL within the loop or setting the var and calling a routine from within the loop...  personal style rules...

The important part is....   getting the job done.. (in a maintainable way)

Good luck
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now