Help with query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We use an Access 2000 database. One of the tables, clients, contains contact
dates which record the dates clients make contact with a particular project.
Clients may have made contact over several years, then gone away, and come
back or they may be completely new (ie first visit). I am trying to devise a
query to pull out completely new clients for a particular period. In other
words people who have no previous contact. So if a person has a contact in
2006 I want to be sure that there are no previous contacts in other years.

Obviously it is easy to pull out contacts for a particular period. But how
do you make sure they are not in another period?

Any ideas would be most appreciated!
 
SELECT *
FROM clients
WHERE client_id in (
SELECT c.client_id
FROM clients c
GROUP BY c.client_id
HAVING Count(c.client_id)=1)
AND period = 2006 ;

The above should get you started on the right track.
 
C said:
We use an Access 2000 database. One of the tables, clients, contains contact
dates which record the dates clients make contact with a particular project.
Clients may have made contact over several years, then gone away, and come
back or they may be completely new (ie first visit). I am trying to devise a
query to pull out completely new clients for a particular period. In other
words people who have no previous contact. So if a person has a contact in
2006 I want to be sure that there are no previous contacts in other years.

Obviously it is easy to pull out contacts for a particular period. But how
do you make sure they are not in another period?

Any ideas would be most appreciated!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (untested):

SELECT C1.contact_id
FROM Clients As C1 LEFT JOIN Clients AS C2
ON C1.contact_id = C2.contact_id
WHERE C1.contact_date BETWEEN #1/1/2006# AND #12/31/2006#
AND C2.contact_date NOT BETWEEN #1/1/2006# AND #12/31/2006#
AND C2.contact_id IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK7LMYechKqOuFEgEQITLQCgxw6XnapRNKOdt/752e1nHPRwu3EAoIDs
5YPA6eC9Z5RQEp/cxEgGoDQM
=7P2d
-----END PGP SIGNATURE-----
 
Crumbs that looks complicated. I forgot to say I'm a bit of a beginner! Is it
possible to build something similar with the query wizard? Or maybe you could
explain, step by step, what this statement is doing? Many thanks for your
help!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This query is what is known as a self-joined query, meaning the same
table is joined to itself. The join is by the contact_id. In this case
the C1 table represents the contacts in 2006. The C2 table represents
the contacts that are NOT in 2006. The LEFT JOIN combined w/ the
"C2.contact_id IS NULL" criteria "says": show all contact_ids in table
C1 (2006) that are not in table C2. IOW, show contacts that are only in
2006.

Read the Access Help articles on LEFT/RIGHT JOINS for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLF8QoechKqOuFEgEQKrWwCg0059e1fWVHz0QDM7YEB/BwQ39q8AoKMN
kefGuu2YRQW+e5eSnmWlknK+
=xaoS
-----END PGP SIGNATURE-----
 
Well I entered the information as you said but the query came up with no
data, which I feel cannot be correct. Sorry to appear an idiot but can you
explain what the IS NULL does, in this statement?!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The IS NULL is means in the comparison between the 2 datasets (table C1,
2006 data, and table C2, all other years' data), when any contact_id is
in table C1 and not in table C2, then show the record (row). This only
works when the join between the tables is a LEFT JOIN w/ the C1 table on
the left and the C2 table on the right.

Of course, all the table and column names have to be change to suit your
DB. If you're joining to another table in the query, that could affect
the result.

You could show the query you're trying to use and a sample of the data
that is being queried. Right now I'm just guessing.

Read the Access SQL help articles on LEFT JOIN for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLL5VoechKqOuFEgEQL7DgCg9+KISm5FC7EWJQrqV0I4LP0fplcAnj8U
zpza4NKCRqmlI4CNm6d/xeqH
=8cpz
-----END PGP SIGNATURE-----
 
Well I did try to look at this carefully. I also looked at the resulting
expression in design view to see if I could make more sense of it. But no
records are returned so I feel the statement is probably not quite right. I
might be completely wrong but the is null statement really correct? The
table will never NOT have a contactid, though it will of course have contact
dates that are not in 2006.
 
Back
Top