Automating Query to use input values (array)

[WilhelmAccess]

Hi,

I am trying to automate the running of a SQL Query. I have a Table in Access 2003 that contains records with several fields, (member identifier, $ amount and months during the year they were members). I want to create a "histogram" of sorts to count and sum the information depending on which bucket the $ amount falls in. For example all members with $ amount between 0 and $1, all members between $1 and $100, etc. The two Queries I have written do this in steps, and currently prompts me to enter the Min and Max values. The first query is a Select Query that creates a Table of all records that fall betwqeen the Min and Max. The second Query then collapses the data to Sum the $ amount and count the members. I then copy the result into an Excel spreadsheet.

How can I automate this process so that I can have the Queries run automatically using as an input an array of Min and Max pairs? I'd also like the output of each run to be automatically (and successively) stored in an Excel Worksheet.

Here are the SQL Queries I have written:
The first is called CLT and creates a Table used by the second

SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
FROM 15B
WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
 

and the second query:

SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
FROM CLT
GROUP BY [CLT].MIN, [CLT].MAX;
 

I have been trying to use VBA scripts, but have not been successful. Any advice would be appreciated,
Wil

[Rabbit]

Hi,

I am trying to automate the running of a SQL Query. I have a Table in Access 2003 that contains records with several fields, (member identifier, $ amount and months during the year they were members). I want to create a "histogram" of sorts to count and sum the information depending on which bucket the $ amount falls in. For example all members with $ amount between 0 and $1, all members between $1 and $100, etc. The two Queries I have written do this in steps, and currently prompts me to enter the Min and Max values. The first query is a Select Query that creates a Table of all records that fall betwqeen the Min and Max. The second Query then collapses the data to Sum the $ amount and count the members. I then copy the result into an Excel spreadsheet.

How can I automate this process so that I can have the Queries run automatically using as an input an array of Min and Max pairs? I'd also like the output of each run to be automatically (and successively) stored in an Excel Worksheet.

Here are the SQL Queries I have written:
The first is called CLT and creates a Table used by the second

SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
FROM 15B
WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
 

and the second query:

SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
FROM CLT
GROUP BY [CLT].MIN, [CLT].MAX;
 

I have been trying to use VBA scripts, but have not been successful. Any advice would be appreciated,
Wil

What are the VBA scripts you have tried to use? Can you post the code?

[WilhelmAccess]

I guess I should have said "I'd like to use VBA scripts", because they seem like they could handle the approach. I don't have any VBA scripts - only the SQL queries shown above.

Thanks,
Wil

[Rabbit]

How are these min/max pairs coming about? Are they chosen by the user or are they constant?

[WilhelmAccess]

They are constant. Right now when I run the Select query I enter them by hand. However, I have them stored in a Table and would like to automate the process. Here is the table:

Min Max
($20,000,000.00 ) ($0.01)
$0.00 $1.00
$1.00 $250.00
$250.00 $1,000.00
$1,000.00 $2,500.00
$2,500.00 $5,000.00
$5,000.00 $10,000.00
$10,000.00 $15,000.00
$15,000.00 $20,000.00
$20,000.00 $25,000.00
$25,000.00 $30,000.00
$30,000.00 $35,000.00
$35,000.00 $40,000.00
$40,000.00 $45,000.00
$45,000.00 $50,000.00
$50,000.00 $55,000.00
$55,000.00 $60,000.00
$60,000.00 $65,000.00
$65,000.00 $70,000.00
$70,000.00 $75,000.00
$75,000.00 $80,000.00
$80,000.00 $85,000.00
$85,000.00 $90,000.00
$90,000.00 $95,000.00
$95,000.00 $100,000.00
$100,000.00 $150,000.00
$150,000.00 $200,000.00
$200,000.00 $250,000.00
$250,000.00 $300,000.00
$300,000.00 $350,000.00
$350,000.00 $400,000.00
$400,000.00 $450,000.00
$450,000.00 $500,000.00
$500,000.00 $750,000.00
$750,000.00 $1,000,000.00
$1,000,000.00 $2,000,000.00
$2,000,000.00 $200,000,000.00


