How to create a Query for a report based on a unrelated lookup table.

[Wazza]

Hello All

I am new to posting on forums though i have browsed them often.

The question i have is to do with how to produce a Report using data that is
only remotely related to the Report's content. Let me explain.

The Report that i have created has a simple layout and is called
[StaffNotYetDoneCourse]. The Report's RowSource is the Staff Table. It has
a Title that is derived from a Table Column ([Courses].[Title]) and a
tabuated list of staff member's details Staff.StaffNo, Staff.LastName, Staff.
FirstName, and two rectangle objects ([Priority] and [Preferred Start Date]).
The resultant Report is used as a proformer for data collection.

There are three tables involved in the creation of the Report. Staff,
Training, and Courses. The Training Table has a Autonumber Primary Key and
is related to the Staff Table on the StaffNo (Text) Column. The Courses
Table has a list of Course.Titles that are used as Look-Up values for the
Training Table. The abreviated structure is as follows.

TABLE [Staff]
StaffNo (Text) Primary Key
LastName (Text)
FirstName (Text)
....
....
FinishDate (Date/Time)

TABLE [Training]
ID (Autonumber) Primary Key
StaffNo (Text) --- [Staff].[StaffNo]
CourseID (Number) --- [Courses].[ID]
StartDate (Date/Time)
FinishDate(Date/Time)
....
....
Comments (Memo)

TABLE [Courses]
ID (Autonumber) Primary Key
Title (Text)
....
....
Description (Memo)

Before Previewing the StaffNotYetDoneCourse Report the User has to select the
Report from a ListBox Me.rptName and select a [Courses].[ID] value from a
ComboBox that has a [Courses].[Title] text display.

I then open the Report with the following DoCmd. (This is modified cut &
paste I hope it makes logical sense)

Dim WC As String
Dim rs As Recordset
WC = "(Staff.StaffNo Not In (SELECT DISTINCT Staff.StaffNo " & _
"FROM Staff INNER JOIN Training ON Staff.StaffNo =
Training.StaffNo " & _
"WHERE (" & _
"((Training.CourseID=" & Me![txtCourse] & ") AND
(Not (Training.FinishDate) Is Null)) " & _
")" & _
"" & _
") " & _
"AND (Staff.FinishDate Is Null) "
Set rs = CurrentDb.OpenRecordset("Courses", dbOpenSnapshot)
rs.FindFirst "[ID] = " & Me![txtCourse]
If Not rs.NoMatch Then
DoCmd.OpenReport Me![Name], acViewDesign, , , acHidden
Reports(Me![Name])!TitleLabel2.Caption = "Course Name: " & rs![Title]
DoCmd.Close acReport, Me![Name], acSaveYes
End If
DoCmd.OpenReport Me![Name], acViewPreview, , WC

This works fine. But now my boss wants to print a StaffNotYetDoneCourse for
every [Course].[Title] and I do not know how to do this.

I need to be able to create a Query that results in every one who has not
done every course. I could put exactly what i have done already into a loop
(While Not Courses.EOF) but that does not allow me to do an initial preview
because i have to open the Report in Design View to Insert the Courses.Title
value for each course. I am finding this difficult because there is no
direct relation between Staff and Courses and the resultant recordset is
implied data because there is no record of who has not done a course only a
record of those who have done the course or at least who are booked to do the
course with a Null value in the FinishDate column.

I hope this makes sense to someone as i am finding it hard to understand and
i am the one doing it.

Thanks in advance for any help you can give me for this.
Wazza

 

[John Spencer]

Not sure this will work, but I would try the following. It will be slow
with large datasets.

1. Get all staff and training.

SELECT Staff.StaffNo, Training.CourseID
FROM Staff INNER JOIN Training
ON Staff.StaffNo = Training.StaffNo

2. Use that in a cartesian query to strip out relevant records.

SELECT Course.*, Staff.*
FROM Course, Staff, Query1
WHERE Course.CourseID <> Query1.CourseID
AND Staff.StaffNo <> Query1.StaffNo

