Help needed with SQL Select

[dig314]

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

[PC Datasheet]

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
re******@pcdata sheet.com
<di****@yahoo.c om> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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

[dig314]

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

[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

[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

[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

[John Marshall, MVP]

Do you actually read the requests before replying?

John... Visio MVP

"PC Datasheet" <no****@nospam. spam> wrote in message
news:pe******** ********@newsre ad1.news.atl.ea rthlink.net...

Change the "<" To ">".

PC Datasheet

[Randy Harris]

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.
<di****@yahoo.c om> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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

[Keith Wilby]

<di****@yahoo.c om> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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.

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.