Serial Number - Temporarily In A Query

[Faraz A. Qureshi]

I have a table with fields:
DT, INVOICE, AMT

Now invoice numbers are different unique keys but not numeric, how to add a
fourth temporary column in a query to insert serial numbers as well like:

Serial, DT, INVOICE, AMT
1
2
3
4
....

 

[Stefan Hoffmann]

hi Faraz,

Now invoice numbers are different unique keys but not numeric, how to add a
fourth temporary column in a query to insert serial numbers as well like:

Serial, DT, INVOICE, AMT
1
2
3
4

Using DCount():

SELECT
*,
DCount("*",
"yourTable",
"DT <= " & Format(DT, "\#mm/dd/yyyy hh:nn:ss\#")
) AS OrderNo
FROM yourTable

This may produce multiple equal serial numbers, if your date field is
not covered by an unique index.

mfG
--> stefan <--

 

[Faraz A. Qureshi]

Sorry Stefan,

But the DT is not a Date Field!

 

[Stefan Hoffmann]

hi Faraz,

Sorry Stefan,

But the DT is not a Date Field!

Which in fact leads us to the question: Why don't you post a concise and
complete example? Instead of letting us guess.


mfG
--> stefan <--

 

[John Spencer]

Why do you need the sequential number?

If you just want a line number in a report, it is very easy to handle this in
the report.

If you want a sequential number what is it based on? Are the invoice numbers
in alphabetic order and you want to use that order to assign the "serial"
number?

Please help us help you by supplying data types, table and field names, and a
few sample records along with the desired result.

You might try searching for "ranking query" to see some examples.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Faraz A. Qureshi]

Why do you need the sequential number?

Simply to use DSUM based on the basis of the same.

If you just want a line number in a report, it is very easy to handle this in
the report.

Thanx John but it is the query which I want to use in multiple outputs,
forms, queries & reports.

If you want a sequential number what is it based on?

It is to be based on the order in which the entries were recorded.

Are the invoice numbers
in alphabetic order and you want to use that order to assign the "serial"
number?

No they are alphanumeric based upon the type of entry

Please help us help you by supplying data types, table and field names, and a
few sample records along with the desired result.

datatypes of all the fields are text and table name is Orders. Samples of
few entries are as follows:

DT (Domicile Tracking ID); INV; AMT

Agent I; I-091201-A001; 60,000
Agent II; II-091031-A001; 50,000
Agent II; I-100111-A001; 40,500
Agent III; I-100201-A001; 90,750
Agent I; I-100228-A001; 66,442

You might try searching for "ranking query" to see some examples.

Thanx but that did not help as well!

Sorry 4 inconvenience of any sort buddy. Thanx anyway.

 

[Faraz A. Qureshi]

Sorry for inconvenience of any sort buddy. No doubt, should have had taken
care for a point of view from your direction. It was actually "Domicile
Tracking ID".

Sure feel regretful Stefan. No hard feelings, I hope?

Thanx anyway.

 

[John Spencer]

Unless you have some field (or fields) of data stored in EACH record that
indicates the entry order of the records then you cannot calculate a "serial
number" that will indicate that order.

Records in a relational database have no built-in order. With your example
records, there seems to be no way to determine by looking at the records which
record is first, second, third, etc.

============================ AN OPTION TO TRY =============================
One thing that MIGHT work is to add an autonumber field to the table or import
the records into another table that has the following structure.

RecNo - A sequential autonumber field
DT
Inv
Amt

With a little bit a luck, appending the existing records from your table to
this table will give you sequential numbers to work with and the records will
be in "entry" order. No guarantees on this at all.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County