Solved

Automate make table query with different criterias

Posted on 2011-02-14
4
474 Views
Last Modified: 2012-05-11
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.

Thanks,

OS
0
Comment
Question by:onesegun
  • 2
4 Comments
 
LVL 13

Expert Comment

by:lucas911
ID: 34888117
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..
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 50 total points
ID: 34888368
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?
0
 

Author Closing Comment

by:onesegun
ID: 34961613
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
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34962190
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
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question