HELP we understanding how to create this subquery

  • Thread starter Thread starter John
  • Start date Start date
J

John

Ted Allen posted this message on how to create an
autonumber field in a query after it is sorted. I do not
understand what he is doing to create this field. Can
someone explain his logic.

THANKS in advance

Here is his advice:
It is possible to number the output rows of a query. To
do this, you create a new field and set the field's
source as a subquery that counts all lesser records based
on the sorted field(s).

But, in order for this to work properly the sorted fields
must result in unique combinations. If some records will
have the same values in the sorted fields, they will both
display the same row number. In those cases you would
usually add other fields to serve as tiebreakers in the
subquery to prevent duplicate row numbers.

If you aren't familiar with subqueries, post back with
your query sql and some info on what fields could be used
to uniquely identify records and I can help with the
subquery text.
 
John,

This works best if you have a datetime field in your
table. Although it doesn't actually create an autonumber
field, it will create a field that allows you to display
a "notional" record number.

SELECT T.*, Count(T1.someField) as RowNum
FROM yourTable T
LEFT JOIN yourTable T1
ON T.DateField >= T.DateField

Again, this could result in multiple rows with the same
[RowNum] if you have dates in your DateField that are not
unique.

You could do this with any other field ([Full Name]) or
combination of fields, although doing the combination of
fields can get complicated if you are using more than one
data type.
 
Dale,

I am not sure where the second table comes from. Is this
select statement for the subquery? What am I suppose to
do with the left join if I am creating a autonumber based
off the number of records in the query. I just need a
little bit more explanation.

Thanks,
John
-----Original Message-----
John,

This works best if you have a datetime field in your
table. Although it doesn't actually create an autonumber
field, it will create a field that allows you to display
a "notional" record number.

SELECT T.*, Count(T1.someField) as RowNum
FROM yourTable T
LEFT JOIN yourTable T1
ON T.DateField >= T.DateField

Again, this could result in multiple rows with the same
[RowNum] if you have dates in your DateField that are not
unique.

You could do this with any other field ([Full Name]) or
combination of fields, although doing the combination of
fields can get complicated if you are using more than one
data type.
-----Original Message-----
Ted Allen posted this message on how to create an
autonumber field in a query after it is sorted. I do not
understand what he is doing to create this field. Can
someone explain his logic.

THANKS in advance

Here is his advice:
It is possible to number the output rows of a query. To
do this, you create a new field and set the field's
source as a subquery that counts all lesser records based
on the sorted field(s).

But, in order for this to work properly the sorted fields
must result in unique combinations. If some records will
have the same values in the sorted fields, they will both
display the same row number. In those cases you would
usually add other fields to serve as tiebreakers in the
subquery to prevent duplicate row numbers.

If you aren't familiar with subqueries, post back with
your query sql and some info on what fields could be used
to uniquely identify records and I can help with the
subquery text.

.
Click to expand...
.
Click to expand...
 
John,

Sorry John, I left out an important part of the query, the GroupBy clause.
It should have read

SELECT T.Field1, T.Field2, T.Field3, Count(T1.someField) as RowNum
FROM yourTable T
LEFT JOIN yourTable T1 ON T.DateField >= T1.DateField
GROUP BY T.Field1, T.Field2, T.Field3

You keep using the term autonumber. My query will not give you an
autonumber. It will, however, allow you to add a column to your query that
displays a notional "RecordNumber" which is based on a field that has unique
values in it.

Basically, what this query does is use two copies of yourTable, and joins
them using a non-equal join. What this type of join will get you is a
result set similiar to an outer join (all records from table 1 assigned
against all records in table 2), except that each record in table 1 will
only link to those records in table 2 where the DateField is less than or
equal to the DateField in table 1.

Table1 Table 2
5/1/04 5/1/04
5/2/04 5/2/04
5/3/04 5/3/04

Result set before doing modifying it to a summation query
5/1/04 -> 5/1/04
5/2/04 -> 5/1/04
5/2/04 -> 5/2/04
5/3/04 -> 5/1/04
5/3/04 -> 5/2/04
5/3/04 -> 5/3/04

After adding the Group by clause and the Count, it would give you:

5/1/04 1
5/2/04 2
5/3/04 3

Initially, when you create the query in the query grid, you will have to
make this an InnerJoin. Then, when you are done converting it to a
Aggregate query, go to the SQL view and change the equality of the inner
join to an inequality.

You could also do this with a Select statement as well, something like:

Select YourTable.*, DCount("ID", "YourTable", "DateField <= " & T.DateField)
FROM yourTable T

But I've done it both ways, and with large recordsets, the first method is
significantly faster.

HTH
Dale

John said:
Dale,

I am not sure where the second table comes from. Is this
select statement for the subquery? What am I suppose to
do with the left join if I am creating a autonumber based
off the number of records in the query. I just need a
little bit more explanation.

Thanks,
John
-----Original Message-----
John,

This works best if you have a datetime field in your
table. Although it doesn't actually create an autonumber
field, it will create a field that allows you to display
a "notional" record number.

SELECT T.*, Count(T1.someField) as RowNum
FROM yourTable T
LEFT JOIN yourTable T1
ON T.DateField >= T.DateField

Again, this could result in multiple rows with the same
[RowNum] if you have dates in your DateField that are not
unique.

You could do this with any other field ([Full Name]) or
combination of fields, although doing the combination of
fields can get complicated if you are using more than one
data type.
-----Original Message-----
Ted Allen posted this message on how to create an
autonumber field in a query after it is sorted. I do not
understand what he is doing to create this field. Can
someone explain his logic.

THANKS in advance

Here is his advice:
It is possible to number the output rows of a query. To
do this, you create a new field and set the field's
source as a subquery that counts all lesser records based
on the sorted field(s).

But, in order for this to work properly the sorted fields
must result in unique combinations. If some records will
have the same values in the sorted fields, they will both
display the same row number. In those cases you would
usually add other fields to serve as tiebreakers in the
subquery to prevent duplicate row numbers.

If you aren't familiar with subqueries, post back with
your query sql and some info on what fields could be used
to uniquely identify records and I can help with the
subquery text.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top