Numbering records

[Guest]

Having half understood the reply to an earlier query I have made my query
work - however since I will be using this style of query again I would really
like to understand what I have written!

What I have managed it to run a make table query that puts a sequential
number on each record.

I am producing a catalogue for a show and so I can't use an autonumber since
I won't know the sequence of the records until all exhibitors have entered -
once they are in I can sort them, and I then need to give each entry an
number.

The code I have used is as follows (although personnally I prefer to use the
design grid):

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassID, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.Position INTO Catalogue2
FROM Catalogue;

Could someone explain where the letter "A" comes from and why the <=
expression.

As I say it works - but I'd like to know more.

Thanks - Helen

 

[Michel Walsh]

Hi,


The A is the name of the variable, like


Dim A As MyClass

or


Dim A As catalogue


in fact, it is the "C/C++/C# way:

MyClass A;
cataloque A;


SQL allows something between the two syntax, keeping the C syntax, but
adding the keyword AS, and, uses FROM rather than DIM

FROM catalogue As A

In SQL, if we don't mention the variable (more generally called an ALIAS),
the name is the same as the class:


FROM catalogue

is the same as

FROM catalogue As catalogue



So, you have two variables, from your table, but the one with the name A
only keep records where classID <= the value of the other variable
classID value.. In other words, for a given catalogue.ClassID value, A
could find only those with a lower value, or the same value, for ClassID...
COUNTing them tell the "rank" (position) occupied by catalogue.ClassID.

SQL statements describe the result, NOT HOW TO get the result: the optimizer
may find the better solution it knows to get to a similar result, so, it
probably does not make a physical copy of data in the tables, when you say
FROM this As that.



Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassID, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.Position INTO Catalogue2
FROM Catalogue;

Could someone explain where the letter "A" comes from and why the <=
expression.

As I say it works - but I'd like to know more.

Hi Helen,

The easiest way I have found to understand
correlated subqueries is to mentally slow the
query down into a step-by-step process.

If you were to run the query w/o the subquery,
we could think of the "step-by-step" process
being something like:

-look at the first record in Catalogue
return it into Catalogue2
-look at the next record in Catalogue
return it into Catalogue2
-look at the next record in Catalogue
return it into Catalogue2
-{continue until no more Catalogue records}

At each step, we would like to look at the
value we have for ClassEntryID and count
the number of records in Catalogue that have
an equal or lesser value for ClassEntryID.

Imagine your Catalogue table had the following
simple data:

ClassEntryID
10
20
30
40
50

For a *specific* ClassEntryID (say 30), you probably
would have no problem creating a query that counts the
number of records in this simple Catalogue table whose
ClassEntryID was less than or equal to 30 (which would
return the number 3).

SELECT COUNT(*)
FROM Catalogue
WHERE ClassEntryID <= 30;

If you had this SQL in the query designer
and switched to Design View, right-mouse
clicked on the table and chose "Properties,"
in the "Alias" row you could type in "A"
(w/o the quotes). Your SQL would then
look like:

SELECT COUNT(*)
FROM Catalogue As A
WHERE A.ClassEntryID <= 30;

You just constructed your "uncorrelated" subquery.

Back to our "step-by-step" query,
each time we look at a record in Catalogue,
we want to compute this Count
using the specific ClassEntryID
for that record.

We could wrap our "uncorrelated" query in
parentheses and type it into a Field row of
a separate column in the query designer grid of
our original query, but it would always return 3.

SELECT
Catalogue.ClassEntryID,
Catalogue.EntryID,
Catalogue.ClassID,
(Select Count(*) FROM Catalogue As A
WHERE A.ClassEntryID <=30) AS CatalogueNo,
Catalogue.Position
INTO Catalogue2
FROM Catalogue;


So...we *correlate* our subquery back to the main query
by substituting
Catalogue.ClassEntryID for "30"

SELECT
Catalogue.ClassEntryID,
Catalogue.EntryID,
Catalogue.ClassID,
(Select Count(*) FROM Catalogue As A
WHERE A.ClassEntryID <=catalogue.ClassEntryID) AS CatalogueNo,
Catalogue.Position
INTO Catalogue2
FROM Catalogue;


Our step-by-step process might now be described as:

-look at the first record in Catalogue
what is the value for ClassEntryID? (=10)
count the number of records in a "separate instance" of the table
whose ClassEntryID is less than or equal to that value.
(Count = 1)
return this record and the count into Catalogue2
-look at the next record in Catalogue
what is the value for ClassEntryID? (=20)
count the number of records in a "separate instance" of the table
whose ClassEntryID is less than or equal to that value.
(Count = 2)
return this record and the count into Catalogue2
-look at the next record in Catalogue
what is the value for ClassEntryID? (=30)
count the number of records in a "separate instance" of the table
whose ClassEntryID is less than or equal to that value.
(Count = 3)
return this record and the count into Catalogue2
-{continue until no more Catalogue records}

Maybe that helps explain it better?

Good luck,

Gary Walter

 

[Guest]

Many thanks Gary and Michel for the comprehensive answers- I feel I have made
another step forward!!!

(but I might well be back!!)