Reporting the last two updates

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

Guest

I have tried to use some of the suggestions from other questions, but it
doesn't seem to work for me.

I have a status report, based on a query that uses two tables for
information. One table collects information for each Property-no duplicates,
the other collects a running status for each property, including a date for
each status.

My report brings in all the information ok, and I was able to limit the
report to status collected within two weeks. I used" Between Date() and
Date()-12" to restrict to the last 13 days.

I have a new problem. My report is eliminating the properties that have not
been updated within that time frame. How do I report the last two updates on
this report, even if it has not been updated within two weeks?

My
 
If the number of status updates per property within the last two weeks is
never going to exceed two, or even if it is but you are only interested in
the last two, then you can forget about the date range and simply return the
last two updates per property. This would use a subquery to determine the
last two updates. Taking a very simple example which returns just the
property and the date of the update:

SELECT
P1.Property, S1.StatusDate
FROM Properties INNER JOIN Status AS S1
ON Properties.Property = S1.Property
WHERE
(SELECT COUNT(*)
FROM Status AS S2
WHERE S2.Property = S1.Property
AND S2.StatusDate >= S1.StatusDate) <=2;

Ken Sheridan
Stafford, England
 
Post back the SQL of the query underlying your report and I'll see if I can
amend it for you. Might not be today though.

JJ said:
I don't understand your response. I am really new to Access and struggling.
Click to expand...
 
Open the query in design view, then select SQL View from the View menu. Copy
the contents of the SQL window to the clipboard and paste it into your reply
here.

JJ said:
How do I do that? As I said, I am very new to this. I don't even know what
the SQL is.
Click to expand...
 
I am in your situation, a newbie to SQL but have made several access
databases. I also was looking for a query to get the last 2 records
from a table and finding the associated info of those last 2 records.
Someone provided me with a SQL statement containing the same design as
the one Ken provided you. If it helps, here is what the statement
does (Ken - if I am in error anywhere, please correct me since it
helps me as well):

SELECT will identify the fields you are interested in working with. So
SELECT is picking the columns in your table that are pertinent to the
discussion

FROM is obvious and it is saying the data identified in the fields
specified by the SELECT statement are originating from.

INNER JOIN...ON is used to combine records from 2 tables using some
criteria

WHERE is the criteria used to select the records from the 2 tables
that INNER JOIN will combine.
 
The only thing I'd add to that is that in this case the WHERE clause contains
a subquery (the SELECT statement in parentheses). This is what's known as a
correlated subquery because it refers to the outer query, the correlation
being on the Property and Status columns of the Status table. Note how the
two instances of the Status table are differentiated by aliases S1 and S2.
By means of the correlation the subquery counts the rows returned where the
value in the Property column is the same as in the current row of the outer
query, and the StatusDate is later than or equal to the StatusDate in the
current row of the outer query. The outer query consequently only returns
rows where the count is less than or equal to 2, i.e. the latest two rows per
property.

However, I've just noticed an error in the SQL statement arising from a
change of tactic on my part when writing it. I'd originally given the
Properties table an alias P1, but realised this wasn't needed as only one
instance of the properties table is referred to. But I omitted to remove the
reference to the alias in the first line which should have read.

SELECT Properties.Property, S1.StatusDate

In real life this sort of slip-up would have become immediately apparent
when debugging the query of course.

Ken Sheridan
Stafford, England
 
Back
Top