Thanks,
Wil

[ADezii]

Hi,

I am trying to automate the running of a SQL Query. I have a Table in Access 2003 that contains records with several fields, (member identifier, $ amount and months during the year they were members). I want to create a "histogram" of sorts to count and sum the information depending on which bucket the $ amount falls in. For example all members with $ amount between 0 and $1, all members between $1 and $100, etc. The two Queries I have written do this in steps, and currently prompts me to enter the Min and Max values. The first query is a Select Query that creates a Table of all records that fall betwqeen the Min and Max. The second Query then collapses the data to Sum the $ amount and count the members. I then copy the result into an Excel spreadsheet.

How can I automate this process so that I can have the Queries run automatically using as an input an array of Min and Max pairs? I'd also like the output of each run to be automatically (and successively) stored in an Excel Worksheet.

Here are the SQL Queries I have written:
The first is called CLT and creates a Table used by the second

SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
FROM 15B
WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
 

and the second query:

SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
FROM CLT
GROUP BY [CLT].MIN, [CLT].MAX;
 

I have been trying to use VBA scripts, but have not been successful. Any advice would be appreciated,
Wil

Here is a basic Code Template that you can adapt to your own specific needs in
order to automate your 'Query Creation Process', It assumes the following:
__1. tblMinMax

[Min] (CURRENCY)

[Max] (CURRENCY)

__2. tblMemberData

[Member ID] (AUTO - PK)

[Total] (CURRENCY)

[Member Months] (TEXT)

Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
Dim strSQL As String, rsResult As DAO.Recordset
 
Set MyDB = CurrentDb()
 
'rsMinMax will consist of all Min/Max pairs in tblMinMax
Set rsMinMax = MyDB.OpenRecordset("tblMinMax", dbOpenSnapshot)
 
rsMinMax.MoveFirst
 
Do While Not rsMinMax.EOF
  strSQL = "SELECT * FROM tblMemberData WHERE [Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    rsResult.MoveFirst
    'processing goes here for each Min/Max pair
  rsMinMax.MoveNext
Loop
 
rsMinMax.Close
rsResult.Close

[WilhelmAccess]

Hi ADezii,

Thanks for the code snippet. I have added it it to a function, and am still having trouble with the second part of my processing. I have modified your snippet to run the first query(with the fields I am interested in), but I still need to run a second Query to Sum and Count. Can I operate on rsResult as if it were a Table? Please look at the following code to see what I have attempted:

Function GenContTbl(tblMinMax, MemberTbl, File_Name) As Integer
'?GenContTbl("CTRanges", "15A", "C:\crap\test2.txt")
Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
Dim line
 
Set MyDB = CurrentDb()
 
'rsMinMax will consist of all Min/Max pairs in tblMinMax
Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
 
rsMinMax.MoveFirst
 
Do While Not rsMinMax.EOF
  'strSQL = "SELECT * FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  strSQL = "SELECT [Member ID], [Total Allowed Claims], [Member Months] FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    rsResult.MoveFirst
 
  'strSQL2 = "SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total Allowed Claims]) AS [SumOfTotal Allowed Claims]" FROM CLT GROUP BY [CLT].MIN, [CLT].MAX;
  strSQL2 = "SELECT Count(NZ([Member ID])) AS [CountOfMember ID]FROM rsResult;"
  Set rsResult2 = MyDB.OpenRecordset(strSQL2, dbOpenSnapshot)
 
    'processing goes here for each Min/Max pair
 
  rsMinMax.MoveNext
Loop
 
rsMinMax.Close
rsResult.Close
 
End Function
 

Is it possible to examine rsResult and rsReult2 in the debugger as I step through the code?

Secondly, I would like to somehow store the result of the second query - preferebly as a record in a new table, which I would then output to excel.

Lastly, I am running into an error situation when the first query finds zero records. Is there an easy way around that?

Thanks much,
Wil

[ADezii]

Hi ADezii,

