How best to extract the last few things done?

[MLH]

I have a table - tblCorresponden ce. It houses records
of correspondence initiated here in our office. Records
are appended whenever it is recognized that some type
of outbound letter needs to be created and sent. The
table contains [OutDate] and [OutProcessor] fields. The
2 fields are updated when the letter is printed, permanently
marking it as having been printed & presumably sent.

At any given moment, there may be a number of unprinted
letters. Whenever its convenient, the clerk clicks a button
to print them all. Their [OutDate] and [OutProcessor] fields
are updated to Now() and CurrentUser() respectively.

What's the best way to write a query that'll extract only the
last record(s) marked as printed? Could be none, one or a
dozen - dunno. Also - dunno the exact timestamp that goes
into the [OutDate] when it is written. The records are NOT
all grouped together at the end of the table.

Recommendation?

[w_a_n_n_a_l_l_ -]

Select c.* From tblCorresponden ce As c where c.OutDate = (Select
Max(OutDate) From tblCorresponden ce))

[Bob Quintal]

MLH <CR**@NorthStat e.net> wrote in
news:b4******** *************** *********@4ax.c om:

I have a table - tblCorresponden ce. It houses records
of correspondence initiated here in our office. Records
are appended whenever it is recognized that some type
of outbound letter needs to be created and sent. The
table contains [OutDate] and [OutProcessor] fields. The
2 fields are updated when the letter is printed, permanently
marking it as having been printed & presumably sent.

At any given moment, there may be a number of unprinted
letters. Whenever its convenient, the clerk clicks a button
to print them all. Their [OutDate] and [OutProcessor] fields
are updated to Now() and CurrentUser() respectively.

What's the best way to write a query that'll extract only the
last record(s) marked as printed? Could be none, one or a
dozen - dunno. Also - dunno the exact timestamp that goes
into the [OutDate] when it is written. The records are NOT
all grouped together at the end of the table.

Recommendation?

SELECT * FROM tblCorresponden ce where datediff("h",
[outdate],dmax("[outdate]","tblCorrespon dence")) < 9
ORDER BY [outdate] DESC;

This will return all records flagged on the last day that any
correspondence was printed, assuming a 9 to 5 workday.

--
Bob Quintal

PA is y I've altered my email address.

[MLH]

YES BABY!!!!

U R A genius man!

[MLH]

>Select c.* From tblCorresponden ce As c where c.OutDate = (Select

Max(OutDate) From tblCorresponden ce))

When I look at the above syntax, a natural question comes to mind.
Why the "As c" thing? I mean, why the above rather than the following?

SELECT *
FROM tblCorresponden ce
WHERE (((OutDate)=(Se lect Max(OutDate) From tblCorresponden ce)));

I'm guessing there's some added flexibility in using the syntax (that
perhaps is not illustrated in this example) and people tend to favor
the syntax for that reason. But I am curious and wonder if you might
elaborate - it time permits.

Thx again for the solution.

[Rick Wannall]

Sorry. I should have said something about that.

The "As c" you probably know by now is an alias. It is a device used to
provide a convenient reference to a long (or confusing) table name. One
benefit: It can make you SQL shorter and easier to read. Compare

Select [some stupid old table name].LastName, [some stupid old table
name].FirstName, [some stupid old table name].Title
From [some stupid old table name]
Where [some stupid old table name].Category = 'mycategory'

with

Select t.LastName, t.FirstName, t.Title
From [some stupid old table name] as t
Where t.Category = 'mycategory'

So, readability all by itself is a plus. I like aliases so much just on
this ground that I regularly let my fingers to crazy and give my tables
really clear names, regardless of length. Then, when referring to them in
the application I apply aliases everywhere.

There is another reason more related to function than to convenience. In
some queries, you will need to use a subquery. That will have a form like
this:

Select field1, field2, field3... From mytable Where field1 =
(Select(Max) field1 from mytable and mytable.field2 = mytable.field2)

An example would be wehre field1 is a date field, and you want all the
records from mytable where field1 is equal to the latest date contained in
field1, and, in the example above, field2 in the main query is equal to
field2 in the subquery. In the SQL above, there is a problem. The intent
is that field2 in the subquery be equal to field2 in the main query. There
is no way for you (or any SQL parser) to understand this intend. Both
references are to mytable.field2, Whicy mytable.field2?

Aliases solve this problem.

Select field1, field2, field3... From mytable As mUpper Where field1 =
(Select(Max) mSub.field1 from mytable as mSub and mSub.field2 =
mUpper.field2)

Note how the use of aliases makes it possible in the subquery to specify
that mSub.field2 should be compared to mUpper.field2.

[MLH]

Enlightening.

Thx for the explanation. Seems I agree
with you on the first count. And I understand
the implications in the second count.

Many thx.