crosstab - left outer join?

[Guest]

I have a crosstab query that I need to work more like a left outer join than
an inner join. Instead of this:
1/2005 2/2005 3/2005 etc
12345-1 33 22 11
54321-1 44 33 22

I need:
1/2005 2/2005 3/2005 etc
12345-1 33 22 11
54321-1 44 33 22
33333-2 0 0 0
22222-1 0 0 0
44444-6 0 0 0

The row heading is a project number. I need to see all the projects on this
report that meet the criteria (filter) specified, whether or not there are
values in the cross-reference table.

My data is structured in 3 tables.. tblProjects, tblResources, and
tblResourceUsage. The first is the projects, the second is the people, and
the third is a crossreference between the two with month, year and hours
columns as well.

At this point I've been reduced to doing a bunch of loops to put 0's in my
cross-reference table to get what I need because I am completely exhausted in
my efforts to produce the extra rows with query tricks.

Thanks in advance for help, code for my crosstab query is below.

Ken


TRANSFORM CInt(nz(Sum(tblResourceUsage.hours),0)) AS hours
SELECT tblResourceUsage.ferc & tblResourceUsage.point & "-" &
CStr(tblResourceUsage.projectKey) AS fp, tblResourceUsage.userid,
IIf(Len(tblResources.name)>0,tblResources.name,tblResourceUsage.userid) AS
name, tblResources.discipline, tblResources.resourceRollKey,
tblResources.orig_par, tblResources.resource_cd, tblProjects.Description,
tblProjects.PA, tblProjects.Team
FROM tblProjects RIGHT JOIN (tblResources RIGHT JOIN tblResourceUsage ON
tblResources.userid = tblResourceUsage.userid) ON (tblProjects.ProjectKey =
tblResourceUsage.projectKey) AND (tblProjects.Point = tblResourceUsage.point)
AND (tblProjects.Ferc = tblResourceUsage.ferc)
GROUP BY tblResourceUsage.ferc & tblResourceUsage.point & "-" &
CStr(tblResourceUsage.projectKey), tblResourceUsage.userid,
IIf(Len(tblResources.name)>0,tblResources.name,tblResourceUsage.userid),
tblResources.discipline, tblResources.resourceRollKey, tblResources.orig_par,
tblResources.resource_cd, tblProjects.Description, tblProjects.PA,
tblProjects.Team
PIVOT
IIf(tblResourceUsage.year=Year(Now()),"Present",IIf(tblResourceUsage.year=(Year(Now())+1),"Future",IIf(tblResourceUsage.year=(Year(Now())+2),"Futurer",IIf(tblResourceUsage.year<(Year(Now())),"Prev","Other"))))
& Format(tblResourceUsage.month,"00");

 

[Michel Walsh]

Hi,


Make a first query that produces all the possible "groups",

Make the crosstab,

Make a third query that would be an inner join between the first query and
your crosstab, based on the possible groups: As a real left join is used,
that will produces NULL, not ZERO, for missing data.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your response.
I considered that, but I need to apply a filter that will affect what the
possible groups are. As I understand, you can only apply a filter to the
topmost query in a "query of queries" situation.

Any other ideas or ways around this?

Ken

 

[Michel Walsh]

Hi,


A parameter can appear in any query.

A filter can only be applied to the top most query since it has to be
applied in *something* you selected, in the SELECT ... list, since a
filter occur after the SELECT is finish, all done, all other internal
information (logically) disregarded.

Since "group" will be SELECTed in the final list, a filter should be
applicable to their values, here.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks a bunch for all the info, I can definitely solve it with parameters
and the filter stuff here looks promising.

ken