MATCH

[mark r]

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.

In ACCESS QUERY, I imagine you write QUERY 1 and QUERY 2, and somehow
reference QUERY 1 from within QUERY 2, perhaps onthe WHERE line.

 

[Stefan Hoffmann]

hi Mark,

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 <--

 

[mark r]

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.

hi Mark,

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 <--

 

[Stefan Hoffmann]

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.

What's your problem?

You started talking about intersections in a Venn diagram which is plain
old set theory...


mfG
--> stefan <--

 

[John Spencer]

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
'====================================================

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.

hi Mark,

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 <--

 

[mark r]

What makes you think that everyone who submits a question is a computer
science major? This is a public forum. Certainly users can be non-technical.
If you read between the lines, you can figure out who your audience is and
serve them better. Read Spencer's answer. Takes pride in workmanship. Try
it, could be rewarding. Thanks for the quick response and trying, though.

 

[Stefan Hoffmann]

hi,

What makes you think that everyone who submits a question is a computer
science major?

Frankly said, the terms you used.

Everybody I know who is talking about intersections, Venn diagram's or
fourth generation language is someone who had at least information
sciences as a subsidiary subject.

On the otherside I have to assume that a person using these terms is
capable of using any dictionary to translate it.


mfG
--> stefan <--

 

[John Spencer]

Well, I kind of took you as one of the more learned. You were using the
math terminology that is associated with SET theory (and which Codd
based his relational database treatise).

You did talk abount 4GL FOCUS in a way that led me to believe you might
have exposure to other languages. That said we all have to make guesses
at the level of Access knowledge that others have. Sometimes we are way
off and the user gets offended because they feel they were treated as
ignorant morons and other times the poster is upset because the answer
was too technical.

So Stefan may have appropriately responded the way he did with general
information.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mark r]

JOHN,

I HOPE YOU WILL RESPOND AS YOUR LAST ADVICE WORKED GREAT AND I WAS ABLE TO
UNDERSTAND YOUR STYLE OF EXPlANATION.

I have a query now that outputs the records I am interested in. However,
the query will not let me update any of the fields. Specifically, I wish to
"mark TRE" the PRINTFLAG field so that I can run a report and print of only
the records I am interested in.

I assume there is no way to allow me to just point and click. So, perhaps
there is a way i could write another query which will eliminate duplicate
records from my output file. One way might be to select the first record from
any duplicated last names.

Another approach I thought of was to use SET in an currentdb.Execute
where somehow I match the lastnames of the query output to the original
source table and SET printflag to TRUE on Match . However, the wource table
is keyed on IDfield and my query "duplicates " are identified by lastname.

printflag lastname dateofappointment

alton 11/01/2007
alton 03/02/2007
alton 05/01/2006

I want to print only one of these into a label using REPORT.
I cannot seem to click on printflag. I donot know of a filter or query
function
FIRST.

please reference your previews reply on how to do a MATCH using Join.

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
'====================================================

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.

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 <--

 

[mark r]

John,

I hope you will respond, as your last advice regarding how to do a MATCH
using Join worked for me.

my output is


printflag lastname dateofappointment IDfield

alton 11/01/2007 1287
alton 01/04/2007 1127
alton 04/01/2006 987
bion 12/05/2007 971
bion 12/01/2007 872
bion 11/01/2006 287


The original table is keyed by IDfield. I only want to print out a label
for one of each last name. The query will not allow me to toggle the
printflag to "TRUE".
I do not know how to write a query to .FIRST output only the first
occurrence of each lastname.

what should I do? I am not a computer programmer by training, so use simple
language please.

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
'====================================================

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.

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 <--