Counting records within a group of a table and updating the table

  • Thread starter Thread starter Di
  • Start date Start date
D

Di

I want to increment a field, within a sorted table,
resetting the counter to 1 when another field within the
same table changes. (I do not want to know that I have "x"
number of occurrences of a group. I want to give each item
a number within the group). I think I want to use an
update query - but I have only been successful in getting
a count of the occurences. Can you help?
 
Dear Di:

The data within most tables is sufficiently dynamic that keeping such
a Ranking value in the table is very inefficient. Instead, you should
generally derive this information as you query the table, so it is
always up-to-date. (Not storing derived information is a solid rule
of database design).

If this is acceptable, then please post a query that returns what you
want from this table. Be sure to sort the query to represent just how
you want the results to appear. Please also specify which column(s)
are used to restart the counter to 1. This must be a subset of the
columns on which you are sorting. Any other columns on which you sort
will determine the numbering withing each set based on that first set
of columns.

I will add to your query a column to product the ranking.

An improtant factor is whether your sorting is unique or not. If not,
then you may have "ties" for the ranking. These would be ranked
identically, then the subsequent rows will skip the "places" involved
in the tie. For example, a 3 way tie for first place results in 3
first place rankings (1) followed by a fourth place ranking (4). Just
like in a horse race.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Di:

Sure. I'm short on the details of your tables, but I can offer
general advice.

A query like this would return the most recent 6 appointments for a
given patient (if the appointments are recorded by date only and may
not be unique, it is possible it would return more than 6 due to
ties).

SELECT PatientName, AppointmentDate
FROM YourTable T1
WHERE AppointmentDate IN (SELECT TOP 6 AppointmentDate
FROM YourTable T2 WHERE T2.PatientName = T1.PatientName
ORDER BY AppointmentDate DESC)
ORDER BY PatientName, AppointmentDate DESC

How is this?

Now, if the 6th oldest appointment is one of two appointments for that
patient on that day, then it will appear twice, and you'll have 7
appointments listed. You could make the query DISTINCT and this would
go away. But the, if the 5th oldest appointment is one of two
appointments for that patient on that day, then you would have 6
appointmens, and the DISTINCT would cut this back to show only 5.
Basically, if there could be 2 appointments for a patient in a single
day, the thing is ambiguous and you have to live with the rules. The
only way to show 6 different dates (assuming there are at least 6) is
to use DISTINCT in both the inner and outer queries, but then you may
be looking at the 7th and 8th oldest appointments because you'd be
looking at the 6 most recent appointment dates, not the 6 most recent
appointments.

Picky, aren't we!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Di:

Microsoft has awarded this title to those who are willing and expert
in each of their product lines to render assistance not unlike what I
have done here. This is a purely volunteer activity.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top