Queries on tables with one-to-many relationships

[LeLe]

I have a table for my customers which is linked to a table for activities on
the customer name. Over the years most customers will have multiple records
in the activiites table as each time we have contact a record is added. The
most common example is a mailing which usually goes out a couple of times a
year. This time I am doing my mailing in stages and don't want to inadvently
mail to the same customer that I just sent catalogues to. I have created a
select Query using both of the above tables and set criteria using my
activity table field called activityID to eliminate those records for
customer with who were just mailed to. The problem is These same customer
have multiple activity records (other than the one I am trying to eliminate)
so they are still showing up in my query. My query is not giving me what I
need and although I think I understand why not, I can't figure out how to
redesign the query so it will give produce the results I am looking for.
ANy help is greatly appreicated.

 

[GeoffG]

You need to use a correlated subquery, something like this
(in SQL view):

SELECT tblOrgs.ID, tblOrgs.Surname, tblOrgs.Firstname
FROM tblOrgs
WHERE (((Exists (SELECT tblActivity.fkID FROM tblActivity
WHERE (((tblActivity.Activity)="May" And fkID =
tblOrgs.ID)))=False));

To simplify construction of the above query, first write the
subquery as a separate query, i.e:

SELECT tblActivity.fkID FROM tblActivity WHERE
(((tblActivity.Activity)="May"

The idea here is that this query will select all foreign
keys (fkID) in the Activity table for those organisations
that have already received May's leaflet.

Change the subquery to SQL View, select the SQL statement
and press CTRL-C to copy the subquery SQL statement to the
Windows clipboard.

Create the main query (based on the Organisations table).

Enter open and close parenthesis () in the criterion row.
I used the criterion row of the ID (primary key) column.

Between the parentheses, paste in the subquery.

Enter NOT EXISTS before the first parenthesis.

Carefully enter AND fkID = tblOrgs.ID as shown above.

Be especially careful with the open and close parentheses.
Access will complain if you get them wrong.

After you save the query and re-open it, Access will move
things around a bit in design view, but it will be
equivalent to what you entered.

View the query in SQL view to see the full statement.

Run the query.
Check that it selects the records you want.


Geoff

 

[Fred]

I noticed that nobody answered so far. Below is our low tech method of doing
what you describe; I was waiting / hoping to see / learn a more elegant
answer from someone smarter than me.


Have a Temp1 temporary field in the customers table. Run an update query to
empty the field, and then an update query that goes through all of the
activity records and whenever the "just mailed to" conditions are met, set
the temp1 field to "Y". Then select customer records where the temp1 field
is null.