Please Help!

J

jpark3205

I am trying to select the most recent loan store from a group of data for
each individual person. It is close to the last step in perfecting my query,
but it is one of the most important parts because I have the report grouped
by Loan Store so it needs to be correct. For example,

PS loan store Date Amt
100 20 7/20/08 10
100 1 8/19/08 20

In my query I have it grouped by PS and Sum of Amount... However, I need the
most recent loan store to be brought in as well. I have tried using the last
option in design view, but it is always taking the largest number. (i.e. 20
in this case). I need it to select 1. Here is my SQL statement if it will
help.

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Max(Advances.[Payout Store]) AS [MaxOfPayout
Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term Date],
Count(Advances.Description) AS CountOfDescription, [Chargeback log].[Date
Charged Back]
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back];


Thank you sooo much for your help!
 
K

Ken Sheridan

You'll need to use a subquery with a another subquery within it for this. In
SQL view the subquery would go in the SELECT Clause like this:

(SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastLoanStore

To do it in query design view enter the following in the 'field' row of a
blank column (you'll find it easier if you press Shift+F2 to zoom)

LastLoanStore: (SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS))

Use Ctrl+Enter to insert carriage returns when entering the SQL in zoom
view. The carriage returns aren't necessary but they make the SQL more
readable. You can of course name the column whatever you like rather than
LastLoanStore.

Note that this does rely on there being only one row per PS per date.

I would advise against using Date as a column name, however, as it’s the
name of a built in function. If you do use it be sure to qualify it with the
column name or wrap it in square brackets as above, but its better to use a
more specific name such as AdvanceDate or whatever suits.

Ken Sheridan
Stafford, England
 
J

jpark3205

Thank you so much for your help!! As far as the subquery goes, do I need to
make a different query or can I just place that clause in my main query.
Also, I am creating the query from a table so there are multiple lines and
dates. However, in the query there will only be 1 line per person. Is this
going to work? I will change the "date" field to a different name as soon as
I get it working. Thanks for the advice!

Ken Sheridan said:
You'll need to use a subquery with a another subquery within it for this. In
SQL view the subquery would go in the SELECT Clause like this:

(SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastLoanStore

To do it in query design view enter the following in the 'field' row of a
blank column (you'll find it easier if you press Shift+F2 to zoom)

LastLoanStore: (SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS))

Use Ctrl+Enter to insert carriage returns when entering the SQL in zoom
view. The carriage returns aren't necessary but they make the SQL more
readable. You can of course name the column whatever you like rather than
LastLoanStore.

Note that this does rely on there being only one row per PS per date.

I would advise against using Date as a column name, however, as it’s the
name of a built in function. If you do use it be sure to qualify it with the
column name or wrap it in square brackets as above, but its better to use a
more specific name such as AdvanceDate or whatever suits.

Ken Sheridan
Stafford, England

jpark3205 said:
I am trying to select the most recent loan store from a group of data for
each individual person. It is close to the last step in perfecting my query,
but it is one of the most important parts because I have the report grouped
by Loan Store so it needs to be correct. For example,

PS loan store Date Amt
100 20 7/20/08 10
100 1 8/19/08 20

In my query I have it grouped by PS and Sum of Amount... However, I need the
most recent loan store to be brought in as well. I have tried using the last
option in design view, but it is always taking the largest number. (i.e. 20
in this case). I need it to select 1. Here is my SQL statement if it will
help.

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Max(Advances.[Payout Store]) AS [MaxOfPayout
Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term Date],
Count(Advances.Description) AS CountOfDescription, [Chargeback log].[Date
Charged Back]
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back];


Thank you sooo much for your help!
Click to expand...
Click to expand...
 
J

jpark3205

Ken,

I tried just adding it to the SQL statement and it is returning an error
saying

At most one record can be returned by this subquery.

What am I doing wrong? Here is how my SQL statement now appears (With the
error) The statement you provided is located at the end of the Select
statement.

Thanks!

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Sum(Advances.Amount) AS SumOfAmount,
Termed.[Term Date], Count(Advances.Description) AS CountOfDescription,
[Chargeback log].[Date Charged Back], [Chargeback log].[Amount Charged Back],
[Chargeback log].Reason, (SELECT [Payout Store] FROM Advances AS A2 WHERE
A2.PS = Advances.PS AND [Date] = (SELECT MAX([Date]) FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastPayoutStore
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back], [Chargeback log].[Amount Charged Back], [Chargeback log].Reason;


Ken Sheridan said:
You'll need to use a subquery with a another subquery within it for this. In
SQL view the subquery would go in the SELECT Clause like this:

(SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastLoanStore

To do it in query design view enter the following in the 'field' row of a
blank column (you'll find it easier if you press Shift+F2 to zoom)

LastLoanStore: (SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS))

Use Ctrl+Enter to insert carriage returns when entering the SQL in zoom
view. The carriage returns aren't necessary but they make the SQL more
readable. You can of course name the column whatever you like rather than
LastLoanStore.

Note that this does rely on there being only one row per PS per date.

I would advise against using Date as a column name, however, as it’s the
name of a built in function. If you do use it be sure to qualify it with the
column name or wrap it in square brackets as above, but its better to use a
more specific name such as AdvanceDate or whatever suits.

Ken Sheridan
Stafford, England

jpark3205 said:
I am trying to select the most recent loan store from a group of data for
each individual person. It is close to the last step in perfecting my query,
but it is one of the most important parts because I have the report grouped
by Loan Store so it needs to be correct. For example,

PS loan store Date Amt
100 20 7/20/08 10
100 1 8/19/08 20

In my query I have it grouped by PS and Sum of Amount... However, I need the
most recent loan store to be brought in as well. I have tried using the last
option in design view, but it is always taking the largest number. (i.e. 20
in this case). I need it to select 1. Here is my SQL statement if it will
help.

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Max(Advances.[Payout Store]) AS [MaxOfPayout
Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term Date],
Count(Advances.Description) AS CountOfDescription, [Chargeback log].[Date
Charged Back]
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back];


Thank you sooo much for your help!
Click to expand...
Click to expand...
 
K

Ken Sheridan

I see the problem. You'll recall that I said 'this does rely on there being
only one row per PS per date'; well even though that is the case the query
doesn't know this so it correctly assumes that more than one row could
theoretically be returned, which would not work of course as you only want
one value in the column. The trick is to force it to return just one value
by using any aggregation operator, e.g. MAX in the first subquery:

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Sum(Advances.Amount) AS SumOfAmount,
Termed.[Term Date], Count(Advances.Description) AS CountOfDescription,
[Chargeback log].[Date Charged Back], [Chargeback log].[Amount Charged Back],
[Chargeback log].Reason,
(SELECT MAX([Payout Store])
FROM Advances AS A2
WHERE A2.PS = Advances.PS AND [Date] =
(SELECT MAX([Date]) FROM Advances AS A3
WHERE A3.PS = A2.PS))
AS LastPayoutStore
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID)
LEFT JOIN [Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back], [Chargeback log].[Amount Charged Back], [Chargeback log].Reason;

Ken Sheridan
Stafford, England

jpark3205 said:
Ken,

I tried just adding it to the SQL statement and it is returning an error
saying

At most one record can be returned by this subquery.

What am I doing wrong? Here is how my SQL statement now appears (With the
error) The statement you provided is located at the end of the Select
statement.

Thanks!

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Sum(Advances.Amount) AS SumOfAmount,
Termed.[Term Date], Count(Advances.Description) AS CountOfDescription,
[Chargeback log].[Date Charged Back], [Chargeback log].[Amount Charged Back],
[Chargeback log].Reason, (SELECT [Payout Store] FROM Advances AS A2 WHERE
A2.PS = Advances.PS AND [Date] = (SELECT MAX([Date]) FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastPayoutStore
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back], [Chargeback log].[Amount Charged Back], [Chargeback log].Reason;


Ken Sheridan said:
You'll need to use a subquery with a another subquery within it for this. In
SQL view the subquery would go in the SELECT Clause like this:

(SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS)) AS LastLoanStore

To do it in query design view enter the following in the 'field' row of a
blank column (you'll find it easier if you press Shift+F2 to zoom)

LastLoanStore: (SELECT [loan store]
FROM Advances AS A2
WHERE A2.PS = Advances.PS
AND [Date] =
(SELECT MAX([Date])
FROM Advances AS A3
WHERE A3.PS = A2.PS))

Use Ctrl+Enter to insert carriage returns when entering the SQL in zoom
view. The carriage returns aren't necessary but they make the SQL more
readable. You can of course name the column whatever you like rather than
LastLoanStore.

Note that this does rely on there being only one row per PS per date.

I would advise against using Date as a column name, however, as it’s the
name of a built in function. If you do use it be sure to qualify it with the
column name or wrap it in square brackets as above, but its better to use a
more specific name such as AdvanceDate or whatever suits.

Ken Sheridan
Stafford, England

jpark3205 said:
I am trying to select the most recent loan store from a group of data for
each individual person. It is close to the last step in perfecting my query,
but it is one of the most important parts because I have the report grouped
by Loan Store so it needs to be correct. For example,

PS loan store Date Amt
100 20 7/20/08 10
100 1 8/19/08 20

In my query I have it grouped by PS and Sum of Amount... However, I need the
most recent loan store to be brought in as well. I have tried using the last
option in design view, but it is always taking the largest number. (i.e. 20
in this case). I need it to select 1. Here is my SQL statement if it will
help.

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Max(Advances.[Payout Store]) AS [MaxOfPayout
Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term Date],
Count(Advances.Description) AS CountOfDescription, [Chargeback log].[Date
Charged Back]
FROM (Advances LEFT JOIN Termed ON Advances.PS = Termed.ID) LEFT JOIN
[Chargeback log] ON Advances.PS = [Chargeback log].PS
GROUP BY Advances.PS, Termed.[Term Date], [Chargeback log].[Date Charged
Back];


Thank you sooo much for your help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help with formula (multiple ifs to determine due date) 3
Queries In Microsoft Access..help Please!!!! 0
VB help please! 3
Subform in datasheet view with field displaying whether file exist 1
HELP!! Outlook's Misbehaving-Ways: Viewing Email Appears Impossibl 1
the ultimate stock trading data model 0
E-mail access 7

Top