Crosstab Riddle

[knowshowrosegrows]

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The crosstab
lets me choose a date range and then spits out the vacancy rate for each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day in
the range - I do not get that program listed with blanks in all the date
fields. I need to have the table that is created by the crosstab joined
somehow with the program table so all the programs show up regardless of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?

 

[Dale Fye]

Assuming you have a table that contains all of your "program names", add that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
....

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join by
right clicking on the line linking the table and the query, and selecting the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[knowshowrosegrows]

Thanks so much for your quick response.

OK, I have simplified the sql so you may be able to see it better and I
think I put in the JOIN you suggest. I am still only getting records for
program Codes that reported during the time frame I submit rather than all
the program codes whether they reported or not.

Is this even possible for me to do with a crosstab?

See below:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

--
Thanks

You all are teaching me so much

Assuming you have a table that contains all of your "program names", add that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
...

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join by
right clicking on the line linking the table and the query, and selecting the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The crosstab
lets me choose a date range and then spits out the vacancy rate for each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day in
the range - I do not get that program listed with blanks in all the date
fields. I need to have the table that is created by the crosstab joined
somehow with the program table so all the programs show up regardless of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?

 

[Michel Walsh]

You have to test for null:


....
WHERE
(
[qryVacancyRateTrendFirst].[CensusDate]
BETWEEN DateAdd("yyyy",-1,Date()) AND Date()
)
OR
(
[qryVacancyRateTrendFirst].[CensusDate] IS NULL
)



since [qryVacancyRateTrendFirst] is the unpreserved side of the LEFT JOIN.
Sure, if CensusDate CAN be initially NULL, use a field from
[qryVacancyRateTrendFirst] which is known to not be null:


WHERE
(
[qryVacancyRateTrendFirst].[CensusDate]
BETWEEN DateAdd("yyyy",-1,Date()) AND Date()
)
OR
(
[qryVacancyRateTrendFirst].[KnownFieldToNotBeNull] IS NULL
)



Vanderghast, Access MVP

Thanks so much for your quick response.

OK, I have simplified the sql so you may be able to see it better and I
think I put in the JOIN you suggest. I am still only getting records for
program Codes that reported during the time frame I submit rather than all
the program codes whether they reported or not.

Is this even possible for me to do with a crosstab?

See below:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

--
Thanks

You all are teaching me so much

Assuming you have a table that contains all of your "program names", add
that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
...

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join
by
right clicking on the line linking the table and the query, and selecting
the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The
crosstab
lets me choose a date range and then spits out the vacancy rate for
each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day
in
the range - I do not get that program listed with blanks in all the
date
fields. I need to have the table that is created by the crosstab
joined
somehow with the program table so all the programs show up regardless
of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?

 

[Dale Fye]

Change:

FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram

to:

FROM tblProgram LEFT JOIN qryVacancyRateTrendFirst

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thanks so much for your quick response.

OK, I have simplified the sql so you may be able to see it better and I
think I put in the JOIN you suggest. I am still only getting records for
program Codes that reported during the time frame I submit rather than all
the program codes whether they reported or not.

Is this even possible for me to do with a crosstab?

See below:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

--
Thanks

You all are teaching me so much

Assuming you have a table that contains all of your "program names", add that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
...

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join by
right clicking on the line linking the table and the query, and selecting the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The crosstab
lets me choose a date range and then spits out the vacancy rate for each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day in
the range - I do not get that program listed with blanks in all the date
fields. I need to have the table that is created by the crosstab joined
somehow with the program table so all the programs show up regardless of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?

 

[Dale Fye]

Actually, that won't work either, since you have the where clause in your
crosstab query. With the WHERE clause, the [Cencus Date] field in the query
will be NULL for those records that you don't have a Prm_Code for in the
crosstab, so they will fail the query. You could modify the WHERE clause to:

WHERE ([qryVacancyRateTrendFirst].[CensusDate]
Between DateAdd("yyyy",-1,Date()) And Date())
OR ([qryVacancyRateTrendFirst].[CensusDate] IS NULL)

If that doesn't work, go back to your original query(before you added
tblProgram). Assuming that was called query1, create another query that uses
tblProgram as one of the data sources and Query1 as the second source. Join
them on the Prm_Code field, and select the option in the Join Properties that
says select all from tblPrograms and only the matching records from Query1.
The query should look something like:

SELECT P.Prm_Code, Q.*
FROM tblProgram as P
LEFT JOIN Query1 as Q
ON P.Prm_Code = Q.Prm_Code

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thanks so much for your quick response.

OK, I have simplified the sql so you may be able to see it better and I
think I put in the JOIN you suggest. I am still only getting records for
program Codes that reported during the time frame I submit rather than all
the program codes whether they reported or not.

Is this even possible for me to do with a crosstab?

See below:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

--
Thanks

You all are teaching me so much

Assuming you have a table that contains all of your "program names", add that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
...

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join by
right clicking on the line linking the table and the query, and selecting the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The crosstab
lets me choose a date range and then spits out the vacancy rate for each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day in
the range - I do not get that program listed with blanks in all the date
fields. I need to have the table that is created by the crosstab joined
somehow with the program table so all the programs show up regardless of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?

 

[knowshowrosegrows]

Thanks Dale and Michel - I got it!

What I ended up with was:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst RIGHT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE (((qryVacancyRateTrendFirst.CensusDate) Between
DateAdd("yyyy",-1,Date()) And Date())) OR (((tblProgram.Prm_Code) Is Not
Null))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

The OR (((tblProgram.Prm_Code) Is Not Null))
is what finally worked
--
Thanks

You all are teaching me so much

You have to test for null:


....
WHERE
(
[qryVacancyRateTrendFirst].[CensusDate]
BETWEEN DateAdd("yyyy",-1,Date()) AND Date()
)
OR
(
[qryVacancyRateTrendFirst].[CensusDate] IS NULL
)



since [qryVacancyRateTrendFirst] is the unpreserved side of the LEFT JOIN.
Sure, if CensusDate CAN be initially NULL, use a field from
[qryVacancyRateTrendFirst] which is known to not be null:


WHERE
(
[qryVacancyRateTrendFirst].[CensusDate]
BETWEEN DateAdd("yyyy",-1,Date()) AND Date()
)
OR
(
[qryVacancyRateTrendFirst].[KnownFieldToNotBeNull] IS NULL
)



Vanderghast, Access MVP

Thanks so much for your quick response.

OK, I have simplified the sql so you may be able to see it better and I
think I put in the JOIN you suggest. I am still only getting records for
program Codes that reported during the time frame I submit rather than all
the program codes whether they reported or not.

Is this even possible for me to do with a crosstab?

See below:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS
[Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst LEFT JOIN tblProgram ON
qryVacancyRateTrendFirst.Prm_Code = tblProgram.Prm_Code
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY tblProgram.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

--
Thanks

You all are teaching me so much

Assuming you have a table that contains all of your "program names", add
that
table to your query. Join it to your query on the "program name" field",
couldn't tell immediately what that field would be, using a LEFT JOIN.
Something like:

Transform ...
SELECT ...
FROM tblPrograms
LEFT JOIN qryVacancyRateTrendFirst
...

Make sure the program name field that you use in the SELECT clause is the
one from tblPrograms, not from the query. You can setup this left join
by
right clicking on the line linking the table and the query, and selecting
the
Join Properties option.

Then, click the radio button that indicates "Include all records from
tblPrograms and only those ....

Then click OK.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

OK, I have a crosstab query that you all helped me with that I love.

I have 72 programs that are supposed to report data every day. The
crosstab
lets me choose a date range and then spits out the vacancy rate for
each
program for each day in the range.

Program 1/1/08 1/2/08 1/3/08
prmX 72% 81% 88%
prmXX 77% 91%

The problem. If one of my programs did not report any data on any day
in
the range - I do not get that program listed with blanks in all the
date
fields. I need to have the table that is created by the crosstab
joined
somehow with the program table so all the programs show up regardless
of
whether they reported on each day of the range.

The sql I have for the crosstab is:

TRANSFORM Avg(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code,
Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of Vacancy Rate]
FROM qryVacancyRateTrendFirst
WHERE ((([qryVacancyRateTrendFirst.CensusDate]) Between
DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY qryVacancyRateTrendFirst.OrderOfIntensity,
qryVacancyRateTrendFirst.Reg, qryVacancyRateTrendFirst.Agency,
qryVacancyRateTrendFirst.Location, qryVacancyRateTrendFirst.Cap,
qryVacancyRateTrendFirst.LOC_Alias, qryVacancyRateTrendFirst.Prm_Code
PIVOT Format([CensusDate],"mm-dd");

Is this clear enough for someone to help me?