Join 2 queries

[Guest]

Ok, I have 2 queries qryTardyToady and qryTeacherCount. I need 1 query to
join both of these in a particular way.

I have a combobox, cboTardySelecton, a form, frmTardyUpdate, that needs to
be populated. The population is the join of these 2 queries. The only kids
in that belong in the combo box are student who have >3 Count of Teacher from
qryTeacherCount and show up in qryTardyToday tardy in the class that is >3
in qryTeacherCount.

qryTeacherCount counts the number of times a student is tardy in each
teacher's class.

qryTardyToday only show students who were absent today and the teacher they
were absent in.

Example: Ron is tardy today for the 3rd time in Ms. Jones' class. He will
not be in the list for this offense, but he is tardy later in Mr. Davis'
class. Since this is his fourth offense in Mr. Davis' class, he will then be
in the list for Mr. Davis' tardy, but not for Ms. Jones'.

I cannot figure out how to join them to get the results that I need. This
is WAY beyond me. I am using access 2003 with a 2000 format.

 

[John Spencer (MVP)]

Not enough detail on the structure of your queries, so lots of assumptions would
have to be made. Suggest you post the SQL statements for your two queries.

 

[Jack MacDonald]

Have you looked at UNION queries? I think that's the key to what you
want to accomplish.

There are some caveats -- the number of fields (and field type,
too???) must be identical in both queries. If "John" appears in both
queries, he will be duplicated in the UNION query. You could probably
do a subsequent SUM query to eliminate duplicates.

Lastly, UNION queries must be built manually. Open a new query and
view its SQL. Then type something like:

SELECT * from qryTardyToady
UNION
SELECT * from qryTeacherCount

Ok, I have 2 queries qryTardyToady and qryTeacherCount. I need 1 query to
join both of these in a particular way.

I have a combobox, cboTardySelecton, a form, frmTardyUpdate, that needs to
be populated. The population is the join of these 2 queries. The only kids
in that belong in the combo box are student who have >3 Count of Teacher from
qryTeacherCount and show up in qryTardyToday tardy in the class that is >3
in qryTeacherCount.

qryTeacherCount counts the number of times a student is tardy in each
teacher's class.

qryTardyToday only show students who were absent today and the teacher they
were absent in.

Example: Ron is tardy today for the 3rd time in Ms. Jones' class. He will
not be in the list for this offense, but he is tardy later in Mr. Davis'
class. Since this is his fourth offense in Mr. Davis' class, he will then be
in the list for Mr. Davis' tardy, but not for Ms. Jones'.

I cannot figure out how to join them to get the results that I need. This
is WAY beyond me. I am using access 2003 with a 2000 format.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security