Crosstab w/ no Null Return

[Guest]

Group,

I am using the following query that I would like to only return the last 90
days. Right now it is returning all 12 months. but only values for the last
90 days....I need it to remove the null columns.

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for all the help

Scottie

 

[Guest]

This part is demaing that you display these columns --
In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
You can edit it to include only your three months.

 

[Guest]

Karl,

How do I make the column heading to always display the same header.....so I
can create a report....ie: Like Days30, Days60, Days90

This part is demaing that you display these columns --
In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
You can edit it to include only your three months.
--
KARL DEWEY
Build a little - Test a little

Group,

I am using the following query that I would like to only return the last 90
days. Right now it is returning all 12 months. but only values for the last
90 days....I need it to remove the null columns.

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for all the help

Scottie

 

[John Spencer]

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("M",Date()-90,DateOf) +1

Try the above. If I did the Pivot statement correctly in should return
Month1, Month2, Month3
for the column names.

I might point out that you might want to change your criteria statement
to something that gives you a more specific range
Where DateOf >= DateSerial(Year(Date()),Month(Date())-3,1)
AND DateOf < DateSerial(Year(Date()),Month(Date()),0)



Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl,

How do I make the column heading to always display the same header.....so I
can create a report....ie: Like Days30, Days60, Days90

This part is demaing that you display these columns --
In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
You can edit it to include only your three months.
--
KARL DEWEY
Build a little - Test a little

Group,

I am using the following query that I would like to only return the last 90
days. Right now it is returning all 12 months. but only values for the last
90 days....I need it to remove the null columns.

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for all the help

Scottie

 

[Guest]

John,

Once again a beautiful reply to my query query...Thank you! Your
recommendation regarding the criteria was right on, but I would like to
specify the beginning date of the 90day range on an opening form
([Forms]![Open_PIP Report]![DateStart]). I tried many times to incorporate
it into what you have written here to no avail. If you could answer that I
will be quiet about this query....thanks once again.......

Scottie

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("M",Date()-90,DateOf) +1

Try the above. If I did the Pivot statement correctly in should return
Month1, Month2, Month3
for the column names.

I might point out that you might want to change your criteria statement
to something that gives you a more specific range
Where DateOf >= DateSerial(Year(Date()),Month(Date())-3,1)
AND DateOf < DateSerial(Year(Date()),Month(Date()),0)



Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl,

How do I make the column heading to always display the same header.....so I
can create a report....ie: Like Days30, Days60, Days90

This part is demaing that you display these columns --
In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
You can edit it to include only your three months.
--
KARL DEWEY
Build a little - Test a little


:

Group,

I am using the following query that I would like to only return the last 90
days. Right now it is returning all 12 months. but only values for the last
90 days....I need it to remove the null columns.

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for all the help

Scottie

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Parameters [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE WeightTbl.[Dateof])>=[Forms]![Open_PIP Report]![DateStart]
AND DateOf <DateAdd("d",90,[Forms]![Open_PIP Report]![DateStart] )
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("M",Date()-90,DateOf) +1

IF you want the alternative of 3 whole months versus 90 days then

Where DateOf >= DateSerial(Year([Forms]![Open_PIP
Report]![DateStart]),Month([Forms]![Open_PIP Report]![DateStart]),1)
AND DateOf < DateSerial(Year([Forms]![Open_PIP
Report]![DateStart]),Month([Forms]![Open_PIP Report]![DateStart])+3,0)

Or if [Forms]![Open_PIP Report]![DateStart] will always be the first day of
the first month
WHERE DateOf >= [Forms]![Open_PIP Report]![DateStart]
AND DateOf < DateAdd("d",3,[Forms]![Open_PIP Report]![DateStart])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Once again a beautiful reply to my query query...Thank you! Your
recommendation regarding the criteria was right on, but I would like to
specify the beginning date of the 90day range on an opening form
([Forms]![Open_PIP Report]![DateStart]). I tried many times to
incorporate
it into what you have written here to no avail. If you could answer that
I
will be quiet about this query....thanks once again.......

Scottie

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("M",Date()-90,DateOf) +1

Try the above. If I did the Pivot statement correctly in should return
Month1, Month2, Month3
for the column names.

I might point out that you might want to change your criteria statement
to something that gives you a more specific range
Where DateOf >= DateSerial(Year(Date()),Month(Date())-3,1)
AND DateOf < DateSerial(Year(Date()),Month(Date()),0)



Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl,

How do I make the column heading to always display the same
header.....so I
can create a report....ie: Like Days30, Days60, Days90



:

This part is demaing that you display these columns --
In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
You can edit it to include only your three months.
--
KARL DEWEY
Build a little - Test a little


:

Group,

I am using the following query that I would like to only return the
last 90
days. Right now it is returning all 12 months. but only values for
the last
90 days....I need it to remove the null columns.

TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight
SELECT WeightTbl.RID
FROM WeightTbl
WHERE (((WeightTbl.[Dateof])>=Date()-90))
GROUP BY WeightTbl.RID
PIVOT Format([Dateof],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for all the help

Scottie