Re: Select Qry: Differing results via Access & ADO (& SQL Server)

From: Ray at <%=sLocation%> [MVP] (%=sLocation%)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 07:57:57 -0500

So you aren't seeing [Rank] from your query in your ASP pages? What code
are you using to display the recordset values? 

-- 
Ray at home
Microsoft ASP MVP
"CJM" <cjmwork@yahoo.co.uk> wrote in message
news:%234$MKx5%23DHA.3668@TK2MSFTNGP09.phx.gbl...
> I have a query which produces different results in the Access query
builder
> and in an ASP page (via ADO)
>
> An example of the query is:
> ----------------------------------------------------------
> Select 'Ranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
> H.HotelID, Rank
>     from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
>     Inner Join Locations L on P.LocID = L.LocID
> where Rank is not null and Rank > 0 and L.LocID=2
>
> Union
>
> Select 'Unranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
> H.HotelID, Rank
>     from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
>     Inner Join Locations L on P.LocID = L.LocID
> where (Rank is Null or Rank = 0) and L.LocID=2
>
> Order By Source, Rank
> ----------------------------------------------------------
>
> In Access, I get the following results
>
> Ranked, Hotel1, [other fields], 1
> Ranked, Hotel2, [other fields], 2
> Ranked, Hotel3, [other fields], 3
> Unranked, Hotel4, [other fields], 0
> Unranked, Hotel4, [other fields], 0
> Unranked, Hotel4, [other fields], 0
> etc...
>
> In my ASP Page I get these results:
>
> Unranked, Hotel1, [other fields],
> Unranked, Hotel2, [other fields],
> Unranked, Hotel3, [other fields],
> Unranked, Hotel4, [other fields],
> Unranked, Hotel4, [other fields],
> Unranked, Hotel4, [other fields],
> etc...
>
> I've imported the Access DB to SQL Server, and in Query Analyser and in
the
> ASP page I get the right result:
> Ranked, Hotel1, [other fields], 1
> Ranked, Hotel2, [other fields], 2
> Ranked, Hotel3, [other fields], 3
> Unranked, Hotel4, [other fields], 0
> Unranked, Hotel4, [other fields], 0
> Unranked, Hotel4, [other fields], 0
> etc...
>
> (ie Same as in Access)
>
> I'm assuming that if Access itself doesnt object to the SQL, it must be
> right. Therefore, the problem lies with ADO and the Access OLEDB
drivers...
>
> Any ideas?
>
> Chris
>
>

Relevant Pages

  • Re: ranking calculated fields in a query
    ... I needed to create 3 queries such as you reffered to as qRank. ... Each 'Rank' query uses the ... FROM (ModelingXTab INNER JOIN Twirlers ON ModelingXTab.= ...
    (microsoft.public.access.queries)
  • Re: ranking calculated fields in a query
    ... I needed to create 3 queries such as you reffered to as qRank. ... Each 'Rank' query uses the associated ... FROM (ModelingXTab INNER JOIN Twirlers ON ModelingXTab.= ...
    (microsoft.public.access.queries)
  • Re: ranking calculated fields in a query
    ... resolving the ties - I can't help any more with this. ... the rank to appear the way you need it. ... I will look at the other query and reply later. ... FROM (ModelingXTab INNER JOIN Twirlers ON ModelingXTab.= ...
    (microsoft.public.access.queries)
  • Re: Select Qry: Differing results via Access & ADO (& SQL Server)
    ... So you aren't seeing [Rank] from your query in your ASP pages? ... Inner Join Locations L on P.LocID = L.LocID ...
    (microsoft.public.inetserver.asp.db)
  • Re: ranking calculated fields in a query
    ... Make the following query and save it as qRank ... FROM tblTwirlers INNER JOIN (tblFees INNER JOIN tblModellingXTab ON ... SELECT Q.FirstName, Q.AgeGroup, Q.R3, FROM qRank As Q1 ... FirstName AgeGroup R3 Rank ...
    (microsoft.public.access.queries)
Loading