[AccessD] select most recent

Hi Gustav,

 

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.

 

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?

 

Thanks,

 

Eric

 

-----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

 

Hi Eric 

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. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030217/51636345/attachment-0002.html>