By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

Automating Query to use input values (array)

P: 7
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
Mar 23 '07 #1
Share this Question
Share on Google+
18 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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?
Mar 23 '07 #2

P: 7
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
Mar 23 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
How are these min/max pairs coming about? Are they chosen by the user or are they constant?
Mar 23 '07 #4

P: 7
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
Mar 23 '07 #5

ADezii
Expert 5K+
P: 8,638
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
Mar 24 '07 #6

P: 7
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
Mar 24 '07 #7

ADezii
Expert 5K+
P: 8,638
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
Mar 24 '07 #8

P: 7
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
Mar 24 '07 #9

ADezii
Expert 5K+
P: 8,638
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.
Mar 25 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 25 '07 #11

ADezii
Expert 5K+
P: 8,638
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.
Mar 25 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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.  
Mar 25 '07 #13

P: 7
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
Mar 25 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 25 '07 #15

P: 7
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)?
Mar 25 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 26 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 26 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 26 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.