query unique count for field

[Guest]

I am trying to count a sorted number field in a query. For each identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

Create a TOTALS query. Edit the SQL below with your table and field names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;

 

[Guest]

I'm not sure this does what is required here. The SQL will group and count
each record, but I understand that a sequential running sum is required for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

Create a TOTALS query. Edit the SQL below with your table and field names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;

I am trying to count a sorted number field in a query. For each identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[John Spencer]

Unless you have some other field to determine the ranking order of your same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was static
you could write some VBA to step through the recordset and assign the value
to a (new) field in your table.

I'm not sure this does what is required here. The SQL will group and count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

In Excel, I would do this by counting how many times a particular record has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was static
you could write some VBA to step through the recordset and assign the value
to a (new) field in your table.

I'm not sure this does what is required here. The SQL will group and count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[John Spencer]

As I said in my first reply-
You need another value to determine the order. For instance, if you had a
primary key in the rows (an autonumber perhaps) then you could do it.
PK Number Count(Position)
22 222 1
44 222 2
67 222 3
12 343 1


SELECT [Number],
(SELECT Count(T.PK)
FROM SomeTable as T
WHERE T.[Number] = SomeTable.[Number]
AND T.PK<=SomeTable.PK) as Position
FROM SomeTable
ORDER BY [Number], 2

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.

I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

Thanks John, I am using a defined table to run the query. The number(222,etc)
is a field in the table. How do I set this autonumber up with primary key .
Do I need another table ???

thank you

As I said in my first reply-
You need another value to determine the order. For instance, if you had a
primary key in the rows (an autonumber perhaps) then you could do it.
PK Number Count(Position)
22 222 1
44 222 2
67 222 3
12 343 1


SELECT [Number],
(SELECT Count(T.PK)
FROM SomeTable as T
WHERE T.[Number] = SomeTable.[Number]
AND T.PK<=SomeTable.PK) as Position
FROM SomeTable
ORDER BY [Number], 2

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Michel Walsh]

Hi,


If you can get the maximum count(*) per group, another solution is to make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn, nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.

I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

Thanks Michel. That worked, and I understood it even with my limited Access
skills. I managed to do it all without using SQL. I hope this was also useful
to flc123. This saves me so much time.

Hi,


If you can get the maximum count(*) per group, another solution is to make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn, nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

Thanks, I will give it a try.

Thanks Michel. That worked, and I understood it even with my limited Access
skills. I managed to do it all without using SQL. I hope this was also useful
to flc123. This saves me so much time.

Hi,


If you can get the maximum count(*) per group, another solution is to make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn, nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

:

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

flc123

Just to save time, and maybe if like me, you have limited Access skills. I
found it easier to make the driver table in Excel, in A1 enter 1, Cell A2
(=A1+1), and then fill down from A2 to line 10,000, (or whatever your limit
might be). Paste that column into a new Access table and make it the primary
key.

Create a new query that totals all your groups.

Create a second that joins the new totals query to your pasted table. Don't
join the tables. From your new total query, drop in your Group and Total
fields, and then your driver number field from the pasted table. In that
field's criteria, enter <= Total (from new query).

I know that's what Michel said, and he made it very clear, but I'm trying to
show how it's done with drag and drop. Hope you manage it OK.

 

[Guest]

Please forgive me for hijacking flc123's thread, but I think we are both
trying to achieve the same goal, and so may be experiencing similar problems.

Having succeeded with Michel's advice, I now have a query with the correct
group numbering, but am having problems linking this query back to the query
holding specific information on each row within the groups. How do I go about
applying my new numbers to the relevant query record?

Hi,


If you can get the maximum count(*) per group, another solution is to make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn, nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Michel Walsh]

Hi,



If you have other columns that make each record different, in reality, you
can use them to "rank" your data.


You also have another possibility using a temporary table: append the data
into a temp table that has all the fields of the first table, plus an
autonumber field (autoincrement by 1). Start with such a temp table empty of
records, then append the existing data, sorted:


query1:

SELECT *
FROM myTable
ORDER BY f1


is sorting the data, then execute something like:

