Printing earliest report of name selected

[Grizz]

What I need to do is print a report that will only print the last entry of
that selected Name, not the complete last record of all entries. Now I know
how to do a qwery select by name, but I do not know how to get just the last
record of that name. Any help would be greatly appreciated.

 

[Allen Browne]

Say you have a table named tblEntry with fields like this:
- EntryID autonumber (primary key)
- ClientID relates to a table of clients
- EntryDate Date/Time field
- Entry the actual data for the client on this date.

Since one "name" can have many entries here, I assume you have set up a
one-to-many relationship between the clients table and this table of
entries.

You say you know how to set up a query like this:
SELECT ClientID, Max(EntryDate) AS LastEntry
FROM tblEntry
GROUP BY ClientID;

Now create another query, using that one at your tblEntry as input 'tables.'
Join them on EntryID and also on the date. This query gives only the most
recent date for each client. Use this query as the source for your report.

Note that the concept of a 'last entry' per client is not properly defined
unless you create a unique index on the combination of ClientID + EntryDate
(i.e. a client can't have 2 entries on the same date.)

 

[Grizz]

First off, Thank You Allen for responding, this is the first time to post in
such a long time as I usually just have to read other threads to get my
answers, which allot of them have been yours. So this is an honor you
picking mine. This made perfectly sense up to the second paragraph.

"Now create another query, using that one at your tblEntry as input 'tables.'

Join them on EntryID and also on the date. This query gives only the most
recent date for each client. Use this query as the source for your report.

"
Here are my tables

CallInTbl
CallInID
Date (Auto system Date)
Time (Auto Sytem clock)

ContactInfoTbl
ContactInfoID
(with all the Info fields)

TractorInfoTbl
TractorInfoID
Operator------Text
RigDrive------Text

LocationInfoTbl
LoadID
CallInID
TractorInfoID
RigNameID
(with more info fields)

RigNameTbl
RigNameID
RigNameNumber---------Text

RigName is the field to lookup the lastEntry date
I understand your first part in the select statement, I must be overreading
the second part. Ok I used my location table to SELECT RigNameID, MAX(Date)
AS LastEntry FROM LocationInfoTbl, CallInTbl GROUP BY RigNameID

Now if I am reading this right use this "RigName" query as a JOIN from
another query "that shows all fields"

 

[Allen Browne]

If I can suggest some changes:
a) Date and Time are both reserved words, and will give you problems as
field names. Here's a list of the names to avoid when designing tables:
http://allenbrowne.com/AppIssueBadWord.html

b) Combining the date and time into one field will make this job much easier
(and more efficent to execute). So, I'm suggesting CallInTbl has fields:
- CallInID primary key
- CallInDateTime Date/Time

Now you can get the most recent call date/time for each rig like this:
SELECT RigNameID,
Max(CallinDateTime) AS LastEntry
FROM CallInTbl INNER JOIN LocationInfoTbl
ON CallInTbl.CallInID = LocationInfoTbl.CallInID
GROUP BY RigNameID;

You can then use that query to limit another query to only the most recent
call per rig. If that's a new idea, here's an example using the Orders table
from the old Northwind sample database to show details of the most recent
order per client:
http://allenbrowne.com/temp/LastOrder.jpg

 

[Grizz]

Allen, Thank You very much! it worked great.