Ranking data - urgent

[Guest]

Hi all

I have a query which holds data for dealerships, I have the query sorted in
a specific order and what I need is to be able to take the first five records
of each dealer and mail merge results in word. How can I obtain just the
first five records for each dealer maintaining my sort order? I can't write
to a table using an autonumber Rank field as I would need the Rank to start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Arvin Meyer [MVP]]

Look at the TOP predicate.

Select Top 5 From YourTable Order By DateField;

That will give you the first 5 records if you have a date field. Use the ID
field or any other field that can give you a correct sort, if you don'y have
a date field.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks for the quick response. I tried this in a new sql query:

Select Top 5 * From [Qry Ratio Sorted] Order By Dealer_Code, Ratio DESC;

It doesn't produce what I want though, it just produces the first five
records from the first dealer in the query - I want it to produce the first
five records of EVERY dealer in the query?

Thanks
Sue

 

[Roger Carlson]

You need to use a correlated subquery. If you had given your SQL for the
query, I might have been able to show you something. As is, the best bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues with Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five records of
each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue

You need to use a correlated subquery. If you had given your SQL for the
query, I might have been able to show you something. As is, the best bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues with Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi all

I have a query which holds data for dealerships, I have the query sorted in
a specific order and what I need is to be able to take the first five records
of each dealer and mail merge results in word. How can I obtain just the
first five records for each dealer maintaining my sort order? I can't write
to a table using an autonumber Rank field as I would need the Rank to start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Guest]

I tried using your example and modified to:

select I1.*
from [qry Ratio Sorted] as I1
where (((I1.[Ratio]) in (select top 5 [Ratio] from [qry Ratio Sorted] I2
where I1.[Dealer_Code] = I2.[Dealer_Code] order by I2.[Ratio] desc)))
order by I1.Dealer_Code,I1.[Ratio] DESC;

I created this in a sql union query. I tried running the query and after a
few minutes access fell over with an error. Before I try again can you
confirm if this Is right?

Thanks
Sue

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five records of
each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue

You need to use a correlated subquery. If you had given your SQL for the
query, I might have been able to show you something. As is, the best bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues with Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi all

I have a query which holds data for dealerships, I have the query sorted in
a specific order and what I need is to be able to take the first five records
of each dealer and mail merge results in word. How can I obtain just the
first five records for each dealer maintaining my sort order? I can't write
to a table using an autonumber Rank field as I would need the Rank to start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Roger Carlson]

The query looks all right to me, though I don't understand the bit about the
Union query. Also, I don't know what you are doing in the qryRatioSorted
query, but if you're just sorting it, you might want to run it directly
against the table since this query is also sorting it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I tried using your example and modified to:

select I1.*
from [qry Ratio Sorted] as I1
where (((I1.[Ratio]) in (select top 5 [Ratio] from [qry Ratio Sorted] I2
where I1.[Dealer_Code] = I2.[Dealer_Code] order by I2.[Ratio] desc)))
order by I1.Dealer_Code,I1.[Ratio] DESC;

I created this in a sql union query. I tried running the query and after a
few minutes access fell over with an error. Before I try again can you
confirm if this Is right?

Thanks
Sue

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five records of
each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue

You need to use a correlated subquery. If you had given your SQL for the
query, I might have been able to show you something. As is, the best bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues with Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi all

I have a query which holds data for dealerships, I have the query sorted
in
a specific order and what I need is to be able to take the first five
records
of each dealer and mail merge results in word. How can I obtain just the
first five records for each dealer maintaining my sort order? I can't
write
to a table using an autonumber Rank field as I would need the Rank to
start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Guest]

Thanks Roger, I have changed it to look at [qry Ratio] as [qry Ratio Sorted]
was purely just sorting the data and I realised this new query was also
sorting it. I am not sure if its because Ratio is a calculated field but it
is taking forever to run, running query progress bar does not get past half
way and I've left it for approx 10 mins so far...

The query looks all right to me, though I don't understand the bit about the
Union query. Also, I don't know what you are doing in the qryRatioSorted
query, but if you're just sorting it, you might want to run it directly
against the table since this query is also sorting it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I tried using your example and modified to:

select I1.*
from [qry Ratio Sorted] as I1
where (((I1.[Ratio]) in (select top 5 [Ratio] from [qry Ratio Sorted] I2
where I1.[Dealer_Code] = I2.[Dealer_Code] order by I2.[Ratio] desc)))
order by I1.Dealer_Code,I1.[Ratio] DESC;

I created this in a sql union query. I tried running the query and after a
few minutes access fell over with an error. Before I try again can you
confirm if this Is right?

Thanks
Sue

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five records of
each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue


:

You need to use a correlated subquery. If you had given your SQL for the
query, I might have been able to show you something. As is, the best bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues with Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi all

I have a query which holds data for dealerships, I have the query sorted
in
a specific order and what I need is to be able to take the first five
records
of each dealer and mail merge results in word. How can I obtain just the
first five records for each dealer maintaining my sort order? I can't
write
to a table using an autonumber Rank field as I would need the Rank to
start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Roger Carlson]