INSERT INTO temp( f1, f2, ...., fn) SELECT f1, f2, ..., fn FROM query1

to fill the temp table. This table temp will then have all its record
numbered from 1 to N, in its autonumber field,


Get the minimum value for each value of the groups:


query2:

SELECT f1, MIN(autonumberFieldName) as minGroup
FROM tempTable
GROUP BY f1




and then:


SELECT tempTable.f1, f2, ..., fn, 1+(autonumberFieldName-minGroup)
FROM tempTable INNER JOIN query2
ON tempTable.f1 = query2.f1


will finally supply the desired result.



Hoping it may help,
Vanderghast, Access MVP

Please forgive me for hijacking flc123's thread, but I think we are both
trying to achieve the same goal, and so may be experiencing similar
problems.

Having succeeded with Michel's advice, I now have a query with the correct
group numbering, but am having problems linking this query back to the
query
holding specific information on each row within the groups. How do I go
about
applying my new numbers to the relevant query record?

Hi,


If you can get the maximum count(*) per group, another solution is to
make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn,
nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table
Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP

In Excel, I would do this by counting how many times a particular
record
has
occured above that row using Countif, and then adding one to the
result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group,
but
without including the records below?

:

Unless you have some other field to determine the ranking order of
your
same
values, I know of no way to display the results you want using SQL.
You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is
required
for
each record within a group. This should be fairly simple, but I've
been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and
field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField)
AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

John,

Having reached a dead end with later methods in this thread, I am trying to
make your idea work. Up to now, I have managed to reach a reasonable level in
Access without ever having to resort to SQL as most of the features I use are
available without needing SQL, and I am having difficulties fully
understanding your code. I have spent hours trying to juggle it different
ways and to understand what I am supposed to replace your table references
with.

I think the second line is what causes me problems. I take it the leading
bracket was a typo, but it won't accept anything like SELECT Count (T.PK). Is
T a variable, or is it the table name? I have tried [PK], Tablename.[PK],
[Tablename.PK], [Tablename!PK], and many others. It may seem obvious to many
in here, but would it be possible to shed some light on where I am going
wrong. I feel the answer is almost within my grasp.

Many thanks

As I said in my first reply-
You need another value to determine the order. For instance, if you had a
primary key in the rows (an autonumber perhaps) then you could do it.
PK Number Count(Position)
22 222 1
44 222 2
67 222 3
12 343 1


SELECT [Number],
(SELECT Count(T.PK)
FROM SomeTable as T
WHERE T.[Number] = SomeTable.[Number]
AND T.PK<=SomeTable.PK) as Position
FROM SomeTable
ORDER BY [Number], 2

In Excel, I would do this by counting how many times a particular record
has
occured above that row using Countif, and then adding one to the result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group, but
without including the records below?

Unless you have some other field to determine the ranking order of your
same
values, I know of no way to display the results you want using SQL. You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is required
for
each record within a group. This should be fairly simple, but I've been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField) AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Guest]

I'm afraid I'm still struggling with this. From your valuable help, I now
have a table of records, then a grouping of those records, and then a query
with the same number of records as the first table, but with sequential
numbers for each group. I am trying to apply this new grouped sequence to the
first set of records. I am not looking for a 1 to n sequence across the whole
set, but the sequence must reset at the satart if each group.

I am encouraged that the two tables I wish to join have an equal number of
records, but every time I try to join them by various methods, I get a
multiple of records for each of the records in the original table. Or if I
manage to get the correct number of records, certain records are repeated. I
have an ID field in the original table, but I can't find how to go about
ranking this and then applying the new number to my ranked record.

Am I doing something obvious? Please bear in mind I'm not familiar with SQL
although I will try, but I do work extensively with Functions.

Thanks

Hi,



If you have other columns that make each record different, in reality, you
can use them to "rank" your data.


You also have another possibility using a temporary table: append the data
into a temp table that has all the fields of the first table, plus an
autonumber field (autoincrement by 1). Start with such a temp table empty of
records, then append the existing data, sorted:


query1:

SELECT *
FROM myTable
ORDER BY f1


is sorting the data, then execute something like:

