Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Union select query (visual studio 2003)

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Union select query (visual studio 2003)

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

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union select query (visual studio 2003)

    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...

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Union select query (visual studio 2003)

    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Union select query (visual studio 2003)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union select query (visual studio 2003)

    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.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Union select query (visual studio 2003)

    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?)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Union select query (visual studio 2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •