Code instead of queries all over the place?

[jbuckner72]

Generic question about capabilities of MS Access. I am currently working on a mini project to take a dataset and do a bunch of grouping, various distinct data sets, filtering and all kinds of stuff. I am going about my standard method of writing queries, then queries on top of those queries, etc. All in all I think this will result in 15-20 queries all mashed together with a union query at the end, then a manual export to tab delimited. With MS Access, is there a better way to skin this cat? Is it possible to create a module of code that allows you to do this all at once. Where the code contains the query information, but stores as variables that are referenced later in code, etc?

[Welshgasman]

Some people opinion that if you have to use Union queries, then your DB structure is incorrect?

[jbuckner72]

I don't necessarily disagree with that statement. I am probably not using the best tool for the job necessarily. Mostly using MS Access to transform data from external systems into something different. Different format, different fields, include calculations, etc. A more appropriate tool would be something like Python, Perl, even Powershell. I am just too lazy to learn those and I have decent skills with SQL so that is where I go usually.

[orange]

jbuckner,

It seems you are asking HOW to do something in Access. It would facilitate things if you told us WHAT you want to achieve in simple terms or even a couple of examples. It's difficult -near impossible - to advise on How when the What has not been understood.
I'm sure you'll get focused responses when the requirement is clear.

[Micron]

I'd say the short answer to the question is yes, it can all be done in code.

[orange]

I agree with micron. As a friend of mine use to say --It's SMOP. Simple matter of programming.

[davegri]

Here's a sub that will accept an action SQL string, create a query (without a name) and execute it. It might accommodate your needs. I realize you could just execute the query string directly, but this is cooler.
Example use:

call MakeQuery("Update tblMembers Set lastname ='Smith' Where lastname = 'Jones'")

Public Sub MakeQuery(strPassedSQL As String)
    Dim db As DAO.Database: Set db = CurrentDb
    Dim qthisQuery As DAO.QueryDef
    Set qthisQuery = db.CreateQueryDef("", strPassedSQL)
    qthisQuery.Execute dbFailOnError
    Set qthisQuery = Nothing: Set db = Nothing
End Sub

[jbuckner72]

Thanks all. Sounds like the general capabilities exist enough for me to go exploring.

For the note on providing specific details, I intentionally wanted to ask a generic question, but in general, I find myself following this scenario below quite often. In order to get from that source table to the desired output, it takes me 3-4 queries depending on a few factors. That is fine. It is just that I write those queries in a code editor that makes it easy to read, then copy/paste into Access. Maintaining all that in one set of code would be far more efficient from a maintenance perspective.

Table: Customer_Visits

Name	City	Cust_ID
John Doe	New York	1
Jane Doe	Atlanta	2
John Smith	New York	3
John Doe	New York	1
Daffy Duck	Atlanta	4

Desired Output

City	Visits	Unique Customers
New York	3	2
Atlanta	2	2

[davegri]

It is just that I write those queries in a code editor that makes it easy to read, then copy/paste into Access. Maintaining all that in one set of code would be far more efficient from a maintenance perspective.

You could keep your SQL strings in a table, then have a form for maintenance. The form could also have a button for running the selected query.