Hi Everyone,

I have a scenario where I will need a rownum value for a query. I have
attempted to make this by doing a self join type query based on the
'original' querydef and counting the records. I am getting results
that dont make any sense and I am not sure why.

My 'base' query is made up from data that spans three tables
(category, subcategory, segment). I am selecting all segments by
subcategory by category. One big list (actually not that big, but
important). The sql [qryProductFilters] is as follows:
SELECT category.category, subcategory.subcategory, segment.segment
FROM (category INNER JOIN subcategory ON category.category_id =
subcategory.category_id) INNER JOIN segment ON
subcategory.subcategory_id = segment.subcategory_id
ORDER BY category.category, subcategory.subcategory, segment.segment;

So far so good. I am buggered if I can get an artificial rownum to
work on this query. I based another query on this one to try and
produce the rownum:

SELECT [qryProductFilters].*, (select count(*) from
[qryProductFilters] as SELF where SELF.[category] <=
[qryProductFilters].[category] AND SELF.[subcategory] <=
[qryProductFilters].[subcategory] AND SELF.[segment] <=
[qryProductFilters].[segment]) AS ROWNUM
FROM qryProductFilters
ORDER BY [qryProductFilters].[category], [qryProductFilters].
[subcategory], [qryProductFilters].[segment];

I am getting duplicate ROWNUM values and they are jumbled all over the
place to boot. I am guessing that it is related to the multiple field
'unique key' . Does anyone have a workaround for this? Another
approach perhaps? I need to avoid a temp table if I can FWIW.

Any guidance greatly appreciated

Cheers

The Frog

A little more info on this that comes to mind. The reason for the
artificial rownum is to get around the 'query too complex' stuff with
Access. I actually dont know that my approach is right in the first
place. The bigger picture is this:

I have a series of channels (sales channels), a listing a which
products belong to which channel, and the products conform to the
category subcategory segment normalised structure mentioned in the
queries above in my previous post. What I need to produce, as a final
result, is a listing of the products that belong in each channel with
the channels as the columns. The lists will be of different lengths as
not every channel carries every product. The category structure is
basically used as the filters to hone in on what line of products you
wish to see.

I thought about a crosstab query approach, but have been unable to get
it to work (thus far).

Thats the bigger picture, if it helps :-)

Cheers

The Frog

The Frog wrote:

In your base query up you are linking catId to subcat catid and then
subcategoryID subcatid on segments subcatid. Then you sort on the
alphas fields. Then it appears you attempt to get a rownum from the
result set in another query.

I think I'd want a "where categoryid = [categoryid] and subcategoryid =
[subcategoryid] and segment <= [segment].

IOW, add to the base query to contain the cat and subcat ids that are
the same and can be compared and since the segment is sorted alpha then
count is based on <=. Or change the <= on category and subcategory to
just =. Then again, it's late here and I be misreading the whole thing.

On Aug 10, 5:34*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
Maybe:

http://groups.google.com/group/comp....9960a085d2f378

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Thanks for that James, I have bookmarked the url for future reference.
Thats a better explanation than the one I currently have. I appreciate
it.

The Frog

The Frog wrote:

Just curious. Is this for a report or form? If report you could use a
running sum to break on bands and not bother with it.