Solved

Automate make table query with different criterias

Posted on 2011-02-14
4
472 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now