Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Automate make table query with different criterias

Posted on 2011-02-14
4
Medium Priority
?
481 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:Lucas
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 200 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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