Getting recordcount from resultset of 'union' query

[Marja Ribbers]

Hi,

I need to find out the number of records in the resultset of a union of 2 queries.

E.g.

"select ID from Pages where numPageCatID = " & p_iCatID & _
" and ID not in (1,2,3,4)" & _
" union" & _
" select ID from Pages" & _
" inner join UserCatPages on UserCatPages.numPageID = Pages.ID" & _
" where UserCatPages.numUserCatID in (12,3)"

I am now building a recordset and using l_oRs.GetRows() to get the number of records, and that works okay.

But is it possible to somehow use the Count() function in the SQL query above?

--
Marja Ribbers-de Vroed

Internet:
- [url]www.webwaresystems.nl[/url]
- [url]www.clubwebware.nl[/url]
- [url]www.flevooware.nl/dreamweaver[/url]

[Kindler Chase]

Marja Ribbers wrote:

> Hi,
>
> I need to find out the number of records in the resultset of a union
> of 2 queries.
>
> E.g.
>
> "select ID from Pages where numPageCatID = " & p_iCatID & _
> " and ID not in (1,2,3,4)" & _
> " union" & _
> " select ID from Pages" & _
> " inner join UserCatPages on UserCatPages.numPageID = Pages.ID" & _
> " where UserCatPages.numUserCatID in (12,3)"
>
> I am now building a recordset and using l_oRs.GetRows() to get the
> number of records, and that works okay.
>
> But is it possible to somehow use the Count() function in the SQL
> query above?

Marja,

Are you just trying to count the total records? Or are you trying to count
something more specific within the recordset? If just the total records,
use the UBound function (+1) once the records are brought into an array from
the GetRows function.

--
kindler chase
[url]http://www.ncubed.com[/url]
Home of SuperInvoice: The Online Invoicing Application.
Organize your billing process and impress your clients.

[url]news://news.ncubed.com/support[/url]
n3 Support Group

[Marja Ribbers]

Hi Kindler,

Yes, as I already mentioned I am currently using the GetRows() method (with the UBound() function).
But I was wondering if it could be done otherwise.

--
Marja Ribbers-de Vroed

Internet:
- [url]www.webwaresystems.nl[/url]
- [url]www.clubwebware.nl[/url]
- [url]www.flevooware.nl/dreamweaver[/url]


"Kindler Chase" <weaver@DELETEME_roubaixinteractive.com> wrote in message news:d5iv64$gf6$1@forums.macromedia.com...

> Marja Ribbers wrote:

>> Hi,
>>
>> I need to find out the number of records in the resultset of a union
>> of 2 queries.
>>
>> E.g.
>>
>> "select ID from Pages where numPageCatID = " & p_iCatID & _
>> " and ID not in (1,2,3,4)" & _
>> " union" & _
>> " select ID from Pages" & _
>> " inner join UserCatPages on UserCatPages.numPageID = Pages.ID" & _
>> " where UserCatPages.numUserCatID in (12,3)"
>>
>> I am now building a recordset and using l_oRs.GetRows() to get the
>> number of records, and that works okay.
>>
>> But is it possible to somehow use the Count() function in the SQL
>> query above?

>
> Marja,
>
> Are you just trying to count the total records? Or are you trying to count
> something more specific within the recordset? If just the total records,
> use the UBound function (+1) once the records are brought into an array from
> the GetRows function.
>
> --
> kindler chase
> [url]http://www.ncubed.com[/url]
> Home of SuperInvoice: The Online Invoicing Application.
> Organize your billing process and impress your clients.
>
> [url]news://news.ncubed.com/support[/url]
> n3 Support Group
>
>

[Kindler Chase]

Marja Ribbers wrote:

> Hi Kindler,
>
> Yes, as I already mentioned I am currently using the GetRows() method
> (with the UBound() function).
> But I was wondering if it could be done otherwise.

Yes, it can be done. However, you'd have to create a sub-query and would be
less efficient than using the UBound function.

--
kindler chase
[url]http://www.ncubed.com[/url]
Home of SuperInvoice: The Online Invoicing Application.
Organize your billing process and impress your clients.

[url]news://news.ncubed.com/support[/url]
n3 Support Group