Union select query (visual studio 2003)

[ase001]

Without a vs forum to post in I guess this is the best choice.

aspx web apges created via vs 2003
using the following code
[pre]
Dim oleAdapt As New OleDbDataAdapter("SELECT " & _
"reporttoname, sum(finalsales) AS YTDSales, " & _
"sum(0) as MTDSales FROM tbl_GMRData WHERE AccLedgerYear=2005 " & _
"UNION SELECT ALL reporttoname, sum(0) AS YTDSales, " & _
"sum(finalsales) as MTDSales FROM tbl_GMRData WHERE AccLedgerMonth=5 GROUP BY reporttoname", objConn5)

the page returns an error but when I use just the single select statement I get a result. So I know that my connections all work but with any type of union select statement the page errors.
The tbl_GMRData is an access table.

Any help would be greatly appreciated.
TIA
Alan
Cheshire

[rory]

Use <code>UNION ALL SELECT</code> rather than <code>UNION SELECT ALL</code> and see if that fixes it.

[MarkJ]

Hi Alan,

Can you provide more details on the error you're getting?

Are you able to use this EXACT query to return results in Access?

Also, if I may make a suggestion... It's generally not a good practice to hard code the SQL statement. You might consider creating this as a query in the database and referencing that as the Select source of your data adapter. That way you won't have to recompile and redeploy your application when your query changes - you can simply change it in the database.

Just some ideas for thought...

[ase001]

Hi Rory, Mark...
The "UNION ALL SELECT" had no discernible effect on the result. Still errors out with...No value given for one or more required parameters

The deployment of the query in access first was where I started. And come to think of it, I guess that's where I need to leave it because of the traffic implications.
But if I can get the union working correctly then I can use local parameters as entered in the browser as the parameters for my query e.g. have the user report on month 4 instead of 5. But thinking about it, I could return ALL of the rows, use the parameter to filter out only the relevant ones and then do the grouping on the page?
Hmmmmmm, could be on to something there Mark -leave a union query in the database but don't filter/group till it gets to the page....could be a solution.

Thanks guys, fresh pairs of eyes prompts me to look from different angle.

Cheers
Alan
Cheshire, UK

[rory]

Sorry, I just noticed you appear to be trying to use one GROUP BY clause to affect the whole Union query, which you can't do. You have to use the Group By within the individual queries, which I guess doesn't achieve what you want.

[MarkJ]

I haven't worked with this lately, but I know there's a way you can set parameters in the Access query and pass the values to Access based on user input.

You'll want to research this to find the exact implementation details. That's probably the best way to go about this.

[rory]

I think that to get the overall totals that you want, you would need a SQL string like:
<code>SELECT [Q].reporttoname, Sum([Q].YTDSales) as Sales_YTD, Sum([Q].MTDSales) as Sales_MTD FROM (SELECT reporttoname, sum(finalsales) AS YTDSales, sum(0) as MTDSales FROM tbl_GMRData WHERE AccLedgerYear=2005 GROUP BY reporttoname UNION ALL SELECT reporttoname, sum(0) AS YTDSales, sum(finalsales) as MTDSales FROM tbl_GMRData WHERE AccLedgerMonth=5 GROUP BY reporttoname) As [Q] GROUP BY [Q].reporttoname</code>

Edit: I suspect you also need an additional criterion in the second SELECT statement in the UNION query, since currently it will return anything where the month is 5, regardless of the year. (unless that is what you wanted?)

[ase001]

Many thanks Rory and Mark, the union works fine and I figure I was getting word blind after a couple of hours.
I've also implemented a separate query in the root database to produce further info as per Marks suggestion.
Weekend beckons.
Have a nice one
Alan
Cheshire