Sequel statements

[viskoed via AccessMonster.com]

I am using Access 2003. I am trying to come up with a report that will give
me the last payment made by each retiree. Dental Payments are on a subform.
They pay either yearly, semi-annually, quarterly or yearly. I used the
"Last" statement and it seemed to work perfectly until I found that the
report wasn't correct. There were people who had made payments in June 2007
and it was showing their last payment as being in December of 2006. I am
unsure how to fix the statement below to use the Top 1 or Max to get the last
date of payment. Please help. If there may be a simpler way to do this, your
expertise and ideas are quite welcome. Thank you in advance.


SELECT [completelisting].[FirstName], [completelisting].[LastName],
[Dentalpayments].Account, [Dentalpayments].[Amtpaid], Last([Dentalpayments].
Date) AS LastOfDate, [completelisting].Inactive
FROM [completelisting] INNER JOIN [Dentalpayments] ON [completelisting].
Account = [Dentalpayments].Account
GROUP BY [completelisting].[FirstName], [completelisting].[LastName],
[Dentalpayments].Account, [Dentalpayments].[Amtpaid], [completelisting].
Inactive
ORDER BY [completelisting].[FirstName], [completelisting].[LastName];

 

[Guest]

Firstly forget about using the LAST (or FIRST) operators; they are all but
useless. Use operators which work on the values, such as MIN or MAX. In
this case the MAX operator will return the latest date. However, if you
simply GROUP BY other columns then it will return the latest date for each
group thus defined, so including the amount paid would give you the latest
dates per identical amounts paid per retiree.

What you have to do is find the latest date per retiree and then return the
row with that date. This is done by using a subquery to find the latest date
for the retiree and returning the rows from the outer query where the date
matches the latest date returned by the subquery for each retiree. This is
what's known as a correlated subquery. As the outer query and subquery use
the same table (DentalPayments) the two instances of the table are identified
by separate aliases. So a suitable query in your case would be:

SELECT FirstName, LastName, DP1.Account,
AmtPaid, [Date], Inactive
FROM CompleteListing INNER JOIN DentalPayments AS DP1
ON DP1.Account = CompleteListing.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM DentalPayments AS DP2
WHERE DP2.Account = DP1.Account);

Note that I've enclosed the refernces to the [Date] column in brackets.
This is because Date is the name of a built in function in Access (returning
the current date). It would be far better to name the column something like
PaymentDate, but if not you should either enclose it in brackets like this or
qualify it with the table name (or do both) to avoid any confusion and
possibly incorrect resuts.

Ken Sheridan
Stafford, England

I am using Access 2003. I am trying to come up with a report that will give
me the last payment made by each retiree. Dental Payments are on a subform.
They pay either yearly, semi-annually, quarterly or yearly. I used the
"Last" statement and it seemed to work perfectly until I found that the
report wasn't correct. There were people who had made payments in June 2007
and it was showing their last payment as being in December of 2006. I am
unsure how to fix the statement below to use the Top 1 or Max to get the last
date of payment. Please help. If there may be a simpler way to do this, your
expertise and ideas are quite welcome. Thank you in advance.


SELECT [completelisting].[FirstName], [completelisting].[LastName],
[Dentalpayments].Account, [Dentalpayments].[Amtpaid], Last([Dentalpayments].
Date) AS LastOfDate, [completelisting].Inactive
FROM [completelisting] INNER JOIN [Dentalpayments] ON [completelisting].
Account = [Dentalpayments].Account
GROUP BY [completelisting].[FirstName], [completelisting].[LastName],
[Dentalpayments].Account, [Dentalpayments].[Amtpaid], [completelisting].
Inactive
ORDER BY [completelisting].[FirstName], [completelisting].[LastName];

 

[viskoed via AccessMonster.com]

Mr. Sheridan: Thank you for your time. I tried to use your query as stated
below. What I received was a prompt to choose the last name, ( I chose
Anderson just to see what would happen) and then it showed me the dozen or so
andersons repeated many times ( 403 entries) with the 403 payments that would
be the last payment for all retirees, not just the andersons. What have I
done? On the up side, I am trying this out in my practice database. I am not
THAT NEW. I would appreciate any help you can give me on this.

Firstly forget about using the LAST (or FIRST) operators; they are all but
useless. Use operators which work on the values, such as MIN or MAX. In
this case the MAX operator will return the latest date. However, if you
simply GROUP BY other columns then it will return the latest date for each
group thus defined, so including the amount paid would give you the latest
dates per identical amounts paid per retiree.

What you have to do is find the latest date per retiree and then return the
row with that date. This is done by using a subquery to find the latest date
for the retiree and returning the rows from the outer query where the date
matches the latest date returned by the subquery for each retiree. This is
what's known as a correlated subquery. As the outer query and subquery use
the same table (DentalPayments) the two instances of the table are identified
by separate aliases. So a suitable query in your case would be:

