Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Automating Query to use input values (array)

Newbie
  
Join Date: Mar 2007
Posts: 7
#1: Mar 23 '07
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
  2. FROM 15B
  3. WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
  4.  
and the second query:
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
  2. FROM CLT
  3. GROUP BY [CLT].MIN, [CLT].MAX;
  4.  

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

Rabbit's Avatar
Expert
  
Join Date: Jan 2007
Location: California
Posts: 3,835
#2: Mar 23 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1. SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
  2. FROM 15B
  3. WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
  4.  
and the second query:
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
  2. FROM CLT
  3. GROUP BY [CLT].MIN, [CLT].MAX;
  4.  

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?
Newbie
  
Join Date: Mar 2007
Posts: 7
#3: Mar 24 '07

re: Automating Query to use input values (array)

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's Avatar
Expert
  
Join Date: Jan 2007
Location: California
Posts: 3,835
#4: Mar 24 '07

re: Automating Query to use input values (array)

How are these min/max pairs coming about? Are they chosen by the user or are they constant?
Newbie
  
Join Date: Mar 2007
Posts: 7
#5: Mar 24 '07

re: Automating Query to use input values (array)

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's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,218
#6: Mar 24 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1. SELECT [15B].[Member ID], [15B].[Total], [15B].[Member Months], [min $ ?] AS [MIN], [max $ ?] AS [MAX]
  2. FROM 15B
  3. WHERE ((([15B].[Total])>=[min $ ?] And ([15B].[Total])<[max $ ?]));
  4.  
and the second query:
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLT].MIN, [CLT].MAX, Count(NZ([CLT].[Member ID])) AS [CountOfMember ID], Sum([CLT].[Total]) AS [SumOfTotal]
  2. FROM CLT
  3. GROUP BY [CLT].MIN, [CLT].MAX;
  4.  

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)
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  2. Dim strSQL As String, rsResult As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  7. Set rsMinMax = MyDB.OpenRecordset("tblMinMax", dbOpenSnapshot)
  8.  
  9. rsMinMax.MoveFirst
  10.  
  11. Do While Not rsMinMax.EOF
  12.   strSQL = "SELECT * FROM tblMemberData WHERE [Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  13.   Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  14.     rsResult.MoveFirst
  15.     'processing goes here for each Min/Max pair
  16.   rsMinMax.MoveNext
  17. Loop
  18.  
  19. rsMinMax.Close
  20. rsResult.Close
Newbie
  
Join Date: Mar 2007
Posts: 7
#7: Mar 24 '07

re: Automating Query to use input values (array)

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:

