Re: Need query field assigning line number for each found record
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Wed, 11 May 2005 05:57:50 -0400
Hi,
Yes, if the join does not duplicate it, in the result.
Hoping it may help,
Vanderghast, Access MVP
"Ray S." <RayS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E30C2B8E-F2AE-491B-9AC7-0221EA367720@xxxxxxxxxxxxxxxx
> Thanks Michael. As it turns out no one of the queried accounting fields is
> totally unique. Would you suggest I call up a totally unique auto-numbered
> ID
> field form an underlying table?
>
> "Michel Walsh" wrote:
>
>> Hi,
>>
>> You can rank your records (first, second, 3, 4, ... ) You need one (or
>> some) field that makes the ranking unique, without ex-equo.
>>
>>
>> SELECT a.f1, a.f2, a.f3, COUNT(*) As rank
>> FROM myTable As a INNER JOIN myTable As b
>> ON a.f1>b.f1 OR (a.f1=b.f1 AND a.f2 >= b.f2 )
>> GROUP BY a.f1, a.f2, a.f3
>>
>>
>> as example, assuming 3 fields, but (f1, f2) are enough to specify
>> ordering.
>> If you already have a primary key:
>>
>> SELECT a.f1, a.f2, a.f3, COUNT(*) As rank
>> FROM myTable As a INNER JOIN myTable As b
>> ON a.pk >= b.pk
>> GROUP BY a.f1, a.f2, a.f3
>>
>>
>> does the trick.
>>
>>
>>
>> Hoping it may help,
>> Vanderghast, Access MVP
>>
>>
>> "Ray S." <RayS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:EE50C69B-4334-495A-98E3-8ACC0BBF5405@xxxxxxxxxxxxxxxx
>> > My query collects accounting data. Each query must be assigned a batch
>> > number
>> > which the user manually enters according to stated criteria. I need to
>> > add
>> > a
>> > field to the query that will identify each record by a line number:
>> > 1,2,3,4,5, etc. Can someone help me with either SQL or how I can do
>> > this
>> > in
>> > the query builder? I greatly appreciate your help. Just reading your
>> > discussions is an excellent education!
>>
>>
>>
.
- Follow-Ups:
- References:
- Need query field assigning line number for each found record
- From: Ray S.
- Re: Need query field assigning line number for each found record
- From: Michel Walsh
- Re: Need query field assigning line number for each found record
- From: Ray S.
- Need query field assigning line number for each found record
- Prev by Date: Re: Fetch rows given row nos
- Next by Date: Re: Passing parameter values between queries
- Previous by thread: Re: Need query field assigning line number for each found record
- Next by thread: Re: Need query field assigning line number for each found record
- Index(es):
Relevant Pages
|
