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

 

[Guest]

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]

Hi Ken,

Thanks once again. I managed to get the numbering with the exception that
I'm not getting the nubers in the order I need: according to date in
ascending order. Most probably I am using the wrong fields for wrong
purposes. The sql of the query is:
SELECT (SELECT COUNT(*)
FROM zqry_har_sch_uni AS T2
WHERE T2.sira <= T1.sira AND ( T2.tarih <= T1.tarih OR T2.sira <>
T1.sira)) AS RowCounter, T1.fuar, T1.katilim, T1.firma, T1.tarih, T1.izahat,
T1.belge, T1.borc, T1.alacak
FROM zqry_har_sch_uni AS T1
ORDER BY T1.tarih;

Might sound confusing so;
sira is the autonumber field data (where I have duplicates as I explained in
previous post)
tarih is date
fuar is the fair id which is the same for all companies/entries
katilim is another number field (unique for each company)
firma is the co_id field
izahat is text field (description)
belge is a text field for document type
borc is the debit field
alacak is the credit field

The zqry_har_sch_uni is the union query I had initially asked the row
numbering for.

Regarding your question; yes it is hassle free in a report but I need to
display the series of data per company on a continuous form where the user
can also select/enter a cut-off date to see the corresponding balance for
that company. Then if necessary, a report is printed. The main reason for
the row numbering is to be able to get a running sum on the form. That's how
it all started.

Unfortunately, I don't happen to have a unique identifying field for each
row. Since this is a transaction type of data there are several occurences
of the co_id, there are several transactions having the same date, fair_id
is the same for all rows, and the katilim is just like the co_id occurs
several times and finally sira does have duplicates since it is the combined
colum of 2 tables' autonumber id fields.

I'm trying to figure which field I should use in the above sql to get the
row_num to run sequentially based on the date sorting.

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