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

/gustav

> I'm using AXP to work with sales data. I have a table of sales
> territories and a table of sales managers. Only one manager can be
> assigned to a territory at a time. The managers table has the following
> fields:

> ManagerID
> TerritoryID
> LastName
> FirstName
> StartDate

> ManagerID is the primary key, and TerritoryID is the foreign key. Given
> a date, I need to generate a list of territories and the most recently
> assigned manager. I can select the manager that started before the date.

> SELECT tblTerritories.Name, tblManagers.LastName, tblManagers.StartDate
> FROM tblManagers RIGHT JOIN tblTerritories ON tblManagers.TerritoryID =
> tblTerritories.TerritoryID
> WHERE (((tblManagers.StartDate)<=[Enter Report Date]));

> I need help limiting the result to the single most recent manager for
> each territory. I would greatly appreciate any suggestions.