Re: Printing earliest report of name selected

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Grizz" <Grizz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F13B87EB-D030-4FEB-897A-46753037330B@xxxxxxxxxxxxxxxx
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" wrote:

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" <Grizz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7EED488C-EC1E-4AFE-9EC8-CE5C4177DCEF@xxxxxxxxxxxxxxxx
> 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.

.

Relevant Pages

  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • Re: Printing earliest report of name selected
    ... recent date for each client. ... Use this query as the source for your report. ... Ok I used my location table to SELECT RigNameID, ... Since one "name" can have many entries here, I assume you have set up a ...
    (microsoft.public.access.reports)
  • Re: High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... We have encountered similar problems not limited to Excel, but every client. ... occur when there are many dimensions nested on each other on a single axis ... > Performance Guide to optimize the query, the cube, the server, etc. ... > While the query was grinding, the server was doing absolutely nothing. ...
    (microsoft.public.sqlserver.olap)
  • Re: Delete Query Does Not
    ... When I try to run the SQL query below it gives me the ... Cannot group on fields selected with '*' (client) ... INNER JOIN meals ... Tom Ellison ...
    (microsoft.public.access.queries)