You might be able to do this all in one query. Again I think this would be
slow with large datasets

SELECT *
FROM Course, Staff
WHERE CourseID & StaffNo NOT IN
(SELECT CourseID & StaffNo
FROM Training)

I keep thinking that there has to be a more efficient way to get the
results, but my mind isn't giving me any other solution.

Hello All

I am new to posting on forums though i have browsed them often.

The question i have is to do with how to produce a Report using data that
is
only remotely related to the Report's content. Let me explain.

The Report that i have created has a simple layout and is called
[StaffNotYetDoneCourse]. The Report's RowSource is the Staff Table. It
has
a Title that is derived from a Table Column ([Courses].[Title]) and a
tabuated list of staff member's details Staff.StaffNo, Staff.LastName,
Staff.
FirstName, and two rectangle objects ([Priority] and [Preferred Start
Date]).
The resultant Report is used as a proformer for data collection.

There are three tables involved in the creation of the Report. Staff,
Training, and Courses. The Training Table has a Autonumber Primary Key
and
is related to the Staff Table on the StaffNo (Text) Column. The Courses
Table has a list of Course.Titles that are used as Look-Up values for the
Training Table. The abreviated structure is as follows.

TABLE [Staff]
StaffNo (Text) Primary Key
LastName (Text)
FirstName (Text)
...
...
FinishDate (Date/Time)

TABLE [Training]
ID (Autonumber) Primary Key
StaffNo (Text) --- [Staff].[StaffNo]
CourseID (Number) --- [Courses].[ID]
StartDate (Date/Time)
FinishDate(Date/Time)
...
...
Comments (Memo)

TABLE [Courses]
ID (Autonumber) Primary Key
Title (Text)
...
...
Description (Memo)

Before Previewing the StaffNotYetDoneCourse Report the User has to select
the
Report from a ListBox Me.rptName and select a [Courses].[ID] value from a
ComboBox that has a [Courses].[Title] text display.

I then open the Report with the following DoCmd. (This is modified cut &
paste I hope it makes logical sense)

Dim WC As String
Dim rs As Recordset
WC = "(Staff.StaffNo Not In (SELECT DISTINCT Staff.StaffNo " & _
"FROM Staff INNER JOIN Training ON Staff.StaffNo
=
Training.StaffNo " & _
"WHERE (" & _
"((Training.CourseID=" & Me![txtCourse] & ") AND
(Not (Training.FinishDate) Is Null)) " & _
")" & _
"" & _
") " & _
"AND (Staff.FinishDate Is Null) "
Set rs = CurrentDb.OpenRecordset("Courses", dbOpenSnapshot)
rs.FindFirst "[ID] = " & Me![txtCourse]
If Not rs.NoMatch Then
DoCmd.OpenReport Me![Name], acViewDesign, , , acHidden
Reports(Me![Name])!TitleLabel2.Caption = "Course Name: " & rs![Title]
DoCmd.Close acReport, Me![Name], acSaveYes
End If
DoCmd.OpenReport Me![Name], acViewPreview, , WC

This works fine. But now my boss wants to print a StaffNotYetDoneCourse
for
every [Course].[Title] and I do not know how to do this.

I need to be able to create a Query that results in every one who has not
done every course. I could put exactly what i have done already into a
loop
(While Not Courses.EOF) but that does not allow me to do an initial
preview
because i have to open the Report in Design View to Insert the
Courses.Title
value for each course. I am finding this difficult because there is no
direct relation between Staff and Courses and the resultant recordset is
implied data because there is no record of who has not done a course only
a
record of those who have done the course or at least who are booked to do
the
course with a Null value in the FinishDate column.

I hope this makes sense to someone as i am finding it hard to understand
and
i am the one doing it.

Thanks in advance for any help you can give me for this.
Wazza

 

[Walter via AccessMonster.com]

Thank you John.

