Yep, it’s a SELECT query.
You'll find it a lot easier to write the whole thing in SQL view rather than
doing it in design view. Open the query designer in the usual way, but don't
add any tables. Switch to SQL view and then paste the SQL statement I gave
you in place of what's there. then change the table and column names to your
real ones, but don't change the aliases T1 and T2 unless you do so
consistently; these are to differentiate the two instances of the table so
the subquery can be correlated with the outer query.
You can add other columns from the table to the query if you wish by adding
them to the SELECT clause on the first line, or by switching to design view
and adding them there. If you do you'll see that the subquery id in fact a
column in the query's SELECT clause and will show up in the 'field' row of a
column in the design grid. However, you can't construct the subquery itself
visually ib design view; you have to write its SQL.
As regards the brackets around the column names these are only really
necessary if the name includes a space or other special characters such as
the # character. Access puts them in by default, and there's no harm in
using them universally, but most experienced developers tend to avoid spaces
or such characters, sometimes using an underscore for a space e.g. Lane_Code,
or by using 'camelCase' e.g laneCode or 'CamelCase', e.g. LaneCode (I favour
the last myself). Some purists favour all lower case for column names with
no spaces e.g. lanecode. You'll have noticed I've changed it to a singular
noun; this is another convention favoured by some, including myself, that
table names should wherever possible be plural or collective nouns
(reflecting the fact that a table is a set) and column names should wherever
possible be singular nouns (reflecting the fact that each column represents
an attribute). However, one should not be unduly prescriptive about these
things.
Ken Sheridan
Stafford, England
Chris Reeves said:
This should be a Select Query, correct?
Should Rate and Rankings not be enclosed just like Lane Codes since it is a
column title as well?
Also correct me if I am wrong, I need to put this string in the Criteria
field while my actual FIELD should say Rankings?
Ken Sheridan said:
In a subquery count all rows where the Lane Code is the same and the Rate is
less than the rate for the outer query's current row and add 1:
SELECT [Lane Codes], Rate,
(SELECT COUNT(*)
FROM [YourTable] AS T2
WHERE T2.Rate < T1.Rate
AND T2.[Lane Codes] = T1.[Lane Codes])+1
AS Rankings
FROM [YourTable] AS T1
ORDER BY [Lane Codes], Rate;
Ken Sheridan
Stafford, England
Chris Reeves said:
How would I write a statement have Access do an actual ranking on certain
data. I have the data for my columns titled Lane Codes and Rate. I know how
to sort the columns, but I can not figure out how to write the statement to
make the Rankings column count.
Lane Codes Rate Rankings
A $100 1
A $200 2
A $300 3
A $400 4
B $100 1
B $200 2
B $300 3
Thanks for your help.
