How to accoplish this query?

J

jason

I have a table with ID, first, last, purchase_date.
The items our customers get, they only purchase once a year. I'd like to be
able to find out which customers made a purchase last year (in 2005) and NOT
in 2006.

Right now I have:

SELECT [2005-2006 DIABCO AND NOBLE SHOE Patients].socsec, [2005-2006 DIABCO
AND NOBLE SHOE Patients].f_name, [2005-2006 DIABCO AND NOBLE SHOE
Patients].l_name, [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date,
[2005-2006 DIABCO AND NOBLE SHOE Patients].procedure
FROM [2005-2006 DIABCO AND NOBLE SHOE Patients]
WHERE ((((([2005-2006 DIABCO AND NOBLE SHOE
Patients].[svc_date])=#1/1/2005#-#12/31/2005#) And (([2005-2006 DIABCO AND
NOBLE SHOE Patients].[svc_date])=#1/1/2006#-#12/31/2006#))=False))
ORDER BY [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date;
--------------------------------------------------------

But this gives me customers who have made a purchase in EITHER 2005 or 2006,
but I only want to see the ones from 2005.

Any help is appreciated.
Jason
 
J

Jeff Boyce

Jason

One way to approach this is to break it into smaller steps.

You can create a query that returns all the 2005 purchasers, right? You can
create a second query for all the 2006 customers, right?

Now use the New Query Wizard to help you create a "not in" query. You want
to know which "2005 customers are NOT IN the list of 2006 customers", right?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

jason

Thanks for the reply, Jeff.
How exactly do you create a "not in" query. I tried the Query Wizard and
didn't see that option.
Or do you mean start a new query based on two previous queries for 2005 and
2006 customers and see which customers are on one query and not the other?


Jeff Boyce said:
Jason

One way to approach this is to break it into smaller steps.

You can create a query that returns all the 2005 purchasers, right? You
can create a second query for all the 2006 customers, right?

Now use the New Query Wizard to help you create a "not in" query. You
want to know which "2005 customers are NOT IN the list of 2006 customers",
right?

Regards

Jeff Boyce
Microsoft Office/Access MVP


jason said:
I have a table with ID, first, last, purchase_date.
The items our customers get, they only purchase once a year. I'd like to
be able to find out which customers made a purchase last year (in 2005)
and NOT in 2006.

Right now I have:

SELECT [2005-2006 DIABCO AND NOBLE SHOE Patients].socsec, [2005-2006
DIABCO AND NOBLE SHOE Patients].f_name, [2005-2006 DIABCO AND NOBLE SHOE
Patients].l_name, [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date,
[2005-2006 DIABCO AND NOBLE SHOE Patients].procedure
FROM [2005-2006 DIABCO AND NOBLE SHOE Patients]
WHERE ((((([2005-2006 DIABCO AND NOBLE SHOE
Patients].[svc_date])=#1/1/2005#-#12/31/2005#) And (([2005-2006 DIABCO
AND NOBLE SHOE Patients].[svc_date])=#1/1/2006#-#12/31/2006#))=False))
ORDER BY [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date;
--------------------------------------------------------

But this gives me customers who have made a purchase in EITHER 2005 or
2006, but I only want to see the ones from 2005.

Any help is appreciated.
Jason
Click to expand...
Click to expand...
 
J

Jeff Boyce

Exactly. Access may call it "unmatched"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

jason said:
Thanks for the reply, Jeff.
How exactly do you create a "not in" query. I tried the Query Wizard and
didn't see that option.
Or do you mean start a new query based on two previous queries for 2005
and 2006 customers and see which customers are on one query and not the
other?


Jeff Boyce said:
Jason

One way to approach this is to break it into smaller steps.

You can create a query that returns all the 2005 purchasers, right? You
can create a second query for all the 2006 customers, right?

Now use the New Query Wizard to help you create a "not in" query. You
want to know which "2005 customers are NOT IN the list of 2006
customers", right?

Regards

Jeff Boyce
Microsoft Office/Access MVP


jason said:
I have a table with ID, first, last, purchase_date.
The items our customers get, they only purchase once a year. I'd like to
be able to find out which customers made a purchase last year (in 2005)
and NOT in 2006.

Right now I have:

SELECT [2005-2006 DIABCO AND NOBLE SHOE Patients].socsec, [2005-2006
DIABCO AND NOBLE SHOE Patients].f_name, [2005-2006 DIABCO AND NOBLE SHOE
Patients].l_name, [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date,
[2005-2006 DIABCO AND NOBLE SHOE Patients].procedure
FROM [2005-2006 DIABCO AND NOBLE SHOE Patients]
WHERE ((((([2005-2006 DIABCO AND NOBLE SHOE
Patients].[svc_date])=#1/1/2005#-#12/31/2005#) And (([2005-2006 DIABCO
AND NOBLE SHOE Patients].[svc_date])=#1/1/2006#-#12/31/2006#))=False))
ORDER BY [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date;
--------------------------------------------------------

But this gives me customers who have made a purchase in EITHER 2005 or
2006, but I only want to see the ones from 2005.

Any help is appreciated.
Jason
Click to expand...
Click to expand...
Click to expand...
 
J

jason

Thanks again Jeff.
I looked through the web for unmatched and found the answer from Microsoft:
http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#25

Gave me exactly what I was looking for.

Jeff Boyce said:
Exactly. Access may call it "unmatched"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

jason said:
Thanks for the reply, Jeff.
How exactly do you create a "not in" query. I tried the Query Wizard and
didn't see that option.
Or do you mean start a new query based on two previous queries for 2005
and 2006 customers and see which customers are on one query and not the
other?


Jeff Boyce said:
Jason

One way to approach this is to break it into smaller steps.

You can create a query that returns all the 2005 purchasers, right? You
can create a second query for all the 2006 customers, right?

Now use the New Query Wizard to help you create a "not in" query. You
want to know which "2005 customers are NOT IN the list of 2006
customers", right?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a table with ID, first, last, purchase_date.
The items our customers get, they only purchase once a year. I'd like
to be able to find out which customers made a purchase last year (in
2005) and NOT in 2006.

Right now I have:

SELECT [2005-2006 DIABCO AND NOBLE SHOE Patients].socsec, [2005-2006
DIABCO AND NOBLE SHOE Patients].f_name, [2005-2006 DIABCO AND NOBLE
SHOE Patients].l_name, [2005-2006 DIABCO AND NOBLE SHOE
Patients].svc_date, [2005-2006 DIABCO AND NOBLE SHOE
Patients].procedure
FROM [2005-2006 DIABCO AND NOBLE SHOE Patients]
WHERE ((((([2005-2006 DIABCO AND NOBLE SHOE
Patients].[svc_date])=#1/1/2005#-#12/31/2005#) And (([2005-2006 DIABCO
AND NOBLE SHOE Patients].[svc_date])=#1/1/2006#-#12/31/2006#))=False))
ORDER BY [2005-2006 DIABCO AND NOBLE SHOE Patients].svc_date;
--------------------------------------------------------

But this gives me customers who have made a purchase in EITHER 2005 or
2006, but I only want to see the ones from 2005.

Any help is appreciated.
Jason
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding Duplicates 9
Dates not sorting correctly in Report 9
using last , max from access query 2
Query Help 5
History Summary Analysis and Inspection Periods! 2
sumproduct with dates 4
pivot table help 2
Tranpose data from columns to rows 4

Top