Query to set the number of records in a group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to display the last 5 payments made towards a file number. Is there a
way to set up a query in Access to do that?
 
Thank you. But how do i specify the "Top 5" in a query. I will be having
the fields: file number, date the payment was made and the amounts. For each
file number i want to get the top five payments.
 
SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)
 
Thank you John Spencer. I used your SQL but it did not give an output and
the message was "at most one record can be returned by this subquery". The
SQL is below - is there any thing wrong with it that I missed.

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd = (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno = finsum.fileno
order by ta_amt_recvd DESC);


John Spencer said:
SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)


R Alapatt said:
Thank you. But how do i specify the "Top 5" in a query. I will be having
the fields: file number, date the payment was made and the amounts. For
each
file number i want to get the top five payments.
Click to expand...
Click to expand...
 
One small problem. You didn't miss anything, I did. I should have used IN
instead of =

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd IN (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno = finsum.fileno
order by ta_amt_recvd DESC);

R Alapatt said:
Thank you John Spencer. I used your SQL but it did not give an output and
the message was "at most one record can be returned by this subquery".
The
SQL is below - is there any thing wrong with it that I missed.

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd = (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno = finsum.fileno
order by ta_amt_recvd DESC);


John Spencer said:
SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)


R Alapatt said:
Thank you. But how do i specify the "Top 5" in a query. I will be
having
the fields: file number, date the payment was made and the amounts. For
each
file number i want to get the top five payments.

:

If you have a payment date field then you can create a Top 5 query
that
is
sorted by that date field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I need to display the last 5 payments made towards a file number. Is
there
a
way to set up a query in Access to do that?
Click to expand...
Click to expand...
Click to expand...
 
Thank you John, the query worked (it took a lot of time) but the result is
not what I am looking. There were more than 5 amounts listed for each file
no. Let me show a sample of the result I am looking for. Some file numbers
may not have 5 payments and some have more than 5 but I need that lastest
five payments.

FILENO DATE TA_AMT_RECVD
C100005 3/2/2004 0
C100009 11/28/2005 25
C100009 7/1/2005 75
C100009 4/29/2005 75
C100009 3/1/2005 150
C100009 2/3/2005 75
C100015 5/2/2006 100
C100017 12/2/2002 -220
C100017 11/15/2002 220
C100027 4/4/2005 90
C100027 3/7/2005 90
C100027 1/28/2005 90
C100027 12/27/2004 90
C100027 12/6/2004 90

John Spencer said:
One small problem. You didn't miss anything, I did. I should have used IN
instead of =

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd IN (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno = finsum.fileno
order by ta_amt_recvd DESC);

R Alapatt said:
Thank you John Spencer. I used your SQL but it did not give an output and
the message was "at most one record can be returned by this subquery".
The
SQL is below - is there any thing wrong with it that I missed.

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd = (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno = finsum.fileno
order by ta_amt_recvd DESC);


John Spencer said:
SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)


Thank you. But how do i specify the "Top 5" in a query. I will be
having
the fields: file number, date the payment was made and the amounts. For
each
file number i want to get the top five payments.

:

If you have a payment date field then you can create a Top 5 query
that
is
sorted by that date field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I need to display the last 5 payments made towards a file number. Is
there
a
way to set up a query in Access to do that?
Click to expand...
Click to expand...
Click to expand...
 
First of all I may have misread what you want.

Do you want the TOP 5 Amounts or do you want the last five payments by date
(Top 5 Dates)? Reading your request again, I think you want the top 5 dates
per FileNo. I gave you the Top 5 payment amounts (so if every payment was
$75 for 12 months for account C10009, you would get 12 records returned for
that File_No

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where FinSum.Date IN (SELECT Top 5 Temp.Date
FROM finsum as Temp
WHERE Temp.fileno = finsum.fileno
ORDER BY Temp.Date DESC)
ORDER BY FileNo, FinSum.Date Desc

If that doesn't work, can you tell us
-- What is the primary key of the table FinSum
-- How is failed? Too many records, ties displayed and you don't want ties


R Alapatt said:
Thank you John, the query worked (it took a lot of time) but the result is
not what I am looking. There were more than 5 amounts listed for each
file
no. Let me show a sample of the result I am looking for. Some file
numbers
may not have 5 payments and some have more than 5 but I need that lastest
five payments.

FILENO DATE TA_AMT_RECVD
C100005 3/2/2004 0
C100009 11/28/2005 25
C100009 7/1/2005 75
C100009 4/29/2005 75
C100009 3/1/2005 150
C100009 2/3/2005 75
C100015 5/2/2006 100
C100017 12/2/2002 -220
C100017 11/15/2002 220
C100027 4/4/2005 90
C100027 3/7/2005 90
C100027 1/28/2005 90
C100027 12/27/2004 90
C100027 12/6/2004 90

John Spencer said:
One small problem. You didn't miss anything, I did. I should have used
IN
instead of =

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd IN (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno =
finsum.fileno
order by ta_amt_recvd DESC);

R Alapatt said:
Thank you John Spencer. I used your SQL but it did not give an output
and
the message was "at most one record can be returned by this subquery".
The
SQL is below - is there any thing wrong with it that I missed.

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd = (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno =
finsum.fileno
order by ta_amt_recvd DESC);


:

SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)


