Hello, trying to run a query to create a sequential number by group on MS Access. I have tried the suggestions from several sites and they all result in a total by group or simply a '1' for all records. I cannot find what I am doing wrong:
Query ONE:
SELECT a.site_id, a.sample_unit, Count(*) AS rank
FROM zTable AS a INNER JOIN zTable AS b ON (a.sample_unit = b.sample_unit) AND (a.site_id=b.site_id)
GROUP BY a.sample_unit, a.site_id;
results:
site_id sample_unit rank
4482 96362 1
4495 96362 1
4505 96362 1
4506 96400 1
4507 96400 1
4508 96500 1
another attempt:
Query TWO:
Select sample_unit, site_id
,(select count(*)
from zTable T1
where t1.sample_unit = T.sample_unit)
as rank
from zTable T
order by sample_unit
,site_id;
returns results:
sample_unit sample rank
96362 4482 6
96362 4495 6
96362 4505 6
96362 4506 6
96362 4507 6
96362 4508 6
Several web sites ask this question, receive an answer (which I mimmick in my sql) and then they say 'thanks, it worked' ... not one has worked for me ... I am stuck my friends and can use some help.
Thanks,
Bill
Michel Walsh wrote:
Your original table replaces the tableNanme.
23-Oct-07
Your original table replaces the tableNanme. Your real fields name replace
f1 and f2
BUT you don't touch the aliases, they are 'pointers' to the table.
Generally, we need only one 'pointer' to scan a list (as a picture,
manually, it can be as when we can slide our finger over a column - list of
elements) but here, we need two pointers (kind of using one finger from each
hand and sliding over the list with each of them, in a more or less
'complex' pattern, where the second finger moves from the top of the
column-list down to where is your first finger, then, move our first finger
down by one element, and repeat the sequence...just for a picture of why we
can use two fingers, two 'aliases', over the same list, table)
If you really want, you can change the alias a by any other name, like
firstFinger
SELECT firstFinger.f1, firstFinger.f2, COUNT(*) as Ran
FROM tableName AS firstFinger INNER JOIN tableName AS
ON firstFinger.f1=b.f1 AND firstFinger.f2>= b.f
GROUP BY firstFinger.f1, firstFinger.f
Hoping it may help
Vanderghast, Access MV
Previous Posts In This Thread:
Counting by groups
I can count records down in a group on a report. Is there a way to cont and
store in a query to start and renumber from one. I want to get a list of the
top three records in each group I am sorting by
--
Thanks
John...
Re: Counting by groups
You can rank by group
SELECT a.f1, a.f2, COUNT(*) as Ran
FROM tableName AS a INNER JOIN tableName AS
ON a.f1=b.f1 AND a.f2>= b.f
GROUP BY a.f1, a.f
will rank the records accordingly to the value of f2, by groups of f1. Note
that the SAME table is mentioned twice, with two aliases, a and b. Replace
tableName, f1 and f2 accordingly to your own design table
SELECT a.f1, a.f2, COUNT(*) as Ran
FROM tableName AS a INNER JOIN tableName AS
ON a.f1=b.f1 AND a.f2>= b.f
GROUP BY a.f1, a.f
HAVING COUNT(*) <=
will get only the first three record, for each group
Alternatively, you can use something like
SELECT a.f1, a.f
FROM tableName As
WHERE a.f2 IN( SELECT TOP 3 b.f
FROM tableName AS
WHERE b.f1=a.f
ORDER BY b.f2 DESC
ORDER BY a.f1, a.f2 DES
But this can be slow on a large table
Vanderghast, Access MV
I think I get the concept but I'm a little confused with the refrence to table
I think I get the concept but I'm a little confused with the refrence to
table a, table b and tableName.
table a - would contain my initial data values
table b - is the alias to table a
where does tableName fit in?
--
Thanks,
John...
:
Your original table replaces the tableNanme.
Your original table replaces the tableNanme. Your real fields name replace
f1 and f2.
BUT you don't touch the aliases, they are 'pointers' to the table.
Generally, we need only one 'pointer' to scan a list (as a picture,
manually, it can be as when we can slide our finger over a column - list of
elements) but here, we need two pointers (kind of using one finger from each
hand and sliding over the list with each of them, in a more or less
'complex' pattern, where the second finger moves from the top of the
column-list down to where is your first finger, then, move our first finger
down by one element, and repeat the sequence...just for a picture of why we
can use two fingers, two 'aliases', over the same list, table).
If you really want, you can change the alias a by any other name, like
firstFinger:
SELECT firstFinger.f1, firstFinger.f2, COUNT(*) as Rank
FROM tableName AS firstFinger INNER JOIN tableName AS b
ON firstFinger.f1=b.f1 AND firstFinger.f2>= b.f2
GROUP BY firstFinger.f1, firstFinger.f2
Hoping it may help,
Vanderghast, Access MVP
Submitted via EggHeadCafe - Software Developer Portal of Choice
Web Services - Server To Server With SOAP
http://www.eggheadcafe.com/tutorial...0ae-216f61592d79/web-services--server-to.aspx