[AccessD] select most recent

Hi Eric

> Thank you for taking up my question. That comes tantalizingly close. The
> trouble is that with the [ManagerID] in the GROUP BY, I end up with most
> of the managers that have been assigned to the territory prior to
> [DateSelect] instead of just the most recent one. If a manager had been
> in the territory more than once, only the most recent assignment is
> returned. So I do get some filtering.

Well, you end up with all the managers, sorry.
You could feed it to another query to filter that out but I would
rather suggest to follow the "TOP 1" route as suggested by Drew.

By the way, no trouble with Null values; they simply represent "not
known" or "undefined" which exactly would be the case here.

/gustav


> My sample data looks like this:

> ManagerID  Territory ID  DateAssigned
> 1                  1                11/1/2002
> 2                  1                12/1/2002
> 1                  1                  1/1/2003
> 3                  2                  1/1/2003
> 4                  2                  2/1/2003

> For a report as of 1/31/2003, I am trying to get:

> ManagerID  Territory ID  DateAssigned
> 1                  1                  1/1/2003
> 3                  2                  1/1/2003

> I use this:

> <SQL>

> SELECT 
>   tblManagerAssignments.TerritoryID,
>   tblManagerAssignments.MangerID,
>   Max(tblManagerAssignments.DateAssigned) AS MaxOfDateAssigned
> FROM tblManagerAssignments
> WHERE (((tblManagerAssignments.DateAssigned)<=#1/31/2003#))
> GROUP BY
>   tblManagerAssignments.TerritoryID,
>   tblManagerAssignments.MangerID;

> </SQL>

> I end up with this:

> ManagerID  Territory ID  DateAssigned
> 2                  1                12/1/2002
> 1                  1                  1/1/2003
> 3                  2                  1/1/2003

> I could add a [DateCancelled] field, but I just don't feel right about a
> design that incorporates NULL fields. Maybe I could calculate the
> [DateCancelled] field. Got any more ideas?


> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk] 
> Sent: Monday, February 17, 2003 2:16 AM
> To: Eric Goetz
> Subject: Re: [AccessD] select most recent

> That could be something like:

> <SQL> 

> PARAMETERS 
>   DateSelect DateTime; 
> SELECT 
>   TerritoryID, 
>   ManagerID, 
>   Max(DateAssigned) AS DateAssigned 
> FROM 
>   tblManagerAssignments 
> WHERE 
>   (DateAssigned <= [DateSelect]) 
> GROUP BY 
>   TerritoryID, 
>   ManagerID; 

> </SQL> 

> This, of course, assumes that a territory is assigned to a specific 
> manager until assigned to another. If assignment can be cancelled 
> without reassignment, you'll need to add a new field, DateCancelled, 
> and add to the Where statement: 

>   AND 
>   (DateCancelled Is Null OR DateCancelled > [DateSelect]) 

> If you wish to list territories not assigned a manager, create a query 
> with all territories and an outer join to the query above; those not 
> assigned will have a Null for ManagerID. Vice versa for managers 
> without a territory. 

> Please note that ManagerID and TerritoryID will both be foreign keys. 
> And, as you note later, strip the name fields etc. from this table.