create table from adodb recordset

[MrsMope]

Hi All,
I have a recordset in my application that combined 4 different companies data, I want to create a table based on this recordset. The end output will be an access report, or an export to excel.
What is the best way to accomplish this? Here is the SQL statement for my recordset:

SELECT A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, Sum(B.WB_COMM_CALC) AS SumOfWB_COMM_CALC, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, 1 AS Company, B.WB_TIER_PERC, B.ITEMNMBR FROM misown_MIS0121T as D INNER JOIN((WaubeWB_CPICH as B LEFT JOIN WaubeRM00101 as A ON B.CUSTNMBR = A.CUSTNMBR) LEFT JOIN WaubeRM00301 as C ON B.SLPRSNID = C.SLPRSNID) ON D.POL_NBR= B.CUSTNMBR GROUP BY A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, B.WB_TIER_PERC, B.ITEMNMBR, C.SLPRSNID , B.WB_DATE_COMM_PROC, C.SLPRSNID HAVING (B.WB_DATE_COMM_PROC)>= #1/1/2005# And (B.WB_DATE_COMM_PROC)<= #10/31/2005# and b.SLPRSNID= '57675          ' UNION ALL SELECT A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, Sum(B.WB_COMM_CALC) AS SumOfWB_COMM_CALC, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, 3 AS Company, B.WB_TIER_PERC, B.ITEMNMBR FROM misown_MIS0121T as D INNER JOIN((HartWB_CPICH as B LEFT JOIN HartRM00101 as A ON B.CUSTNMBR = A.CUSTNMBR) LEFT JOIN HartRM00301 as C ON B.SLP
RSNID = C.SLPRSNID) ON D.POL_NBR= B.CUSTNMBR GROUP BY A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, B.WB_TIER_PERC, B.ITEMNMBR, C.SLPRSNID , B.WB_DATE_COMM_PROC, C.SLPRSNID HAVING (B.WB_DATE_COMM_PROC)>= #1/1/2005# And (B.WB_DATE_COMM_PROC)<= #10/31/2005# and b.SLPRSNID= '57675          ' UNION ALL SELECT A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, Sum(B.WB_COMM_CALC) AS SumOfWB_COMM_CALC, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, 4 AS Company, B.WB_TIER_PERC, B.ITEMNMBR FROM misown_MIS0121T as D INNER JOIN ((NBRWB_CPICH as B LEFT JOIN NBRRM00101 as A ON B.CUSTNMBR = A.CUSTNMBR) LEFT JOIN NBRRM00301 as C ON B.SLPRSNID = C.SLPRSNID) on D.POL_NBR=B.CUSTNMBR GROUP BY A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, B.WB_TIER_PERC, B.ITEMNMBR, C.SLPRSNID , B.WB_DATE_COMM_PROC, C.SLPRSNID HAVING (B.WB_DATE_COMM_PROC)>= #1/1/2005# And (B.WB_DATE_COMM_PROC)<= #10/31/2005# and b.SLPRSNID= '57675          
' UNION ALL SELECT A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, Sum(B.WB_COMM_CALC) AS SumOfWB_COMM_CALC, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, 2 AS Company, B.WB_TIER_PERC, B.ITEMNMBR FROM misown_MIS0121T as D INNER JOIN ((MGUWB_CPICH as B LEFT JOIN MGURM00101 as A ON B.CUSTNMBR = A.CUSTNMBR) LEFT JOIN MGURM00301 as C ON B.SLPRSNID = C.SLPRSNID) ON D.POL_NBR= B.CUSTNMBR GROUP BY A.CUSTNMBR, A.CUSTNAME, B.SLPRSNID, C.SPRSNSLN, B.WB_DATE_COMM_PROC, B.WB_NEWRENEW, B.WB_COMM_ID, B.WB_TIER_PERC, B.ITEMNMBR, C.SLPRSNID , B.WB_DATE_COMM_PROC, C.SLPRSNID HAVING (B.WB_DATE_COMM_PROC)>= #1/1/2005# And (B.WB_DATE_COMM_PROC)<= #10/31/2005# and b.SLPRSNID= '57675          '

 

[snerting]

I guess you can loop over your recordset and export it to a comma separated file, which in turn can be imported into excel without problems.

 

[PHV]

or an export to excel
Have a look at the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

You could go into Excel and under the "Data" tab go into "get external data" and provided you saved the SQL as a query in Access, just run the query from Excel.

 

[MrsMope]

Thanks for the replys, however I really want to keep this contained in access. What I mean is I want to create an output to excel yes, but I don't want the end user to have to do anything in excel, just open the file. As in if they click on excel for output, a save file dialog box comes up and they save the file to the appropriate location. IF they choose report then the report comes up in access, if they choose access table, then the table displays. So I really need to get this data out of the recordset and into a MS Access table. Any suggestions?

Thanks,
RB

 

[JerryKlmns]

Create a query with that looooong sql statement.

Place a combo box with available choices
Excel | Table | Report.
and a button, on the click event of which

Select Case comboBox.Value 
   Case "Excel"
     DoCmd.Transferspreadsheet acExport, acSpreadsheetTypeExcel9, "YourQueryName", "C:\Test\YourExcelName.xls"
   Case "Table"
     CurrentProject.Connection.Execute "Select YourQueryName Into YourTableName;",,129
   Case "Report"
      DoCmd.OpenReport "YourReportName", acViewPreview

 

[MrsMope]

Yes that solution would work if the query was the same, however that query changes based on the choices the end user makes.

I know the query is long.. I had no way around that, needed the info for the different companies...

 

[JerryKlmns]

And where does it change?