Query Help

[jeancake]

I have a query that I run every month that gives me all the claims for a list of policies. I would like to export that list to excel where each policy would have it's own page with its list of claims. How would I construct this?

 

[guerillaunit]

You would have to write a module that does this. It would be a loop that goes through the entire list and creates a separate tab in a workbook for every policy

 

[jeancake]

Thanks! I know enough vba to export the list,but not enough to set up the loop. Can you help?

 

[guerillaunit]

Can you post a screenshot of what your query results look like?

 

[Norie]

Why not create a report that groups by policy?

Then you can have a separate page for each policy.

 

[jeancake]

The raw data looks something like this:
<TABLE style="WIDTH: 293pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=390 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 170pt; mso-width-source: userset; mso-width-alt: 8265" width=226><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 height=17 width=64>Policy</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 170pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 width=226>INSD_NM</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 width=100>Claim</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>12345</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl25>AMCORP</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-040763-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>12345</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>AMCORP</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-040834-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>12345</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>AMCORP</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-040956-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>12345</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>AMCORP</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-041079-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>67890</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="STARLING; RON ">STARLING; RON </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>980-004401-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>67890</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="STARLING; RON ">STARLING; RON </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>980-004401-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>111213</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="BILL WILLIAMS ">BILL WILLIAMS </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-046783-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>111213</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="BILL WILLIAMS ">BILL WILLIAMS </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-044488-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>111213</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="BILL WILLIAMS ">BILL WILLIAMS </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-045344-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>111213</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="BILL WILLIAMS ">BILL WILLIAMS </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-045746-001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=17 x:num>111213</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25 x:str="BILL WILLIAMS ">BILL WILLIAMS </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl25>295-045890-001</TD></TR></TBODY></TABLE>
It's just a sample of 3 out of dozens of policies. I'm only showing 3 fields, but there are a couple more. Ultimately I would like each page to have its own policy and claims with the policy # as the sheet name.

 

[guerillaunit]

Create a query named "step1" with the below SQL

SELECT sampletable.Policy
FROM sampletable
GROUP BY sampletable.Policy;

Change "sampletable" to the actual name of the query you mentioned in your initial post

Start a new module and use the following code:

Option Compare Database
Sub test()
Dim newapp As Excel.Application
Dim newworkbook As Excel.Workbook
Dim rst As DAO.Recordset
Dim newtab As String
Dim sqlstatement As String
Dim rng As Excel.Range
Set newapp = New Excel.Application
newapp.Visible = True
Set newworkbook = newapp.Workbooks.Add
Set rst = CurrentDb.OpenRecordset("Step1")
Do Until rst.EOF
    newtab = rst("Policy")
    Sheets.Add.Name = newtab
    rst.MoveNext
Loop
Sheets("sheet1").Delete
Sheets("sheet2").Delete
Sheets("sheet3").Delete
For Each Sheet In ActiveWorkbook.Sheets
    sqlstring = "SELECT claim FROM sampletable WHERE policy = " & Sheet.Name
    Set rst = CurrentDb.OpenRecordset(sqlstring, dbOpenSnapshot)
    Sheet.Range("A1").CopyFromRecordset rst
Next Sheet
    
End Sub

Again, remember to change "sampletable" to the actual name of the query you mentioned in your initial post.
Before you run this, make sure you have the Microsoft DAO Object Library and Microsoft Excel Object Library referenced

 

[jeancake]

This is exactly what I needed. This will save me so much time going forward.

Thanks!