Adding an extra feild to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to put a query together that extracts some records from a table,
but I would like the Query Design grid to add an extra feild that adds a
counter to each record incrementing by 1.

For example, the new feild created in the query is called : RecInc

If 72 records were selected, the RecInc will = 1 for the first record, 2 for
the second record, 3 for the third record etc

Does anyone know of a function in Access that can do this?

Thanks
 
Dear GLT:

A report will do this nicely.

A query MAY be able to do this if the rows produced are sorted uniquely. If
this is the case, send details. Give me the SQL includeing and ORDER BY
that uniquely orders the rows and I will add the Ranking column for you and
explain how it is done.

Be aware that this numbering will not be preserved when anything in the
table changes - adding new rows, deleting rows, or updating anything that
affects the order of the rows. Each time you run this query (or report) the
numbering may change.

Tom Ellison
 
Hi Tom,

The records produced are in a Subform, not a report. I have an Auto number
feild (ie. recNo) in the original table, is this what you mean by Uniquly
ordering the rows?

I would like the subform to re-calculate this number everytime it is
displayed ...

Is it possible for a subform (based on a query) to do this?
 
Dear GLT:

Are the rows on your subform sorted, or just thrown up in any order? If
they are sorted, is the sorting unique? If there is no systematic order,
then everything is just arbitrary, and nothing can be done short of imposing
a random order and ranking on that. Even that may sometimes not be unique.

Unique ordering and ranking are two sides of the same coin. If you do not
have unique ordering, then you cannot rank uniquely.

Tom Ellison
 
Hi Tom,

Here is the SQL that I currently am using:

SELECT OP_IncDet.IncRecNo, OP_IncDet.IncDat, OP_IncDet.IncTim,
OP_IncDet.IncSum
FROM OP_IncDet
ORDER BY OP_IncDet.IncRecNo;

Regarding sorting, I am using the primary key (IncRecNo) which is an
Autonumber, and has been set to Index = Yes (No Duplicates).

Is this what I need to do?

Thanks...
 
Dear GLT:

SELECT IncRecNo, IncDat, IncTim, IncSum,
(SELECT COUNT(*)
FROM OP_IncDet T1
WHERE T1.IncRecNo < T.IncRecNo) + 1
AS Rank
FROM OP_IncDet T
ORDER BY IncRecNo;

I believe this will give what you want.

Be aware that if you delete a row from the table, all the subsequent rows
will have a new Rank value.

Tom Ellison
 
GLT said:
Hi,

I am trying to put a query together that extracts some records from a
table,
but I would like the Query Design grid to add an extra feild that adds a
counter to each record incrementing by 1.

For example, the new feild created in the query is called : RecInc

If 72 records were selected, the RecInc will = 1 for the first record, 2
for
the second record, 3 for the third record etc

Does anyone know of a function in Access that can do this?

Thanks
Click to expand...


Why don't you just create the desired query to pull your information, then
build a report on that query.

Then add a unbound text box to the report and go into the "all tab", set the
following:
Control Source: =1
Running Sum - Over Group

then when you open the report it will count all the records that the
underlying query found.
This might be the simplest method for your problem.

Take Care,
_Bigred
 
Back
Top