The Staff Table has 51 rows.
The Courses Table has 92 rows.
The Training Table has 569 rows but only 154 of them meet the (NOT Training.
FinishDate IS NULL) criteria.

The first method returns 700,700 rows or 4,692 rows with SELECT DISTINCT in
Query2.
The second method produces 48,993 rows or 2,463 rows with SELECT DISTINCT and
takes just over 5 minutes to calculate (P4 2.4GHz).

Both are incorrect results.

First Method

Query1 =
SELECT Staff.StaffNo AS QStaffNo, Training.CourseID AS QCourseID
FROM Staff INNER JOIN Training ON Staff.StaffNo=Training.StaffNo
WHERE NOT (Training.FinishDate IS NULL);

Query2 =
SELECT DISTINCT Courses.Title, Staff.LastName
FROM Courses, Staff, Query1
WHERE Courses.ID <> Query1.QCourseID
AND Staff.StaffNo <> Query1.QStaffNo

Second Method

Query3 =
SELECT DISTINCT Courses.Title, Staff.LastName
FROM Courses, Staff INNER JOIN Training ON Staff.StaffNo = Training.StaffNo
WHERE Courses.ID & "," & Staff.StaffNo NOT IN (SELECT Training.CourseID & ","
& Training.StaffNo FROM Training WHERE NOT (Training.FinishDate IS NULL));

With 51 staff and 92 courses there are 4692 combinations. 154 of those
combinations can be found in the filtered Training Table. The Query result
should be 4692 - 154 = 4538 rows.

Training.CourseID and Training.StaffNo are both indexed.

Thank you very much for your time John. Like you, I know there is a solution
but I am so far unable to find it and i just know it will be simple.

Walter Parker (Wazza)

Not sure this will work, but I would try the following. It will be slow
with large datasets.

1. Get all staff and training.

SELECT Staff.StaffNo, Training.CourseID
FROM Staff INNER JOIN Training
ON Staff.StaffNo = Training.StaffNo

2. Use that in a cartesian query to strip out relevant records.

SELECT Course.*, Staff.*
FROM Course, Staff, Query1
WHERE Course.CourseID <> Query1.CourseID
AND Staff.StaffNo <> Query1.StaffNo

You might be able to do this all in one query. Again I think this would be
slow with large datasets

SELECT *
FROM Course, Staff
WHERE CourseID & StaffNo NOT IN
(SELECT CourseID & StaffNo
FROM Training)

I keep thinking that there has to be a more efficient way to get the
results, but my mind isn't giving me any other solution.

Hello All

[quoted text clipped - 101 lines]

Thanks in advance for any help you can give me for this.
Wazza

 

[John Spencer]

Sorry to take so long to get back to you on this. It's been processing in
my subconscious. I've come up with one more method.
Basically you produce a list of all staff with all courses (using a
cartesian join). Using that and the Training table you do an unmatched
query to strip out all the matches in the cartesian join query.


Using two queries.

Query One - Produce all Courses * All staff list. Add any other fields you
want from the Courses table, but you need at least the ID field.

SELECT Staff.*, Courses.ID as CourseID
FROM Staff, Courses

That should give you 51 * 92 rows (4,692 rows)

Query Two.
SELECT QueryOne.*
FROM QueryOne LEFT JOIN Training
ON QueryOne.StaffNo = Training.StaffNo
AND QueryOne.CourseID =Training.CourseID
WHERE Training.StaffNo is Null

You may be able to combine that all into one query as

SELECT Q.*
FROM (
SELECT Staff.*, Courses.ID as CourseID
FROM Staff, Courses
) as Q
LEFT JOIN Training
ON Q.StaffNo = Training.StaffNo
AND Q.CourseID =Training.CourseID
WHERE Training.StaffNo is Null

If this works (and I believe it should) then celebrate. I knew it should be
simple but I was looking at this whole thing from the wrong angle.

Thank you John.

The Staff Table has 51 rows.
The Courses Table has 92 rows.
The Training Table has 569 rows but only 154 of them meet the (NOT
Training.
FinishDate IS NULL) criteria.