Thank you. But how do i specify the "Top 5" in a query. I will be
having
the fields: file number, date the payment was made and the amounts.
For
each
file number i want to get the top five payments.

:

If you have a payment date field then you can create a Top 5 query
that
is
sorted by that date field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I need to display the last 5 payments made towards a file number.
Is
there
a
way to set up a query in Access to do that?
Click to expand...
Click to expand...
Click to expand...
 
John Spencer : Thank you and sorry for the delay in responding. The query
works but takes is too much time because of the ordered by for the main
query. So I split the query - the first query creates a table that is
sorted. The second query takes data from the created table and uses the
criteria to get the top 5 payments. Work beautifully!

John Spencer said:
First of all I may have misread what you want.

Do you want the TOP 5 Amounts or do you want the last five payments by date
(Top 5 Dates)? Reading your request again, I think you want the top 5 dates
per FileNo. I gave you the Top 5 payment amounts (so if every payment was
$75 for 12 months for account C10009, you would get 12 records returned for
that File_No

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where FinSum.Date IN (SELECT Top 5 Temp.Date
FROM finsum as Temp
WHERE Temp.fileno = finsum.fileno
ORDER BY Temp.Date DESC)
ORDER BY FileNo, FinSum.Date Desc

If that doesn't work, can you tell us
-- What is the primary key of the table FinSum
-- How is failed? Too many records, ties displayed and you don't want ties


R Alapatt said:
Thank you John, the query worked (it took a lot of time) but the result is
not what I am looking. There were more than 5 amounts listed for each
file
no. Let me show a sample of the result I am looking for. Some file
numbers
may not have 5 payments and some have more than 5 but I need that lastest
five payments.

FILENO DATE TA_AMT_RECVD
C100005 3/2/2004 0
C100009 11/28/2005 25
C100009 7/1/2005 75
C100009 4/29/2005 75
C100009 3/1/2005 150
C100009 2/3/2005 75
C100015 5/2/2006 100
C100017 12/2/2002 -220
C100017 11/15/2002 220
C100027 4/4/2005 90
C100027 3/7/2005 90
C100027 1/28/2005 90
C100027 12/27/2004 90
C100027 12/6/2004 90

John Spencer said:
One small problem. You didn't miss anything, I did. I should have used
IN
instead of =

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd IN (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno =
finsum.fileno
order by ta_amt_recvd DESC);

Thank you John Spencer. I used your SQL but it did not give an output
and
the message was "at most one record can be returned by this subquery".
The
SQL is below - is there any thing wrong with it that I missed.

SELECT DATE, FILENO, TA_AMT_RECVD, TRANS_CODE
FROM FINSUM
Where ta_amt_recvd = (Select top 5 ta_amt_recvd
from finsum as Temp
where Temp.fileno =
finsum.fileno
order by ta_amt_recvd DESC);


:

SQL statement would look like the following.

SELECT [File Number], DatePaid, Amounts
FROM YourTable
WHERE Amounts =
(SELECT Top 5 Amounts
FROM YourTable as Temp
WHERE Temp.[File Number] = YourTable.[FileNumber]
ORDER BY Amounts DESC)


Thank you. But how do i specify the "Top 5" in a query. I will be
having
the fields: file number, date the payment was made and the amounts.
For
each
file number i want to get the top five payments.

:

If you have a payment date field then you can create a Top 5 query
that
is
sorted by that date field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I need to display the last 5 payments made towards a file number.
Is
there
a
way to set up a query in Access to do that?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top