No subject
Thu Dec 29 09:38:12 CST 2011
Where TerritoryID=T1.TerritoryID And DateAssigned<=[What Report Date would
you like to use?]
Order By DateAssigned DESC);
Good luck,
Drew
-----Original Message-----
From: Eric Goetz [mailto:EricGoetz at egisystems.com]
Sent: Monday, February 17, 2003 10:00 PM
To: accessd at databaseadvisors.com
Subject: RE: [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.
------_=_NextPart_001_01C2D70F.32E95930
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Re: [AccessD] select most recent</TITLE>
<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
P.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff size=2>Okay,
I built tblTest. It has ManagerID, TerritoryID and DateAssigned as you
have below. Then I used this SQL statement to produce the results you
want:</FONT></SPAN></DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff size=2>Select
ManagerID, TerritoryID, DateAssigned<BR>From tblTest As T1<BR>Where DateAssigned
In<BR>(Select Top 1 DateAssigned<BR>From tblTest<BR>Where
TerritoryID=T1.TerritoryID And DateAssigned<=[What Report Date would you like
to use?]<BR>Order By DateAssigned DESC);</FONT></SPAN></DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff size=2>Good
luck,</FONT></SPAN></DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=669003205-18022003><FONT face=Arial color=#0000ff
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Eric Goetz
[mailto:EricGoetz at egisystems.com]<BR><B>Sent:</B> Monday, February 17, 2003
10:00 PM<BR><B>To:</B> accessd at databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] select most recent<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><!-- Converted from text/plain format -->Hi
Gustav,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">My sample data looks
like this:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">ManagerID</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
</SPAN></FONT><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Territory</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> ID
DateAssigned</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">1
1
11/1/2002</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">2
1
12/1/2002</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">1
1
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">3
2
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">4
2
2/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">For a report as of
</SPAN></FONT><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">1/31/2003</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">, I am trying to
get:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">ManagerID</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
</SPAN></FONT><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Territory</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> ID
DateAssigned</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">1
1
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">3
2
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">I use
this:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><SQL></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">SELECT
</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
tblManagerAssignments.TerritoryID, </SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
tblManagerAssignments.MangerID, </SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
Max(tblManagerAssignments.DateAssigned) AS MaxOfDateAssigned</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">FROM
tblManagerAssignments</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">WHERE
(((tblManagerAssignments.DateAssigned)<=#1/31/2003#))</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">GROUP BY
</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
tblManagerAssignments.TerritoryID, </SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
tblManagerAssignments.MangerID;</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SQL></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">I end up with
this:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">ManagerID</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">
</SPAN></FONT><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Territory</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> ID
DateAssigned</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">2
1
12/1/2002</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">1
1
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">3
2
1/1/2003</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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?</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Thanks,</SPAN></FONT></P>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Eric</SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Gustav
Brock [mailto:gustav at cactus.dk] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Monday, February 17,
2003</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">2:16
AM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"><BR><B><SPAN
style="FONT-WEIGHT: bold">To:</SPAN></B> Eric Goetz<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [AccessD] select most
recent</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">Hi Eric</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">That could be something like:</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt"><SQL></SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">PARAMETERS</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> DateSelect DateTime;</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt">SELECT</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt"> TerritoryID,</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> ManagerID,</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> Max(DateAssigned) AS
DateAssigned</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">FROM</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> tblManagerAssignments</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt">WHERE</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt"> (DateAssigned <=
[DateSelect])</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">GROUP BY </SPAN></FONT><BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> TerritoryID,</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt"> ManagerID;</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt"></SQL></SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">This, of course, assumes that a territory is assigned
to a specific</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">manager until assigned to another. If assignment can
be cancelled</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">without reassignment, you'll need to add a new field,
DateCancelled,</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">and add to the Where statement:</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt"> AND</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> (DateCancelled Is Null OR DateCancelled >
[DateSelect])</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">If you wish to list territories not assigned a
manager, create a query</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">with all territories and an outer join to the query
above; those not</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">assigned will have a Null for ManagerID. Vice versa
for managers</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">without a territory.</SPAN></FONT> </P>
<P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">Please note that ManagerID and TerritoryID will both
be foreign keys.</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">And, as you note later, strip the name fields etc.
from this table.</SPAN></FONT> </P></DIV></BLOCKQUOTE></BODY></HTML>
------_=_NextPart_001_01C2D70F.32E95930--
More information about the AccessD mailing list