Thanks for the code snippet. I have added it it to a function, and am still having trouble with the second part of my processing. I have modified your snippet to run the first query(with the fields I am interested in), but I still need to run a second Query to Sum and Count. Can I operate on rsResult as if it were a Table? Please look at the following code to see what I have attempted:

Function GenContTbl(tblMinMax, MemberTbl, File_Name) As Integer
'?GenContTbl("CTRanges", "15A", "C:\crap\test2.txt")
Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
Dim line
 
Set MyDB = CurrentDb()
 
'rsMinMax will consist of all Min/Max pairs in tblMinMax
Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
 
rsMinMax.MoveFirst
 
Do While Not rsMinMax.EOF
  'strSQL = "SELECT * FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  strSQL = "SELECT [Member ID], [Total Allowed Claims], [Member Months] FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    rsResult.MoveFirst
 
  'strSQL2 = "SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total Allowed Claims]) AS [SumOfTotal Allowed Claims]" FROM CLT GROUP BY [CLT].MIN, [CLT].MAX;
  strSQL2 = "SELECT Count(NZ([Member ID])) AS [CountOfMember ID]FROM rsResult;"
  Set rsResult2 = MyDB.OpenRecordset(strSQL2, dbOpenSnapshot)
 
    'processing goes here for each Min/Max pair
 
  rsMinMax.MoveNext
Loop
 
rsMinMax.Close
rsResult.Close
 
End Function
 

Is it possible to examine rsResult and rsReult2 in the debugger as I step through the code?

Secondly, I would like to somehow store the result of the second query - preferebly as a record in a new table, which I would then output to excel.

Lastly, I am running into an error situation when the first query finds zero records. Is there an easy way around that?

Thanks much,
Wil

'Insert this line for the Summation of [Total] for a given Min/Max combination as indicated in the code below (code window):
Debug.Print DSum("[Total]", "tblMemberData" , "[Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])

Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
Dim strSQL As String, rsResult As DAO.Recordset
 
Set MyDB = CurrentDb()
 
'rsMinMax will consist of all Min/Max pairs in tblMinMax
Set rsMinMax = MyDB.OpenRecordset("tblMinMax", dbOpenSnapshot)
 
rsMinMax.MoveFirst
 
Do While Not rsMinMax.EOF
  strSQL = "SELECT * FROM tblMemberData WHERE [Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  rsResult.MoveFirst
    '----------------------------- Insertion Here -----------------------------
  Debug.Print DSum("[Total]", "tblMemberData", "[Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])
 
    'processing goes here for each Min/Max pair
  rsMinMax.MoveNext
Loop
 
rsMinMax.Close
rsResult.Close

[WilhelmAccess]

Thanks for the Debug statement. It is very helpful.

Is there a way to run my second query on the resultant "rsResult"?

Thanks in advance,
Wil

[ADezii]

Thanks for the Debug statement. It is very helpful.

Is there a way to run my second query on the resultant "rsResult"?

Thanks in advance,
Wil

You seem to be unlucky in that you caught the least skilled Moderator/Expert as far as SQL goes. From what I see, you cannot Open the Query from the Recordset (rsResult) itself but you can utilize the SQL String used to create the Rercordset, namely strSQL. You cannot use the RunSQL or Execute Methods since they are reserved for Action Queries. What you can do, and this is a far stretch, is create a QueryDef (Query) Object based on the SQL and then Open the Query. The code below will illustrate the point I am making.

Dim qdf As QueryDef, qdf2 As QueryDef
'If the Query Exists, it must be Deleted or an Error will occur
For Each qdf2 In CurrentDb.QueryDefs
  If qdf2.Name = "MyQuery" Then
    CurrentDb.QueryDefs.Delete qdf2.Name
  End If
Next
 
'Create the QueryDef, in your case from strSQL
Set qdf = CurrentDb.CreateQueryDef("MyQuery", "SELECT * FROM Employees WHERE [LastName] Like 'Bu*'")
 
'Open the Query
DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly

NOTE: Please do not go crazy trying to implement this logic. As previously stated, I can almost guarantee that more skilled members will come up with a more viable solution, but for now it is an alternative.