Unfortunately, this WILL take a while to run against a large dataset. The
subquery is re-run for each line in the main query.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger, I have changed it to look at [qry Ratio] as [qry Ratio Sorted]
was purely just sorting the data and I realised this new query was also
sorting it. I am not sure if its because Ratio is a calculated field but it
is taking forever to run, running query progress bar does not get past half
way and I've left it for approx 10 mins so far...

The query looks all right to me, though I don't understand the bit about the
Union query. Also, I don't know what you are doing in the qryRatioSorted
query, but if you're just sorting it, you might want to run it directly
against the table since this query is also sorting it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I tried using your example and modified to:

select I1.*
from [qry Ratio Sorted] as I1
where (((I1.[Ratio]) in (select top 5 [Ratio] from [qry Ratio Sorted] I2
where I1.[Dealer_Code] = I2.[Dealer_Code] order by I2.[Ratio] desc)))
order by I1.Dealer_Code,I1.[Ratio] DESC;

I created this in a sql union query. I tried running the query and after a
few minutes access fell over with an error. Before I try again can you
confirm if this Is right?

Thanks
Sue


:

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five

records
of

each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue


:

You need to use a correlated subquery. If you had given your SQL

for
the

query, I might have been able to show you something. As is, the

best
bet is

to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice)

for
each

Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price] desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which discusses a number of issues

with
Top

queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi all

I have a query which holds data for dealerships, I have the

query
sorted

in
a specific order and what I need is to be able to take the first five
records
of each dealer and mail merge results in word. How can I obtain

just
the

first five records for each dealer maintaining my sort order? I can't
write
to a table using an autonumber Rank field as I would need the

Rank
to

start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Guest]

Thanks for all your help Roger, couldn't have sorted myself! I finally got to
work in approx 1 minute by creating a new table from [qry Ratio] first and
basing the top 5 query on this table instead of the query.

Thanks again
Sue

Unfortunately, this WILL take a while to run against a large dataset. The
subquery is re-run for each line in the main query.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger, I have changed it to look at [qry Ratio] as [qry Ratio Sorted]
was purely just sorting the data and I realised this new query was also
sorting it. I am not sure if its because Ratio is a calculated field but it
is taking forever to run, running query progress bar does not get past half
way and I've left it for approx 10 mins so far...

The query looks all right to me, though I don't understand the bit about the
Union query. Also, I don't know what you are doing in the qryRatioSorted
query, but if you're just sorting it, you might want to run it directly
against the table since this query is also sorting it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I tried using your example and modified to:

select I1.*
from [qry Ratio Sorted] as I1
where (((I1.[Ratio]) in (select top 5 [Ratio] from [qry Ratio Sorted] I2
where I1.[Dealer_Code] = I2.[Dealer_Code] order by I2.[Ratio] desc)))
order by I1.Dealer_Code,I1.[Ratio] DESC;

I created this in a sql union query. I tried running the query and after a
few minutes access fell over with an error. Before I try again can you
confirm if this Is right?

Thanks
Sue


:

Thanks Roger, will look at your example. For reference my SQL is

SELECT [qry Ratio].*
FROM [qry Ratio]
ORDER BY [qry Ratio].DEALER_CODE, [qry Ratio].Ratio DESC;

Currently there are 73879 records. I want just the first five records
of
each dealer code, totalling approx 1000 records.

P.S. Sorry if this posts twice the first time I think it failed?
--
Thanks
Sue


:

You need to use a correlated subquery. If you had given your SQL for
the
query, I might have been able to show you something. As is, the best
bet is
to look at a sample and generalize from there.

Suppose I have an Invoices table with AccountNum, OrderNum, and
TotalPrice.
Suppose further, I want to show the Top 3 orders (by TotalPrice) for
each
Account. I could do this:

SELECT I1.Account, I1.OrderNum, I1.[Total Price]
FROM Invoices AS I1
WHERE (((I1.[Total Price]) In (SELECT TOP 3 [Total Price] FROM
Invoices I2
WHERE I1.[account] = I2.[account] ORDER BY I2.[total price]
desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "TopQuery.mdb" which discusses a number of issues with
Top
queries, including this. Look at example 4.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi all

I have a query which holds data for dealerships, I have the query
sorted
in
a specific order and what I need is to be able to take the first
five
records
of each dealer and mail merge results in word. How can I obtain just
the
first five records for each dealer maintaining my sort order? I
can't
write
to a table using an autonumber Rank field as I would need the Rank
to
start
at 1 again each time the dealer number changed.

Any help asap would be most appreciated thanks.

Sue

 

[Arvin Meyer [MVP]]

This might be what you're looking for:

http://support.microsoft.com/default.aspx?scid=kb;en-us;153747
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thanks for the quick response. I tried this in a new sql query:

Select Top 5 * From [Qry Ratio Sorted] Order By Dealer_Code, Ratio DESC;

It doesn't produce what I want though, it just produces the first five
records from the first dealer in the query - I want it to produce the first
five records of EVERY dealer in the query?

Thanks
Sue

Look at the TOP predicate.

Select Top 5 From YourTable Order By DateField;

That will give you the first 5 records if you have a date field. Use the ID
field or any other field that can give you a correct sort, if you don'y have
a date field.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

sorted
in