In Access, you would have a query to "hold" the data.
For instance to get all persons that had an appointment in 2006 or 2007,
your query would be something like the following.
SELECT PersonID
FROM Appointments
WHERE Appointments.AppointmentDate Between #2006/01/01# and #2007/12/31#
To get all those that had an appointment in 2008
SELECT PersonID
FROM Appointments
WHERE Appointments.AppointmentDate Between #2008/01/01# and #2008/12/31#
Now you can treat those two queries as if they were tables. So to get
anyone that had an appointment in 06 or 07 and NOT in 08 you would use a
query like the following.
SELECT q06_07.PersonID
FROM q06_07 LEFT JOIN q08
ON q06_07.PersonID = q08.PersonID
WHERE q08.PersonID is Null
And since you can use subqueries as sources, you can do something like
the following. AS LONG AS your table and field names consist of ONLY
letters, numbers, and the underscore character.
SELECT q06_07.PersonID
FROM
(SELECT PersonID
FROM Appointments
WHERE Appointments.AppointmentDate Between #2006/01/01#
and #2007/12/31#
) as q06_07
LEFT JOIN
(SELECT PersonID
FROM Appointments
WHERE Appointments.AppointmentDate Between #2008/01/01#
and #2008/12/31#) as q08
ON q06_07.PersonID = q08.PersonID
WHERE q08.PersonID is Null
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
mark said:
Take for example a table of names and appointment dates.
I want to output a list of names that had appointments in 2006 or 2007, but
not if they had an appointment in 2008.
If I join the table back on itself, I need conditional statements.
However, if I create a HOLD FILE of names that had appointments in 2006 or
2007 and a different HOLDFiLE of names that had appoinments in 2008, I could
MATCH the two HOLDFILES and output all names that did not MATCH or ALL except
the intersection.
So would I Gabiffle a transintelligental-field malfiesance or tuple an apple
or
a turnip instead?. . . . try English, sometimes it can be more intelligble
than penguinnerd.
Stefan Hoffmann said:
hi Mark,
mark r wrote:
how do you write a query to do a sort of VENN [diagram] UNION or Interesection
between two tables? In the 4GL FOCUS Language, you would query table 1
and place the output on HOLD sorted by key 1. Then query Table 2 and place
the output on HOLD sorted by key 1. Then you would MATCH the two HOLDFILES
on Key 1,
and output UNION or INTERSECTION.
Let's assume that both tables/queries have at least one tuple of
n-fields in common.
The union set:
SELECT tuple
FROM table1
UNION
SELECT tuple
FROM table2
To control the output, you may use UNION ALL, take a closer look at the OH.
The intersection set:
SELECT tuple
FROM table1 T1
INNER JOIN table2 T2
ON T1.tuple = T2.tuple
Assume you have a tuple of three fields, then you need:
SELECT *
FROM table1 T1
INNER JOIN table2 T2
ON T1.field1 = T2.field1
AND T1.field2 = T2.field2
AND T1.field3 = T2.field3
In the union set you can only return a tuple or any other set of fields
both tables have in common. In the intersection query you can return all
fields.
mfG
--> stefan <--