Solved

Automate make table query with different criterias

Posted on 2011-02-14
4
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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