My goal is to select Vendors that have not had any activity after a
given date. The table Orders has all activity from each order. Most
vendors will be listed multiple times with activity in several years.

Below is my Select statement:

SELECT Distinct Vendor
FROM Orders
WHERE update_date < #01/01/2002#

I am using "Distinct", because I don't need to see vendors more
than once. As the statement is written, it shows each Vendor that had
activity before 01/01/2002. How can it be rewritten to show Vendors
that do not have activity after that date ?

Thank you for your assistance.

Dig

Change the "<" To ">".

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
resource@pcdatasheet.com


<dig314@yahoo.com> wrote in message
news:1140542852.705392.247000@o13g2000cwo.googlegr oups.com...[color=blue]
> My goal is to select Vendors that have not had any activity after a
> given date. The table Orders has all activity from each order. Most
> vendors will be listed multiple times with activity in several years.
>
> Below is my Select statement:
>
> SELECT Distinct Vendor
> FROM Orders
> WHERE update_date < #01/01/2002#
>
> I am using "Distinct", because I don't need to see vendors more
> than once. As the statement is written, it shows each Vendor that had
> activity before 01/01/2002. How can it be rewritten to show Vendors
> that do not have activity after that date ?
>
> Thank you for your assistance.
>
> Dig
>[/color]

Thanks for the prompt reply. Changing the "<" To ">" will show vendors
that have had activity after that date. I need to see vendors that have
NOT had activity AFTER the date.

To put it another way, I need to see vendors that were used in 2001 (or
earlier), but have not been used since 2001.

Dig

Save your original query and then use that query as the the basis for a
new query. For example, if you named your original query 'MyQuery' you
could create a second query with the following SQL:

select distinct Vendor
from Orders
left join MyQuery
on Orders.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

If you have a Vendors table it might be preferable to use that to link
to your MyQuery query rather than use the Orders table again, e.g.

select Vendor
from Vendors
left join MyQuery
on Vendors.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

HTH,
Bruce

Save your original query and then use that query as the the basis for a
new query. For example, if you named your original query 'MyQuery' you
could create a second query with the following SQL:

select distinct Vendor
from Orders
left join MyQuery
on Orders.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

If you have a Vendors table it might be preferable to use that to link
to your MyQuery query rather than use the Orders table again, e.g.

select Vendor
from Vendors
left join MyQuery
on Vendors.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

HTH,
Bruce

Save your original query and then use that query as the the basis for a
new query. For example, if you named your original query 'MyQuery' you
could create a second query with the following SQL:

select distinct Vendor
from Orders
left join MyQuery
on Orders.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

If you have a Vendors table it might be preferable to use that to link
to your MyQuery query rather than use the Orders table again, e.g.

select Vendor
from Vendors
left join MyQuery
on Vendors.Vendor = MyQuery.Vendor
where MyQuery.Vendor Is Null

HTH,
Bruce

Do you actually read the requests before replying?

John... Visio MVP

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:peJKf.1724$S25.109@newsread1.news.atl.earthli nk.net...[color=blue]
> Change the "<" To ">".
>
> PC Datasheet[/color]

Dig, this is aircode, totally untested, but I think what you want is this:

SELECT Vendor
FROM Orders
Group By Vendor
Having Max(update_date) < #01/01/2002#

Hope it helps -

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.


<dig314@yahoo.com> wrote in message
news:1140542852.705392.247000@o13g2000cwo.googlegr oups.com...[color=blue]
> My goal is to select Vendors that have not had any activity after a
> given date. The table Orders has all activity from each order. Most
> vendors will be listed multiple times with activity in several years.
>
> Below is my Select statement:
>
> SELECT Distinct Vendor
> FROM Orders
> WHERE update_date < #01/01/2002#
>
> I am using "Distinct", because I don't need to see vendors more
> than once. As the statement is written, it shows each Vendor that had
> activity before 01/01/2002. How can it be rewritten to show Vendors
> that do not have activity after that date ?
>
> Thank you for your assistance.
>
> Dig
>[/color]

<dig314@yahoo.com> wrote in message
news:1140548297.948367.322690@z14g2000cwz.googlegr oups.com...[color=blue]
> Thanks for the prompt reply. Changing the "<" To ">" will show vendors
> that have had activity after that date. I need to see vendors that have
> NOT had activity AFTER the date.
>
> To put it another way, I need to see vendors that were used in 2001 (or
> earlier), but have not been used since 2001.
>[/color]

I'm sorry but PCDS has no interest in your problem, only in his advertising.
Should you be interested there is more information here:
http://home.tiscali.nl/arracom/whoissteve.html

Regards,
Keith.