K
ken
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");
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");