Problem with Query and Outer Joins

[Matt]

I have a query based off of many other queries and it contains a ton of outer
joins. What i want the query to do is spit out one record at the end...and it
works in that regard. My problem comes when I try using the serverfilter
property in my Data Access Page that is based off of this query. It returns
nothing. I tried some debugging and discovered that even making a new query
that is based off this last query, it doesn't even work. I think there's
something wrong with Access and OUTER JOINS. Here is my query:

SELECT TOP 1 EmployeeTable.EmployeeID AS EmpID, UpdaterQuery.EntryDate,
UpdaterQuery.Updater, DevActionQuery.DevAction, TimeFrameQuery.TimeFrame,
PossibleQuery.Possible, AdditionalQuery.Additional, FRFLeaderQuery.FRFLeader,
FRFCompQuery.FRFComp, FRFJobQuery.FRFJob, FRFPressureQuery.FRFPressure,
FRFOrgQuery.FRFOrg, FRFOtherQuery.FRFOther, ReadinessQuery.Readiness,
WillingQuery.Willing, EmployeeTable.*
FROM ((((((((((((EmployeeTable LEFT JOIN TimeFrameQuery ON
EmployeeTable.EmployeeID = TimeFrameQuery.EmployeeID) LEFT JOIN
DevActionQuery ON EmployeeTable.EmployeeID = DevActionQuery.EmployeeID) LEFT
JOIN AdditionalQuery ON EmployeeTable.EmployeeID =
AdditionalQuery.EmployeeID) LEFT JOIN PossibleQuery ON
EmployeeTable.EmployeeID = PossibleQuery.EmployeeID) LEFT JOIN FRFCompQuery
ON EmployeeTable.EmployeeID = FRFCompQuery.EmployeeID) LEFT JOIN FRFJobQuery
ON EmployeeTable.EmployeeID = FRFJobQuery.EmployeeID) LEFT JOIN
FRFLeaderQuery ON EmployeeTable.EmployeeID = FRFLeaderQuery.EmployeeID) LEFT
JOIN FRFOrgQuery ON EmployeeTable.EmployeeID = FRFOrgQuery.EmployeeID) LEFT
JOIN FRFOtherQuery ON EmployeeTable.EmployeeID = FRFOtherQuery.EmployeeID)
LEFT JOIN FRFPressureQuery ON EmployeeTable.EmployeeID =
FRFPressureQuery.EmployeeID) LEFT JOIN ReadinessQuery ON
EmployeeTable.EmployeeID = ReadinessQuery.EmployeeID) LEFT JOIN UpdaterQuery
ON EmployeeTable.EmployeeID = UpdaterQuery.EmployeeID) LEFT JOIN WillingQuery
ON EmployeeTable.EmployeeID = WillingQuery.EmployeeID;


Please help!

Thanks,
Matt

 

[Gary Walter]

No solution here...just some thoughts
that may help...

An outer join implies a "preserved table"

it looks like you want to preserve all of
EmployeeTable and only return any matching
fields from the other table/queries....

you went to all this trouble to preserve the table,
then only select "TOP 1" (plus, without any SORT BY
clause to determine what is top)???....

then....
if you are returning only one record,
what is there to filter?

If you remove the "TOP 1" then
you may have more than one record
to filter, but you have to be careful
applying a filter...

the only filter you can apply to an "inner table"
of an outer join and expect a *meaningful* result
is to to test if innertable.field IS NULL.

any other filtering must be applied to the
inner table first, then that filtered query
used in the outer join query in place of
the inner table....

if you want to filter on the "preserved table,"
in most cases one might ask what is the point
of "preserving it" with an outer join??....
i.e., why not just use an inner join?

 

[Gary Walter]

:

if you want to filter on the "preserved table,"
in most cases one might ask what is the point
of "preserving it" with an outer join??....
i.e., why not just use an inner join?

<snip>
sorry....

actually, I do see the point here being that
you don't want one of the queries
where you might not get a match causing
"no results" if they were inner joined....

did you try saving the query w/o
"TOP 1" and w/o any WHERE clause,
then apply TOP 1 against this saved query
in one case, and then filtering this saved query
in the other case?

 

[Matt]

Thanks Gary,

I'm not sure what you mean about the cases. I have all of these queries and
outer joins because I'm trying to do an extremely complicated thing. I'm
trying to "flatten" records from the table so that the query will output the
latest entry for each field of a specific employee in one record. This means
that if a field is null for the latest record for an employee, I want the
query to keep going back record after record for that employee until that
field is not null, then use that entry. I'm emailing you a color-coded
graphic of what I want to happen.

Thanks again,
Matt

 

[Gary Walter]

Hi Matt,

Sorry, I use postini to automatically block spam
and it may have been too aggressive in the
case of your email (which I did not receive)?

"the query will output the latest entry for each field
of a specific employee in one record"

Where does "each field" come from?

What field (in table where "each field" comes from)
determines "latest?" Is it a date field, or are you using
an autonumber field where largest value insures "latest?"

EmployeeTable.EmployeeID AS EmpID,
UpdaterQuery.EntryDate,
UpdaterQuery.Updater,
DevActionQuery.DevAction,
TimeFrameQuery.TimeFrame,
PossibleQuery.Possible,
AdditionalQuery.Additional,
FRFLeaderQuery.FRFLeader,
FRFCompQuery.FRFComp,
FRFJobQuery.FRFJob,
FRFPressureQuery.FRFPressure,
FRFOrgQuery.FRFOrg,
FRFOtherQuery.FRFOther,
ReadinessQuery.Readiness,
WillingQuery.Willing,
EmployeeTable.*

If you had a table "FRF" with fields FRFID, EmployeeID,
Leader, Comp, Job, Pressure, Org, Other...

I might start a new query and add "EmployeeTable."

Right-mouse click on the table,
choose "Properties"
and change "Alias" to "E"

Double-click on top of table
to select all fields, then drag-and-drop
selection down to a field row in grid
(rather than use "*").

In next available column's field row,
I might create the first correlated subquery
(typed out as one line).

FRFLeader: (SELECT TOP 1 F.Leader
FROM FRF AS F
WHERE
F.EmployeeID = E.EmployeeID
AND
F.Leader IS NOT NULL
ORDER BY F.FRFID DESC)

if a date field (say "FRFDate") determined "latest"

FRFLeader: (SELECT TOP 1 F.Leader
FROM FRF AS F
WHERE
F.EmployeeID = E.EmployeeID
AND
F.Leader IS NOT NULL
ORDER BY F.FRFDate DESC)

then continue creating these correlated subqueries
for remaining fields...

FRFComp: (SELECT TOP 1 F.Comp
FROM FRF AS F
WHERE
F.EmployeeID = E.EmployeeID
AND
F.Comp IS NOT NULL
ORDER BY F.FRFID DESC)

If you tried to email me using the fake address
I reveal on newsgroups, here be actual
(if above is not clear or I misunderstood)

g
a
r
y
l
w
(at)
w
a
m
e
g
o
(dot)
n
e
t

 

[Guest]

I dunno, but I guess what you are seeing is that 'Top 1' is
not compatible with Server Filter. Rather than using Top 1,
the usual approach is to use a Group By query to get the
max date/index of the non-null records. Then join that back
to the table to get the full record, then join that to the employee
table to get the employee.

(david)