SELECT FirstName, LastName, DP1.Account,
AmtPaid, [Date], Inactive
FROM CompleteListing INNER JOIN DentalPayments AS DP1
ON DP1.Account = CompleteListing.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM DentalPayments AS DP2
WHERE DP2.Account = DP1.Account);

Note that I've enclosed the refernces to the [Date] column in brackets.
This is because Date is the name of a built in function in Access (returning
the current date). It would be far better to name the column something like
PaymentDate, but if not you should either enclose it in brackets like this or
qualify it with the table name (or do both) to avoid any confusion and
possibly incorrect resuts.

Ken Sheridan
Stafford, England

I am using Access 2003. I am trying to come up with a report that will give
me the last payment made by each retiree. Dental Payments are on a subform.

[quoted text clipped - 15 lines]

Inactive
ORDER BY [completelisting].[FirstName], [completelisting].[LastName];

 

[Guest]

That suggests to me that the reference to LastName in the query does not
agree with the name of the column in the table. Even the slightest
difference in spelling, which can be all too easily done, will cause Access
not to recognize it as a column name and therefore treat it as a parameter,
producing the prompt which you are getting. When you respond with Anderson
it will then put that name in every row of the result set. The other data
returned in each row will be correct, but the name you entered at the
parameter prompt will replace the correct last name for each retiree.

Another possibility would be that a control in the report based on the query
refers to the LastName column incorrectly. This would only produce the
parameter prompt if you open the report, not the query directly, however, so
first test the query by opening it directly.

Incidentally, as you are using this query as the RecordSource for a report
don't include an ORDER BY clause in the query, as you did in your original
one. Instead use the report's internal sorting mechanism via the Sorting and
Grouping dialogue in report design view.

Ken Sheridan
Stafford, England

Mr. Sheridan: Thank you for your time. I tried to use your query as stated
below. What I received was a prompt to choose the last name, ( I chose
Anderson just to see what would happen) and then it showed me the dozen or so
andersons repeated many times ( 403 entries) with the 403 payments that would
be the last payment for all retirees, not just the andersons. What have I
done? On the up side, I am trying this out in my practice database. I am not
THAT NEW. I would appreciate any help you can give me on this.

Firstly forget about using the LAST (or FIRST) operators; they are all but
useless. Use operators which work on the values, such as MIN or MAX. In
this case the MAX operator will return the latest date. However, if you
simply GROUP BY other columns then it will return the latest date for each
group thus defined, so including the amount paid would give you the latest
dates per identical amounts paid per retiree.

What you have to do is find the latest date per retiree and then return the
row with that date. This is done by using a subquery to find the latest date
for the retiree and returning the rows from the outer query where the date
matches the latest date returned by the subquery for each retiree. This is
what's known as a correlated subquery. As the outer query and subquery use
the same table (DentalPayments) the two instances of the table are identified
by separate aliases. So a suitable query in your case would be:

SELECT FirstName, LastName, DP1.Account,
AmtPaid, [Date], Inactive
FROM CompleteListing INNER JOIN DentalPayments AS DP1
ON DP1.Account = CompleteListing.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM DentalPayments AS DP2
WHERE DP2.Account = DP1.Account);

Note that I've enclosed the refernces to the [Date] column in brackets.
This is because Date is the name of a built in function in Access (returning
the current date). It would be far better to name the column something like
PaymentDate, but if not you should either enclose it in brackets like this or
qualify it with the table name (or do both) to avoid any confusion and
possibly incorrect resuts.

Ken Sheridan
Stafford, England

I am using Access 2003. I am trying to come up with a report that will give
me the last payment made by each retiree. Dental Payments are on a subform.

[quoted text clipped - 15 lines]

Inactive
ORDER BY [completelisting].[FirstName], [completelisting].[LastName];

 

[viskoed via AccessMonster.com]

Thank you again for your assistance. It took me a few hours to figure out
what I was doing wrong, but finally got the appropriate results. I
definitely received an education. Thanks.

That suggests to me that the reference to LastName in the query does not
agree with the name of the column in the table. Even the slightest
difference in spelling, which can be all too easily done, will cause Access
not to recognize it as a column name and therefore treat it as a parameter,
producing the prompt which you are getting. When you respond with Anderson
it will then put that name in every row of the result set. The other data
returned in each row will be correct, but the name you entered at the
parameter prompt will replace the correct last name for each retiree.

Another possibility would be that a control in the report based on the query
refers to the LastName column incorrectly. This would only produce the
parameter prompt if you open the report, not the query directly, however, so
first test the query by opening it directly.

Incidentally, as you are using this query as the RecordSource for a report
don't include an ORDER BY clause in the query, as you did in your original
one. Instead use the report's internal sorting mechanism via the Sorting and
Grouping dialogue in report design view.

Ken Sheridan
Stafford, England

Mr. Sheridan: Thank you for your time. I tried to use your query as stated
below. What I received was a prompt to choose the last name, ( I chose

[quoted text clipped - 43 lines]

Inactive
ORDER BY [completelisting].[FirstName], [completelisting].[LastName];