Adding an autonumber field in a query?

[Alp Bekisoglu]

Hi Experts,

Is there a way to add an autonumber field to a query in Access? What I need
to get is an equivalent of a report unbound field set to =1 and running sum.
I need the query to return i.e.:
1 fieldA fieldB fieldC
2 fieldA fieldB fieldC
3 fieldA fieldB fieldC
4 fieldA fieldB fieldC
....

Just coudn't figure out how to... I couldn't get a result from my searches
as well.

Thanks in advance.

Alp

 

[Ken Sheridan]

Alp:

It can be done subject to two conditions; (1) the query is ordered by a
column(s), (2) the underlying table has a unique key column (YourID in the
example below) such as an autonumber, e.g.

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.FieldA <= T1.FieldA
AND ( T2.YourID <= T1.YourID
OR T2.FieldA <> T1.FieldA)) AS RowCounter,
T1.fieldA, T1.fieldB T1.fieldC
FROM YourTable AS T1
ORDER BY T1.FieldA, T1.YourID;

With queries drawing on more than one table it can get a little more
complex, but the principle is the same. Its easier to create an unnumbered
query then base a second query on this rather than doing it all in the one
query.

Ken Sheridan
Stafford, England

 

[Alp Bekisoglu]

Hi Ken,

Thanks for your response. My case IS actually on a bit trickier side; I have
(for the time being) 2 seperate tables with their own autonum id fields and
the query I need the sequential numbering is a Union query. Observing that
the query was not picking up seemingly duplicate entries in one table I
added the id fields in both tables. Now I do have a numbering field but with
same numbers coming from the two tables.
To further complicate the problem, in a near future there will be 3 more
tables joining these in the form of a sum from each new table. As you would
have quessed these also do have their own id fields.
The whole thing needs to be sorted as ascending based on the date field in
each table!
To give some more info data pulled from the tables are:
tablePaymentPlan - co_id, date, description, DebitAmount, CreditAmount(as 0)
tablePayments - co_id, date, description, DebitAmount(as 0), CreditAmount
to be incorporated(briefly):
tableMaterials - co_id, date, description, DebitAmount, CreditAmount(as 0)
<< a one-liner sum per company
tableAdverts - co_id, date, description, DebitAmount, CreditAmount(as 0) <<
a one-liner sum per company
tableServices - co_id, date, description, DebitAmount, CreditAmount(as 0) <<
a one-liner sum per company

One problem is to incorporate a line_no for the sum rows coming from the
final 3 tables.
As I indicated; there is a line_no included for the PaymentPlan and Payments
tables.

Hope the above makes some sense so you could be able to guide me a little
more on this since I guess my brain has gone for a vacation.

Thanks again.

Alp

 

[Alp Bekisoglu]

Ken,

First sorry for not responding earlier.
Then, yes, language is Turkish.
FInally, I will get to your suggestions during the weekend since I have to
rush a brochure design! I will get back to you once I do the trials and
since this post is getting a bit old, I will post refering your name in
subject.
Just in case it takes me longer than anticipated, I wish you and all in the
group who celebrate;
a Merry Christmas and a Happy New Year!

Alp