Expand|Select|Wrap|Line Numbers
  1. Function GenContTbl(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTbl("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5. Dim line
  6.  
  7. Set MyDB = CurrentDb()
  8.  
  9. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  10. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  11.  
  12. rsMinMax.MoveFirst
  13.  
  14. Do While Not rsMinMax.EOF
  15.   'strSQL = "SELECT * FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  16.   strSQL = "SELECT [Member ID], [Total Allowed Claims], [Member Months] FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  17.   Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  18.     rsResult.MoveFirst
  19.  
  20.   '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;
  21.   strSQL2 = "SELECT Count(NZ([Member ID])) AS [CountOfMember ID]FROM rsResult;"
  22.   Set rsResult2 = MyDB.OpenRecordset(strSQL2, dbOpenSnapshot)
  23.  
  24.     'processing goes here for each Min/Max pair
  25.  
  26.   rsMinMax.MoveNext
  27. Loop
  28.  
  29. rsMinMax.Close
  30. rsResult.Close
  31.  
  32. End Function
  33.  
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's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,218
#8: Mar 24 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers
  1. Function GenContTbl(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTbl("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5. Dim line
  6.  
  7. Set MyDB = CurrentDb()
  8.  
  9. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  10. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  11.  
  12. rsMinMax.MoveFirst
  13.  
  14. Do While Not rsMinMax.EOF
  15.   'strSQL = "SELECT * FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  16.   strSQL = "SELECT [Member ID], [Total Allowed Claims], [Member Months] FROM 15A WHERE [Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  17.   Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  18.     rsResult.MoveFirst
  19.  
  20.   '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;
  21.   strSQL2 = "SELECT Count(NZ([Member ID])) AS [CountOfMember ID]FROM rsResult;"
  22.   Set rsResult2 = MyDB.OpenRecordset(strSQL2, dbOpenSnapshot)
  23.  
  24.     'processing goes here for each Min/Max pair
  25.  
  26.   rsMinMax.MoveNext
  27. Loop
  28.  
  29. rsMinMax.Close
  30. rsResult.Close
  31.  
  32. End Function
  33.  
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])
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  2. Dim strSQL As String, rsResult As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  7. Set rsMinMax = MyDB.OpenRecordset("tblMinMax", dbOpenSnapshot)
  8.  
  9. rsMinMax.MoveFirst
  10.  
  11. Do While Not rsMinMax.EOF
  12.   strSQL = "SELECT * FROM tblMemberData WHERE [Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max] & ";"
  13.   Set rsResult = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  14.   rsResult.MoveFirst
  15.     '----------------------------- Insertion Here -----------------------------
  16.   Debug.Print DSum("[Total]", "tblMemberData", "[Total] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])
  17.  
  18.     'processing goes here for each Min/Max pair
  19.   rsMinMax.MoveNext
  20. Loop
  21.  
  22. rsMinMax.Close
  23. rsResult.Close
Newbie
  
Join Date: Mar 2007
Posts: 7
#9: Mar 24 '07

re: Automating Query to use input values (array)

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's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,218
#10: Mar 25 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by 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

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.


Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef, qdf2 As QueryDef
  2. 'If the Query Exists, it must be Deleted or an Error will occur
  3. For Each qdf2 In CurrentDb.QueryDefs
  4.   If qdf2.Name = "MyQuery" Then
  5.     CurrentDb.QueryDefs.Delete qdf2.Name
  6.   End If
  7. Next
  8.  
  9. 'Create the QueryDef, in your case from strSQL
  10. Set qdf = CurrentDb.CreateQueryDef("MyQuery", "SELECT * FROM Employees WHERE [LastName] Like 'Bu*'")
  11.  
  12. 'Open the Query
  13. 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.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#11: Mar 25 '07

re: Automating Query to use input values (array)

I'm not sure what you are trying to do with your second query but if all you want is a count of the records returned then
Expand|Select|Wrap|Line Numbers
  1. Dim countRes As Integer
  2.  
  3.    rsResult.MoveLast
  4.    rsResult.MoveFirst
  5.    countRes = rsResult.RecordCount 
  6.  
Mary
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,218
#12: Mar 25 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by mmccarthy

I'm not sure what you are trying to do with your second query but if all you want is a count of the records returned then

Expand|Select|Wrap|Line Numbers
  1. Dim countRes As Integer
  2.  
  3.    rsResult.MoveLast
  4.    rsResult.MoveFirst
  5.    countRes = rsResult.RecordCount 
  6.  
Mary

Hello Mary:
I think what is requested here is a Summation of all values within the given Min/Max Range.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#13: Mar 25 '07

re: Automating Query to use input values (array)

Quote:

Originally Posted by ADezii

Hello Mary:
I think what is requested here is a Summation of all values within the given Min/Max Range.

The first query returns a set of records between the min and max range, right?

So if not a count of all records are we looking at the sum of the values within this range?

If so then ...
Expand|Select|Wrap|Line Numbers
  1. Dim amt As Currancy ' don't know what your value is so this is a guess
  2.  
  3.    amt = 0
  4.    rsResult.MoveFirst
  5.    Do until rsResult.EOF
  6.       amt = amt + rsResult![Total Allowed Claims]
  7.       rsResult.MoveNext
  8.    Loop
  9.  
Newbie
  
Join Date: Mar 2007
Posts: 7
#14: Mar 25 '07

re: Automating Query to use input values (array)

I have abandoned the SQL Query approach, and now am just using a giant Print statement in the loop.
Expand|Select|Wrap|Line Numbers
  1. Function GenContTblPrnt(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTblPrnt("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb()
  7. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  8. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  9.  
  10. rsMinMax.MoveFirst
  11.  
  12. Do While Not rsMinMax.EOF
  13.  
  14.    Debug.Print rsMinMax![Min], ";", rsMinMax![Max], ";", DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])
  15.    rsMinMax.MoveNext
  16.  
  17. Loop
  18.  
  19. rsMinMax.Close
  20.  
  21. End Function
  22.  
It works OK. Any advice on getting the output data into either a Table or Excel?

Thanks,
Wil
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#15: Mar 25 '07

re: Automating Query to use input values (array)

Create a table to hold the records. For this example lets call it tblResults

Expand|Select|Wrap|Line Numbers
  1. Function GenContTblPrnt(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTblPrnt("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb()
  7. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  8. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  9.  
  10. rsMinMax.MoveFirst
  11.  
  12. Do While Not rsMinMax.EOF
  13.  
  14.    Debug.Print rsMinMax![Min], ";", rsMinMax![Max], ";", DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", 
  15. DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])
  16.  
  17.    strSQL = "INSERT INTO tblResults(MinAllowed, MaxAllowed, CountMembers, " & _
  18.    "TotalAllowed, TotalMonths) VALUES (" & rsMinMax![Min] & ", " & rsMinMax![Max] & ", " & _
  19.    DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ", " & _
  20.    DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ", " & _
  21.    DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ")"
  22.  
  23.    DoCmd.RunSQL strSQL 
  24.  
  25.    rsMinMax.MoveNext
  26.  
  27. Loop
  28.  
  29. rsMinMax.Close
  30.  
  31. End Function
  32.  
Mary
Newbie
  
Join Date: Mar 2007
Posts: 7
#16: Mar 25 '07

re: Automating Query to use input values (array)

Thank you Mary.

It seems like with this solution I am required to have already created the Table tblResults. Is there a way I can create the Table from within my function, maybe even pass into the function the name of the table? For example, since I am passing in the name of the table from which to pull the data, can I create the output table to be named something like "input"_CT (where input is the MemberTbl)?
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#17: Mar 26 '07

re: Automating Query to use input values (array)

Try this ...

Expand|Select|Wrap|Line Numbers
  1. Function GenContTblPrnt(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTblPrnt("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb()
  7. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  8. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  9.  
  10. rsMinMax.MoveFirst
  11.  
  12. Do While Not rsMinMax.EOF
  13.  
  14.    Debug.Print rsMinMax![Min], ";", rsMinMax![Max], ";", DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", 
  15. DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]), ";", DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max])
  16.  
  17.    strSQL = "SELECT " & rsMinMax![Min] & " As MinAllowed, " & _
  18.    rsMinMax![Max] & " As MaxAllowed, " & _
  19.    DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As CountMembers, " & _
  20.    DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As TotalAllowed, " & _
  21.    DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As TotalMonths" & _
  22.    " INTO " & MemberTbl & "_CT"
  23.  
  24.    DoCmd.RunSQL strSQL 
  25.  
  26.    rsMinMax.MoveNext
  27.  
  28. Loop
  29.  
  30. rsMinMax.Close
  31.  
  32. End Function
  33.  
Mary
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#18: Mar 26 '07

re: Automating Query to use input values (array)

Actually, that won't work as it will just create a new table for every record.

Give me a few minutes to think about it.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#19: Mar 26 '07

re: Automating Query to use input values (array)

OK, try this ...

Expand|Select|Wrap|Line Numbers
  1. Function GenContTblPrnt(tblMinMax, MemberTbl, File_Name) As Integer
  2. '?GenContTblPrnt("CTRanges", "15A", "C:\crap\test2.txt")
  3. Dim MyDB As DAO.Database, rsMinMax As DAO.Recordset
  4. Dim strSQL As String, strSQL2 As String, rsResult As DAO.Recordset, rsResult2 As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb()
  7. 'rsMinMax will consist of all Min/Max pairs in tblMinMax
  8. Set rsMinMax = MyDB.OpenRecordset(tblMinMax, dbOpenSnapshot)
  9.  
  10.     rsMinMax.MoveFirst
  11.  
  12.    strSQL = "SELECT " & rsMinMax![Min] & " As MinAllowed, " & _
  13.    rsMinMax![Max] & " As MaxAllowed, " & _
  14.    DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As CountMembers, " & _
  15.    DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As TotalAllowed, " & _
  16.    DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & " As TotalMonths" & _
  17.    " INTO " & MemberTbl & "_CT"
  18.  
  19.    DoCmd.RunSQL strSQL 
  20.  
  21.    Do While Not rsMinMax.EOF
  22.  
  23.     rsMinMax.MoveNext
  24.  
  25.     strSQL = "INSERT INTO tblResults(MinAllowed, MaxAllowed, CountMembers, " & _
  26.     "TotalAllowed, TotalMonths) VALUES (" & rsMinMax![Min] & ", " & rsMinMax![Max] & ", " & _
  27.     DCount("[Member ID]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ", " & _
  28.     DSum("[Total Allowed Claims]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ", " & _
  29.     DSum("[Member Months]", MemberTbl, "[Total Allowed Claims] Between " & rsMinMax![Min] & " And " & rsMinMax![Max]) & ")"
  30.  
  31.     DoCmd.RunSQL strSQL 
  32.  
  33.    Loop
  34.  
  35.    rsMinMax.Close
  36.  
  37. End Function
  38.  
Mary
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,471 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved