Link to most recent detail only

rbuser

I have 2 table, one of clients, and one of their 'hours' at a specific
point in time, so last year it may have been 12 hours, but from this year
it's 15 hours (say), so I'd have two entries in the second table with a
'StartingDate' field, linked with the primary of the clients one.

What I want is a report of the most recent hours per client, so I want to
link the tables something like 'hours where Max(StartingDate)'

Is this possible? Can't see how to and I've thought about it for a while!
Using RB11.


Thanks
--
Jeremy Knowles

nicocizik

Hi Jeremy,

I'm a bit unclear about what field you are linking your datasets on. Does
the Client table have a StartingDate field that corresponds to the Hours
table?

Nevertheless, it seems a sub query would be what you need to only retrieve
the records with the max starting date. Something like the following....

Select ClientID, StartingDate, Hours
From Hours
Where StartingDate in (Select Max(StartingDate) From Hours Group By
ClientID)

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Well the client table has ClientID, name, date of birth, address and other
details, some of which I have on the report, with various search criteria.

The hours table has ClientID (this is the link between the two),
StartDate, Hours.

What I want is for each client, if there is an entry in this hours table
for the hours field to appear alongside the client.

I've tried adding a new data table and linking it with my existing Clients
one, but am unsure how to get the subquery setup like you suggest so I
edited the SQL directly, but then it seems to be trying to add another
where to it, as I'm getting an SQL parsing error (Expected end of
statement but instead found Where in SELECT SQL statement at line 4). My
line 4 is the group by bit (see below), and I still get the same error if
I remove this bit, so it must be adding a second where statement to make
the link between the data queries.


SELECT ClientsIndex,StartDate,Hours
FROM Hours
where StartDate in (select Max(StartDate) From Hours)
Group By ClientsIndex


So I guess my question is how do I create the link with the sub query as
you suggest. I've tried searching your Wiki (nice job, BTW), and the
newsgroups but to no avail. I guess I'm missing something!

--
Jeremy Knowles

nicocizik

Hi Jeremy,

In DADE, you will want to enter the subquery in the Value edit box of a
search criteria. Also, the Group By command should be in the subquery
rather than the main query. Try adding a new search criteria in the query
designer with...

Field: StartDate
Operator: In List
Value: Select Max(StartDate) From Hours Group By ClientsIndex

This should give you a query that looks similar to the following...

SELECT ClientsIndex,StartDate,Hours
FROM Hours
WHERE (StartDate IN (Select Max(StartDate) From orders Group By
ClientsIndex) )
ORDER BY ClientsIndex

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:


I wasn't clear whether you meant create another query thing or do this in
the original (clients) one so tried both.

I can't add the Group By bit, as DBISAM doesn't like it and without it, I
only get a single return from the sub-query (the one with the highest
startdate for the entire table, not for each client separately), which
means that whilst I see all the clients OK, I only see the hours that
correspond with one of the clients.

--
Jeremy Knowles