How do I get my query results into text boxes on my form??

[EdNerd]

In Access 2007, I have the following:
-- tblEmployees; employee data with EmplNum as PK
-- qryActiveEmpl; returns all data from tblEmployees for all active employees
-- tblTraining; a list of all training classes with ClassName as PK
-- tblAllTrn joins tblEmployees and tblTraining as many-to-many using EmplNum and ClassName both as fk, and includes the dates any employee completed any training class
-- qryTrn2012; draws from tblEmployees and tblAllTrn, & shows the dates all employees completed any training in 2012
-- frmEmplData; record source is qryActiveEmpl

I want to put text boxes on my form for each individual training class and show the date completed. (A subform does show the correct results, but for various reasons is not preferred *if* this other way can be made to work). So -- for each employee shown on the form, I want to pull the date from qryTrn2012 that shows the date that person completed that class, or a Null if no date present.

I can't seem to make it happen! I even went so far as to build separate queries for each of the 17 classes. But I can't get the date to show up in the text box.

Can someone please drop-kick me in the right direction?

Ed

 

[xenou]

Lacking test data, I've written this but not tested it:

[COLOR="#008000"]--Outer Query: All employees cross join all classes[/COLOR]
[COLOR="#000080"]SELECT[/COLOR]
	T1.EmplNum, 
	T2.ClassName,
	T3.CompletedDate
[COLOR="#000080"]FROM[/COLOR] 
	tblEmployees T1, tblTraining T2

[COLOR="#000080"]LEFT JOIN[/COLOR]
    [COLOR="#008000"]--Inner Query: employees with completed dates[/COLOR]
    ([COLOR="#000080"]SELECT[/COLOR] tblAllTrn.EmplNum, 
		tblAllTrn.ClassName, tblAllTrn.CompletedDate
        [COLOR="#000080"]FROM[/COLOR] 
            tblAllTrn
    ) [COLOR="#000080"]AS[/COLOR] T3
    [COLOR="#000080"]ON[/COLOR] T1.EmplNum = T3.EmplNum

[COLOR="#000080"]WHERE[/COLOR] 
	T1.IsInactive = False

[COLOR="#000080"]ORDER BY[/COLOR]
 T1.EmplNum, t2.ClassName;

You'll have to remove the comments - Access will balk at them.
If it doesn't work, post back.

I've assumed you have this table structure (looks good):
<img src="http://northernocean.net/etc/mrexcel/20130107_capture.png" />

 

[EdNerd]

Wow! Thank you so much for the assist.
Gonna hafta chew on that a bit to understand.

Cheers!
Ed

 

[xenou]

Okay. If you still have trouble provide some test data and we can work on the same dataset. To "deconstruct" a (somewhat) complex query, you can run the outer and inner queries separately to see what they return (you could even make-table them into temp tables that you can view and query on):

Outer query:

SELECT
	T1.EmplNum, 
	T2.ClassName,
	T3.CompletedDate
FROM 
	tblEmployees T1, tblTraining T2

Inner Query:

SELECT tblAllTrn.EmplNum, 
    tblAllTrn.ClassName, tblAllTrn.CompletedDate
FROM 
    tblAllTrn

The former should be a list of all employees and classes. The second a list of only classes that employees have taken, with complete dates. By LEFT JOIN'ing the first to the second, you should get another list of all employees and classes, this time showing complete dates where applicable.

ξ