Diana,
I believe this SQL code will do what you ask. I built it after building a
table
containing your three fields and a whole bunch of carefully made-up records
so that I would have some data to test with.
SELECT VN1.Organization, VN1.Vendor, VN1.Amount
FROM VendorName AS VN1
WHERE VN1.Vendor IN
(Select Top 100 VN2.Vendor
FROM VendorName AS VN2
WHERE (VN2.Organization=VN1.Organization)
ORDER BY VN2.Amount DESC)
ORDER BY VN1.Organization, VN1.Amount DESC;
I'm very handy at using the Access Query Design gird for complex queries,
but
I think this request is beyond what you can do with the design grid.
The query above is called a Correlated Subquery.
I carefully copied the table name and the three field names that you gave.
If any of these are wrong here, you will need to carefully correct my code
to
match your source database.
Code explanation (a few aspects of it):
"AS" creates an alias, a simpler way to refer to a table.
I'm using the table twice, one called VN1, the other called VN2.
Doing this establishes it as another entity, ready for examination.
The indented section is a small query in its own right (the subquery).
The main connection between the outer and inner queries is the 3rd
line:
"WHERE VN1.Vendor IN (... ), which supplies to the outer
query only those vendors selected by the inner query.
The inner query's one-line WHERE clause allows it to only include in
its
output those vendors whose organization match the one
currently
being examined by the main query (outer query).
The inner query sorts by Amount (descending) because
it needs to find the top 100 for that organization. The
sort here can ignore organization because it is only dealing
with the org. that the main query gave it.
The ORDER BY clauses set up the sort sequence: sort by this field first,
and then by that field. "DESC" tells it to sort descending.
You will see from the query's bottom line that the outer query is
sorting by
Organization and then by Amount.
Bruce Pick
