How to accoplish this query?

's Computer Specifications

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

's Computer Specifications

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" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
news:(E-Mail Removed)...
>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
>
>

's Computer Specifications

max(svc_date) between .... ?

"jason" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
news:(E-Mail Removed)...
>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
>
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
> news:(E-Mail Removed)...
>>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
>>
>>
>
>

's Computer Specifications

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jason" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
>> news:(E-Mail Removed)...
>>>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
>>>
>>>
>>
>>
>
>

's Computer Specifications

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

Gave me exactly what I was looking for.

"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Exactly. Access may call it "unmatched"...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "jason" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <jasonsantos-NOSPAM-@allstatesmedical.com> wrote in message
>>> news:(E-Mail Removed)...
>>>>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
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MS Access metadata repository generation query, matrix generationquery, table-generation query, array-generation query.	Roy Roebuck	Microsoft Access Database Table Design	1	20th Dec 2008 07:23 PM
Set sub (embedded?) query parameters using VBA, call sub query in primary query with parameters completed	Kelii	Microsoft Access	4	5th Feb 2007 03:01 AM
verfiy a query like Query Analyzer's parse query function	=?Utf-8?B?a2V2aW4=?=	Microsoft C# .NET	1	24th Dec 2005 01:33 AM
Sorry for confusion :Re: Can I insert empyt record (blank row) in a query based on condition an asp query	Dave	Microsoft Frontpage	0	18th Oct 2004 03:42 PM
'Sanity Check' - combine macro, delete query and make table query	Mike Webb	Microsoft Access Queries	2	17th Feb 2004 02:20 PM