Hi, I am connecting to an Excel workbook via ADO using provider Microsoft.ACE.OLEDB.12.0. The workbook has two sheets with similar fields, but the second sheet has an additional field, "Price". On the first sheet rows have a unique identifier column named "JobNo", but on the second sheet the rows are not uniquely identified by this field, in other words more than 1 row can have the same JobNo. I want to create a summary query on the second sheet that will group data by the "JobNo" field, and then join this query to the first sheet. Something like this:
<code>
strSQL = "SELECT [Sheet1$].[JobNo], tbl.Sum1 FROM [Sheet1$] LEFT JOIN (SELECT [Sheet2$].[JobNo], SUM([Sheet2].[Price]) FROM [Sheet2$] GROUP BY [Sheet2$].[JobNo]) tbl ON [Sheet1$].[JobNo] = tbl.JobNo"
rs.open strSQL, cn
</code>
When I step through the code this line produces a run-time error: "No value given for one or more required parameters.". Does anybody have experience of joining a Sheet with a Select statement in a query?
Sorry I don't know what the tags are for code.
Bookmarks