INSERT INTO temp( f1, f2, ...., fn) SELECT f1, f2, ..., fn FROM query1

to fill the temp table. This table temp will then have all its record
numbered from 1 to N, in its autonumber field,


Get the minimum value for each value of the groups:


query2:

SELECT f1, MIN(autonumberFieldName) as minGroup
FROM tempTable
GROUP BY f1




and then:


SELECT tempTable.f1, f2, ..., fn, 1+(autonumberFieldName-minGroup)
FROM tempTable INNER JOIN query2
ON tempTable.f1 = query2.f1


will finally supply the desired result.



Hoping it may help,
Vanderghast, Access MVP

Please forgive me for hijacking flc123's thread, but I think we are both
trying to achieve the same goal, and so may be experiencing similar
problems.

Having succeeded with Michel's advice, I now have a query with the correct
group numbering, but am having problems linking this query back to the
query
holding specific information on each row within the groups. How do I go
about
applying my new numbers to the relevant query record?

Hi,


If you can get the maximum count(*) per group, another solution is to
make a
join with a driver table.


Query q1:

SELECT number, COUNT(*) as c
FROM myTable
GROUP BY number



Query q2:


SELECT q1.number, iotas.itoa
FROM q1 INNER JOIN iotas
ON iotas.iota <= q1.c




where table Iotas has a single field, iota, with values from 1 to nnn,
nnn
being the largest count you can expect.


You are not obliged to fill by hand such a table. Instead, make a table
Ds,
one field, d, with 10 records with values from 0 to 9. Make a query:

SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
FROM ds, ds As ds_1, ds As ds_2, ds As ds_3


will nicely to the job, but make a table, Iotas, out of it, and index the
field iota (or make it a primary key). That takes 3 minutes and you have
your Iotas table filled with values from 1 to 10 000. Much faster than
typing all that data, or even, these instructions about how to do it.


Hoping it may help,
Vanderghast, Access MVP



In Excel, I would do this by counting how many times a particular
record
has
occured above that row using Countif, and then adding one to the
result. I
suppose Access has the advantage of grouping which should help. But is
there
any way Access can look UP the query to count records within a group,
but
without including the records below?

:

Unless you have some other field to determine the ranking order of
your
same
values, I know of no way to display the results you want using SQL.
You
could probably write a VBA function to do this or if your order was
static
you could write some VBA to step through the recordset and assign the
value
to a (new) field in your table.


I'm not sure this does what is required here. The SQL will group and
count
each record, but I understand that a sequential running sum is
required
for
each record within a group. This should be fairly simple, but I've
been
puzzling over it for weeks. Anyone able to help?

:

Create a TOTALS query. Edit the SQL below with your table and
field
names.

SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField)
AS
CountOfNumber
FROM [YourTable]
GROUP BY YourTable.YourNumberField;


:

I am trying to count a sorted number field in a query. For each
identical
number I want the count to increment by one.
For example

Number Count
183 1

222 1
222 2
222 3

343 1


thanks

 

[Michel Walsh]

Hi,

I'm afraid I'm still struggling with this. From your valuable help, I now
have a table of records, then a grouping of those records, and then a
query
with the same number of records as the first table, but with sequential
numbers for each group. I am trying to apply this new grouped sequence to
the
first set of records. I am not looking for a 1 to n sequence across the
whole
set, but the sequence must reset at the satart if each group.

You have it right up to that point. From there, you stop to follow the
suggestion I made, it seems.

If your temp table is like:

auto f1 f2
1 Utah 5050
2 Utah 5051
3 Utah 5052
4 Kansas 10
5 Kansas 22
6 Kansas 27
7 Kansas 32


then query2, see my original post, should return


f1 minGroup
Utah 1
Kansas 4


and the last query would produce:


1+auto-minGroup f1 f2
1 Utah 5050
2 Utah 5051
3 Utah 5052
1 Kansas 10
2 Kansas 22
3 Kansas 27
4 Kansas 32




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel, Sorry for the delay replying. I eventually managed to do this in the
design grid. Once I figured out the logic, it wasn't too bad. Thanks very
much for your help.