The first method returns 700,700 rows or 4,692 rows with SELECT DISTINCT
in
Query2.
The second method produces 48,993 rows or 2,463 rows with SELECT DISTINCT
and
takes just over 5 minutes to calculate (P4 2.4GHz).

Both are incorrect results.

First Method

Query1 =
SELECT Staff.StaffNo AS QStaffNo, Training.CourseID AS QCourseID
FROM Staff INNER JOIN Training ON Staff.StaffNo=Training.StaffNo
WHERE NOT (Training.FinishDate IS NULL);

Query2 =
SELECT DISTINCT Courses.Title, Staff.LastName
FROM Courses, Staff, Query1
WHERE Courses.ID <> Query1.QCourseID
AND Staff.StaffNo <> Query1.QStaffNo

Second Method

Query3 =
SELECT DISTINCT Courses.Title, Staff.LastName
FROM Courses, Staff INNER JOIN Training ON Staff.StaffNo =
Training.StaffNo
WHERE Courses.ID & "," & Staff.StaffNo NOT IN (SELECT Training.CourseID &
","
& Training.StaffNo FROM Training WHERE NOT (Training.FinishDate IS NULL));

With 51 staff and 92 courses there are 4692 combinations. 154 of those
combinations can be found in the filtered Training Table. The Query
result
should be 4692 - 154 = 4538 rows.

Training.CourseID and Training.StaffNo are both indexed.

Thank you very much for your time John. Like you, I know there is a
solution
but I am so far unable to find it and i just know it will be simple.

Walter Parker (Wazza)

Not sure this will work, but I would try the following. It will be slow
with large datasets.

1. Get all staff and training.

SELECT Staff.StaffNo, Training.CourseID
FROM Staff INNER JOIN Training
ON Staff.StaffNo = Training.StaffNo

2. Use that in a cartesian query to strip out relevant records.

SELECT Course.*, Staff.*
FROM Course, Staff, Query1
WHERE Course.CourseID <> Query1.CourseID
AND Staff.StaffNo <> Query1.StaffNo

You might be able to do this all in one query. Again I think this would
be
slow with large datasets

SELECT *
FROM Course, Staff
WHERE CourseID & StaffNo NOT IN
(SELECT CourseID & StaffNo
FROM Training)

I keep thinking that there has to be a more efficient way to get the
results, but my mind isn't giving me any other solution.

Hello All

[quoted text clipped - 101 lines]

Thanks in advance for any help you can give me for this.
Wazza

 

[Walter via AccessMonster.com]

Thanks John. That works. I had a little involuntary chuckle when I saw it.
Thank you for your very valuable time given so graciously.

Walter

Sorry to take so long to get back to you on this. It's been processing in
my subconscious. I've come up with one more method.
Basically you produce a list of all staff with all courses (using a
cartesian join). Using that and the Training table you do an unmatched
query to strip out all the matches in the cartesian join query.

Using two queries.

Query One - Produce all Courses * All staff list. Add any other fields you
want from the Courses table, but you need at least the ID field.

SELECT Staff.*, Courses.ID as CourseID
FROM Staff, Courses

That should give you 51 * 92 rows (4,692 rows)

Query Two.
SELECT QueryOne.*
FROM QueryOne LEFT JOIN Training
ON QueryOne.StaffNo = Training.StaffNo
AND QueryOne.CourseID =Training.CourseID
WHERE Training.StaffNo is Null

You may be able to combine that all into one query as

SELECT Q.*
FROM (
SELECT Staff.*, Courses.ID as CourseID
FROM Staff, Courses
) as Q
LEFT JOIN Training
ON Q.StaffNo = Training.StaffNo
AND Q.CourseID =Training.CourseID
WHERE Training.StaffNo is Null

If this works (and I believe it should) then celebrate. I knew it should be
simple but I was looking at this whole thing from the wrong angle.

Thank you John.

[quoted text clipped - 83 lines]