Code to Insert into Query not build sql in function, add to sql in qry 2

[dkmansion]

Is there a way to modify a query, adding permenantly to it (periodically) through code (function, etc)?

We receive our Nextel billing as an Access db and normally the info is only needed monthly and not as a union of the multiple months. Though lately I have had to compile multiple month data. I have an Admin db that I link the tables from the individual month's files (now 177MB each). Since the max db sizefor Access 2000 is 1GB and the structure (adding of fields can change) I decided against appending all the data to one Access db. [Adding to an Oracle or other sql db is not possible at this time, maybe later...]

db Access 2000
Ultimately 12 linked dbs to one Admin db

I have Union queries that include the new month (when I receive it) for each table then I run my queries against the Union queries I built.

Basically the sql looks like this (Feb was our first month)

SELECT *, "Feb 2006" AS [Month]  FROM bill_clin_SAS2;

UNION ALL select *, "Mar 2006" as [Month] from bill_clin_SAS3;

Each Month the code would add...

UNION ALL select *, "[red]MMM YYYY[/red]" as [Month] from bill_clin_SAS[blue]m[/blue];

Where
[red]MMM YYYY[/red] for the month and year Apr 2006 etc and
[blue]m[/blue] would be 1-12 for the month...
when it was run (I would add variables etc where appropriate).

Any Ideas to automate, there are 14 to do each month every year. I hate to do it manually if I can code it once and pass a couple of variables.


Thanks in advance

Donald M

 

[dhookom]

You can use DAO code to modify the SQL of a saved query. Generically:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("quniMyUnion")
strSQL = "SELECT...."
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

You could also write a procedure which acceptes a date (mm/yy) and loops through the necessary (14?) increments (or decrements) to get the set of months required.

Public Function basUnionMnths(dtEnd As Date, Mnths As Integer) As String

    Dim strSQL As String
    Dim Quo As String * 1
    Dim strMnth As String
    Dim Idx As Integer
    Dim dtMnth As Date

    Quo = Chr(34)

    dtMnth = dtEnd
    strMnth = Format(dtMnth, "mmm yyyy")

    strSQL = "Select *, " & Quo & strMnth & Quo & " " & "AS [Month]  FROM bill_clin_SAS2; "

    Idx = Mnths
    While Idx >= 0
        dtMnth = DateAdd("m", -1, dtMnth)
        strMnth = Format(dtMnth, "mmm yyyy")
        strSQL = strSQL & "UNION ALL Select *, " & Quo & strMnth & Quo & " " & " as [Month] from bill_clin_SAS3; "
        Idx = Idx - 1
    Wend

End Function

I did notice that the reference to :Bill_Clin_SAS" changes from "3" (first bill) to "2" for the second one, but have no idea if this is a progression or a typo in your code. This is also NOT tested anby further than to get a string output without error. Further, note that -as written- the second parameter Mnths (the number of months backward to count) is exclusice of the current month. e.g. to get a fourteen month history, the second parameter would be 13. also notice that the first parameter is a DATE. Since the day part of the date is not used, any date in the month which ends the sequence is acceptable, and if that ending date is the current month, hte parameter can be "Date".




MichaelRed

 

[dkmansion]

Dhookom: Gave it a try and this is what I was looking for changed line 6 to read:

strSQL = qd.sql & vbcrlf & "union all select...." & varMonth & ";"

This might just do the job.

MichaelRed: I haven't written off your sugesstion. I'll give it a try also and see which flavor works best in my situation. The numbers after CLIN_SAS# represent the month for the table. Since each month I link the new month's tables, they get the numeric progression at the end when you link or import multiple objects with the same name:

Eg: tbl, tbl1, tbl2 etc

A Star for each of you Thanks again. Much Appreciated...


Donald M