Changing Fields in Report

[Guest]

I have a crosstab query which has Expr1: Format([WEEKENDING]) as its name. It
brings up 4 new columns with the different weekending dates as the title. I
have this query as the basis of a report (that runs painfully slow), but when
I change the weekending dates to a different month in the query, my report no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to match my
query?

 

[Duane Hookom]

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information

 

[Guest]

I found your relative date solution through google, but I don't think I
understand it. Part of my problem is that I need to get 4 weeks- which need
to be specified before running the query as to track new data and compare it
to the old.

The query has to do with delivery trucks and fines they incur. The first few
row headings show the center they belong to, car number, the base month
avg/wk that is calculated in a separate query, then the column headings come
in for the 4 weeks we want to compare it to (where each weeks fines are
summed), followed by the total of those, the avg/wk, and the % change to the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER, [NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10 Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top 10 Bases
by Center is a Union query that combines all the top 10 cars from several
other queries, and the Current Period Rundates table comes from a Make Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change the dates
as needed. But as I said, it doesn't change in my report.

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP

I have a crosstab query which has Expr1: Format([WEEKENDING]) as its name.
It
brings up 4 new columns with the different weekending dates as the title.
I
have this query as the basis of a report (that runs painfully slow), but
when
I change the weekending dates to a different month in the query, my report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to match my
query?

 

[Duane Hookom]

My solution should work assuming you have a form "frmDate" with a text box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER, [NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10 Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP

I found your relative date solution through google, but I don't think I
understand it. Part of my problem is that I need to get 4 weeks- which
need
to be specified before running the query as to track new data and compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The first
few
row headings show the center they belong to, car number, the base month
avg/wk that is calculated in a separate query, then the column headings
come
in for the 4 weeks we want to compare it to (where each weeks fines are
summed), followed by the total of those, the avg/wk, and the % change to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top 10
Bases
by Center is a Union query that combines all the top 10 cars from several
other queries, and the Current Period Rundates table comes from a Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change the
dates
as needed. But as I said, it doesn't change in my report.

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP

I have a crosstab query which has Expr1: Format([WEEKENDING]) as its
name.
It
brings up 4 new columns with the different weekending dates as the
title.
I
have this query as the basis of a report (that runs painfully slow),
but
when
I change the weekending dates to a different month in the query, my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to match
my
query?

 

[Guest]

Okay, I managed to get it to work.. but the column headings are Wk-5, Wk6..
etc. Is there a way to get those to show as the actual dates? or atleast get
those dates to appear in my form and change as i change the date parameters?

My solution should work assuming you have a form "frmDate" with a text box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER, [NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10 Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP

I found your relative date solution through google, but I don't think I
understand it. Part of my problem is that I need to get 4 weeks- which
need
to be specified before running the query as to track new data and compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The first
few
row headings show the center they belong to, car number, the base month
avg/wk that is calculated in a separate query, then the column headings
come
in for the 4 weeks we want to compare it to (where each weeks fines are
summed), followed by the total of those, the avg/wk, and the % change to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top 10
Bases
by Center is a Union query that combines all the top 10 cars from several
other queries, and the Current Period Rundates table comes from a Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change the
dates
as needed. But as I said, it doesn't change in my report.

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


I have a crosstab query which has Expr1: Format([WEEKENDING]) as its
name.
It
brings up 4 new columns with the different weekending dates as the
title.
I
have this query as the basis of a report (that runs painfully slow),
but
when
I change the weekending dates to a different month in the query, my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to match
my
query?

 

[Duane Hookom]

My solution should have been in the pages you found. Basically, you should
use text boxes for column labels/headings. Use control sources like:

=DateAdd("ww",0, Forms!frmDate!txtEndDate)
=DateAdd("ww",-1, Forms!frmDate!txtEndDate)
=DateAdd("ww",-2, Forms!frmDate!txtEndDate)
=DateAdd("ww",-3, Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP

Okay, I managed to get it to work.. but the column headings are Wk-5,
Wk6..
etc. Is there a way to get those to show as the actual dates? or atleast
get
those dates to appear in my form and change as i change the date
parameters?

My solution should work assuming you have a form "frmDate" with a text
box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP

I found your relative date solution through google, but I don't think I
understand it. Part of my problem is that I need to get 4 weeks- which
need
to be specified before running the query as to track new data and
compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The
first
few
row headings show the center they belong to, car number, the base month
avg/wk that is calculated in a separate query, then the column headings
come
in for the 4 weeks we want to compare it to (where each weeks fines
are
summed), followed by the total of those, the avg/wk, and the % change
to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top 10
Bases
by Center is a Union query that combines all the top 10 cars from
several
other queries, and the Current Period Rundates table comes from a Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change the
dates
as needed. But as I said, it doesn't change in my report.
:

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


I have a crosstab query which has Expr1: Format([WEEKENDING]) as its
name.
It
brings up 4 new columns with the different weekending dates as the
title.
I
have this query as the basis of a report (that runs painfully slow),
but
when
I change the weekending dates to a different month in the query, my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to
match
my
query?

 

[Guest]

I cannot for the life of me get that to return a date. I keep getting #Name?

My solution should have been in the pages you found. Basically, you should
use text boxes for column labels/headings. Use control sources like:

=DateAdd("ww",0, Forms!frmDate!txtEndDate)
=DateAdd("ww",-1, Forms!frmDate!txtEndDate)
=DateAdd("ww",-2, Forms!frmDate!txtEndDate)
=DateAdd("ww",-3, Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP

Okay, I managed to get it to work.. but the column headings are Wk-5,
Wk6..
etc. Is there a way to get those to show as the actual dates? or atleast
get
those dates to appear in my form and change as i change the date
parameters?

My solution should work assuming you have a form "frmDate" with a text
box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP


I found your relative date solution through google, but I don't think I
understand it. Part of my problem is that I need to get 4 weeks- which
need
to be specified before running the query as to track new data and
compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The
first
few
row headings show the center they belong to, car number, the base month
avg/wk that is calculated in a separate query, then the column headings
come
in for the 4 weeks we want to compare it to (where each weeks fines
are
summed), followed by the total of those, the avg/wk, and the % change
to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top 10
Bases
by Center is a Union query that combines all the top 10 cars from
several
other queries, and the Current Period Rundates table comes from a Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change the
dates
as needed. But as I said, it doesn't change in my report.
:

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


I have a crosstab query which has Expr1: Format([WEEKENDING]) as its
name.
It
brings up 4 new columns with the different weekending dates as the
title.
I
have this query as the basis of a report (that runs painfully slow),
but
when
I change the weekending dates to a different month in the query, my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to
match
my
query?

 

[Duane Hookom]

Do you have a form opened named "frmDate" with a control named "txtEndDate"
and a real date value in it?

--
Duane Hookom
MS Access MVP

I cannot for the life of me get that to return a date. I keep getting
#Name?

My solution should have been in the pages you found. Basically, you
should
use text boxes for column labels/headings. Use control sources like:

=DateAdd("ww",0, Forms!frmDate!txtEndDate)
=DateAdd("ww",-1, Forms!frmDate!txtEndDate)
=DateAdd("ww",-2, Forms!frmDate!txtEndDate)
=DateAdd("ww",-3, Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP

Okay, I managed to get it to work.. but the column headings are Wk-5,
Wk6..
etc. Is there a way to get those to show as the actual dates? or
atleast
get
those dates to appear in my form and change as i change the date
parameters?

:

My solution should work assuming you have a form "frmDate" with a text
box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP


I found your relative date solution through google, but I don't think
I
understand it. Part of my problem is that I need to get 4 weeks-
which
need
to be specified before running the query as to track new data and
compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The
first
few
row headings show the center they belong to, car number, the base
month
avg/wk that is calculated in a separate query, then the column
headings
come
in for the 4 weeks we want to compare it to (where each weeks fines
are
summed), followed by the total of those, the avg/wk, and the %
change
to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS
Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top
10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top
10
Bases
by Center is a Union query that combines all the top 10 cars from
several
other queries, and the Current Period Rundates table comes from a
Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change
the
dates
as needed. But as I said, it doesn't change in my report.
:

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


message
I have a crosstab query which has Expr1: Format([WEEKENDING]) as
its
name.
It
brings up 4 new columns with the different weekending dates as
the
title.
I
have this query as the basis of a report (that runs painfully
slow),
but
when
I change the weekending dates to a different month in the query,
my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to
match
my
query?

 

[Guest]

Well you certainly called my attention to the fact that if i look at my form
in form view it says text0: #Name?

I made a form like you had said with a textbox inwhich i typed txtEndDate.
Everything works as it should except the #Name? thing. Was I supposed to make
the Label txtEndDate and the textbox some sort of date?

Do you have a form opened named "frmDate" with a control named "txtEndDate"
and a real date value in it?

--
Duane Hookom
MS Access MVP

I cannot for the life of me get that to return a date. I keep getting
#Name?

My solution should have been in the pages you found. Basically, you
should
use text boxes for column labels/headings. Use control sources like:

=DateAdd("ww",0, Forms!frmDate!txtEndDate)
=DateAdd("ww",-1, Forms!frmDate!txtEndDate)
=DateAdd("ww",-2, Forms!frmDate!txtEndDate)
=DateAdd("ww",-3, Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


Okay, I managed to get it to work.. but the column headings are Wk-5,
Wk6..
etc. Is there a way to get those to show as the actual dates? or
atleast
get
those dates to appear in my form and change as i change the date
parameters?

:

My solution should work assuming you have a form "frmDate" with a text
box
"txtEndDate". If you want four weeks then only one date parameter is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top 10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP


I found your relative date solution through google, but I don't think
I
understand it. Part of my problem is that I need to get 4 weeks-
which
need
to be specified before running the query as to track new data and
compare
it
to the old.

The query has to do with delivery trucks and fines they incur. The
first
few
row headings show the center they belong to, car number, the base
month
avg/wk that is calculated in a separate query, then the column
headings
come
in for the 4 weeks we want to compare it to (where each weeks fines
are
summed), followed by the total of those, the avg/wk, and the %
change
to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS
Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [% Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top
10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query Top
10
Bases
by Center is a Union query that combines all the top 10 cars from
several
other queries, and the Current Period Rundates table comes from a
Make
Table
parameter query so I can set the week ending dates I want. (I give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change
the
dates
as needed. But as I said, it doesn't change in my report.
:

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


message
I have a crosstab query which has Expr1: Format([WEEKENDING]) as
its
name.
It
brings up 4 new columns with the different weekending dates as
the
title.
I
have this query as the basis of a report (that runs painfully
slow),
but
when
I change the weekending dates to a different month in the query,
my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change to
match
my
query?

 

[Duane Hookom]

My posting had stated :
My solution should work assuming you have a form "frmDate"
with a text box "txtEndDate"
Can you just tell me if this is the case? I have said nothing about a label
on the form. Also, I'm not sure how a form in form view shows "text0:
#Name".


--
Duane Hookom
MS Access MVP

Well you certainly called my attention to the fact that if i look at my
form
in form view it says text0: #Name?

I made a form like you had said with a textbox inwhich i typed txtEndDate.
Everything works as it should except the #Name? thing. Was I supposed to
make
the Label txtEndDate and the textbox some sort of date?

Do you have a form opened named "frmDate" with a control named
"txtEndDate"
and a real date value in it?

--
Duane Hookom
MS Access MVP

I cannot for the life of me get that to return a date. I keep getting
#Name?

:

My solution should have been in the pages you found. Basically, you
should
use text boxes for column labels/headings. Use control sources like:

=DateAdd("ww",0, Forms!frmDate!txtEndDate)
=DateAdd("ww",-1, Forms!frmDate!txtEndDate)
=DateAdd("ww",-2, Forms!frmDate!txtEndDate)
=DateAdd("ww",-3, Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


Okay, I managed to get it to work.. but the column headings are
Wk-5,
Wk6..
etc. Is there a way to get those to show as the actual dates? or
atleast
get
those dates to appear in my form and change as i change the date
parameters?

:

My solution should work assuming you have a form "frmDate" with a
text
box
"txtEndDate". If you want four weeks then only one date parameter
is
required.

PARAMETERS [Forms]!frmDate![txtEndDate] DateTime;
TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS
Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [%
Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN [Top
10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT "Wk" & DateDiff("ww",RUNDATE, Forms!frmDate!txtEndDate)
IN ("Wk0","Wk1","Wk2","Wk3");

--
Duane Hookom
MS Access MVP


I found your relative date solution through google, but I don't
think
I
understand it. Part of my problem is that I need to get 4 weeks-
which
need
to be specified before running the query as to track new data and
compare
it
to the old.

The query has to do with delivery trucks and fines they incur.
The
first
few
row headings show the center they belong to, car number, the base
month
avg/wk that is calculated in a separate query, then the column
headings
come
in for the 4 weeks we want to compare it to (where each weeks
fines
are
summed), followed by the total of those, the avg/wk, and the %
change
to
the
base

This is what my SQL looks like:

TRANSFORM Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
SELECT [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW
TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases by
Center].CARNO,
[Top 10 Bases by Center].Base, Sum([NEW TICKETS].[new fine]) AS
Total,
[Total]/4 AS [Avg Per Week], ([Avg Per Week]/[Base])-1 AS [%
Change]
FROM ([NEW TICKETS] INNER JOIN [Current Period Rundates] ON [NEW
TICKETS].RUNDATE=[Current Period Rundates].RUNDATE) INNER JOIN
[Top
10
Bases
by Center] ON [NEW TICKETS].CARNO=[Top 10 Bases by Center].CARNO
GROUP BY [NEW TICKETS].DIVISION, [NEW TICKETS].DM, [NEW
TICKETS].CENTER,
[NEW TICKETS].[CTR NAME], [NEW TICKETS].[CTR MGR], [Top 10 Bases
by
Center].CARNO, [Top 10 Bases by Center].Base
PIVOT Format([Current Period Rundates]!RUNDATE);

The table NEW TICKETS supplies the fine information, the Query
Top
10
Bases
by Center is a Union query that combines all the top 10 cars from
several
other queries, and the Current Period Rundates table comes from a
Make
Table
parameter query so I can set the week ending dates I want. (I
give a
beginning and ending and get 4 week ending dates)

I run the dates query and the crosstab in a macro so I can change
the
dates
as needed. But as I said, it doesn't change in my report.
:

Consider using relative dates/weeks. Search google groups on
relative dates group:*access.Reports* author:hookom
for some information


--
Duane Hookom
MS Access MVP


message
I have a crosstab query which has Expr1: Format([WEEKENDING])
as
its
name.
It
brings up 4 new columns with the different weekending dates as
the
title.
I
have this query as the basis of a report (that runs painfully
slow),
but
when
I change the weekending dates to a different month in the
query,
my
report
no
longer works because it doesn't change too.

Is there any way I can get my report to automatically change
to
match
my
query?

 

[Guest]

I had created a new form in design view. I clicked the textbox in the toolbar
then clicked a spot on in the details area in the form.

That gave me 2 boxes. 1 I assume was a label (where the Text0: is) and the
2nd was the actual textbox. Inside the textbox I typed "txtEndDate." And I
saved the form as "frmDate."

Everything worked as it was supposed to in regards to my query. However if I
veiw the form "frmDate" in Form View, I see "#Name?" in the textbox.

 

[Duane Hookom]

You stated:
Inside the textbox I typed "txtEndDate."
You just set the control source to txtEndDate rather than the name of the
text box. Open the properties dialog of the text box, clear the control
source property and place txtEndDate in the Name property.

--
Duane Hookom
MS Access MVP

I had created a new form in design view. I clicked the textbox in the
toolbar
then clicked a spot on in the details area in the form.

That gave me 2 boxes. 1 I assume was a label (where the Text0: is) and the
2nd was the actual textbox. Inside the textbox I typed "txtEndDate." And I
saved the form as "frmDate."

Everything worked as it was supposed to in regards to my query. However if
I
veiw the form "frmDate" in Form View, I see "#Name?" in the textbox.

 

[Guest]

Ok, I cleared the control source completely and put "txtEndDate" in the Name
property, and the "#Name?" doesnt show up anymore in Form view, but it still
comes up in my report.

You stated:
Inside the textbox I typed "txtEndDate."
You just set the control source to txtEndDate rather than the name of the
text box. Open the properties dialog of the text box, clear the control
source property and place txtEndDate in the Name property.

 

[Duane Hookom]

What's in your report that shows #Name? I need to understand the control
sources etc.

--
Duane Hookom
MS Access MVP

Ok, I cleared the control source completely and put "txtEndDate" in the
Name
property, and the "#Name?" doesnt show up anymore in Form view, but it
still
comes up in my report.

 

[Guest]

=DateAdd("ww",0,Forms!frmDate![txtEndDate])
=DateAdd("ww",-1,Forms!frmDate![txtEndDate])
=DateAdd("ww",-2,Forms!frmDate![txtEndDate])
=DateAdd("ww",-3,Forms!frmDate![txtEndDate])

Those are the control sources... they're named Wk0, Wk1, Wk2, and Wk3.
And when I run the report they come up as "#Name?"

 

[Duane Hookom]

Is the form "frmDate" open with a date value entered into a text box named
"txtEndDate"?

Please try to tell us what you have looked at on your own to trouble-shoot
this so we don't have to play a game of 20 questions. It would really help
to know what your properties and environment are.
--
Duane Hookom
MS Access MVP

=DateAdd("ww",0,Forms!frmDate![txtEndDate])
=DateAdd("ww",-1,Forms!frmDate![txtEndDate])
=DateAdd("ww",-2,Forms!frmDate![txtEndDate])
=DateAdd("ww",-3,Forms!frmDate![txtEndDate])

Those are the control sources... they're named Wk0, Wk1, Wk2, and Wk3.
And when I run the report they come up as "#Name?"

What's in your report that shows #Name? I need to understand the control
sources etc.

 

[Guest]

I did exactly what you had told me to. I created a form and named it frmDate.
I placed a textbox within that form and named it txtEndDate. If I put a date
in the box, I get #Name? in form view. If I put nothing in the box, I get
#Name? in form view.

If I just run my query with relative dates based on the frmDate form, I get
a parameter box that asks me for a txtEndDate. I give it one, I get 4 columns
of fines from each of the 4 weekending dates that coincide with the date I
put in. Which is exactly how I want that to run. When I run my report I get
the same parameter request, and after inputing what I want, I get the data I
want.

However, I cannot get the week ending dates to show up in the page header as
the column headings. I get the same #Name? as I would from my form in form
view.

Going back to my form. If I put =Date() in the textbox named txtEndDate, I
get today's date in form view, and when I run my report I get dates, but
they're from the middle of the week as today is the middle of the week, and
my data gets messed up. If I keep my form open when I run my report or query,
I don't get any returned values.

The "column headings" in my report are textboxes that have the =DateAdd
expression you suggested. They don't work because my form returns "#Name?"

I dont know what information you think I'm not providing you with. I'm only
trying to do exactly what you had suggested.

Is the form "frmDate" open with a date value entered into a text box named
"txtEndDate"?

Please try to tell us what you have looked at on your own to trouble-shoot
this so we don't have to play a game of 20 questions. It would really help
to know what your properties and environment are.
--
Duane Hookom
MS Access MVP

=DateAdd("ww",0,Forms!frmDate![txtEndDate])
=DateAdd("ww",-1,Forms!frmDate![txtEndDate])
=DateAdd("ww",-2,Forms!frmDate![txtEndDate])
=DateAdd("ww",-3,Forms!frmDate![txtEndDate])

Those are the control sources... they're named Wk0, Wk1, Wk2, and Wk3.
And when I run the report they come up as "#Name?"

What's in your report that shows #Name? I need to understand the control
sources etc.

--
Duane Hookom
MS Access MVP

Ok, I cleared the control source completely and put "txtEndDate" in the
Name
property, and the "#Name?" doesnt show up anymore in Form view, but it
still
comes up in my report.

:

You stated:
Inside the textbox I typed "txtEndDate."
You just set the control source to txtEndDate rather than the name of
the
text box. Open the properties dialog of the text box, clear the
control
source property and place txtEndDate in the Name property.

--
Duane Hookom
MS Access MVP



I had created a new form in design view. I clicked the textbox in the
toolbar
then clicked a spot on in the details area in the form.

That gave me 2 boxes. 1 I assume was a label (where the Text0: is)
and
the
2nd was the actual textbox. Inside the textbox I typed "txtEndDate."
And I
saved the form as "frmDate."

Everything worked as it was supposed to in regards to my query.
However
if
I
veiw the form "frmDate" in Form View, I see "#Name?" in the textbox.


:

My posting had stated :
My solution should work assuming you have a form "frmDate"
with a text box "txtEndDate"
Can you just tell me if this is the case? I have said nothing about
a
label
on the form. Also, I'm not sure how a form in form view shows
"text0:
#Name".

 

[Duane Hookom]

Back up to "I get #Name? in form view". What is the Control Source of the
text box txtEndDate on the form? The only way that it would show #Name? is
if you had something typed into the Control Source property. I have asked
you to clear that control source so there is nothing in the property.


--
Duane Hookom
MS Access MVP

I did exactly what you had told me to. I created a form and named it
frmDate.
I placed a textbox within that form and named it txtEndDate. If I put a
date
in the box, I get #Name? in form view. If I put nothing in the box, I get
#Name? in form view.

If I just run my query with relative dates based on the frmDate form, I
get
a parameter box that asks me for a txtEndDate. I give it one, I get 4
columns
of fines from each of the 4 weekending dates that coincide with the date I
put in. Which is exactly how I want that to run. When I run my report I
get
the same parameter request, and after inputing what I want, I get the data
I
want.

However, I cannot get the week ending dates to show up in the page header
as
the column headings. I get the same #Name? as I would from my form in form
view.

Going back to my form. If I put =Date() in the textbox named txtEndDate, I
get today's date in form view, and when I run my report I get dates, but
they're from the middle of the week as today is the middle of the week,
and
my data gets messed up. If I keep my form open when I run my report or
query,
I don't get any returned values.

The "column headings" in my report are textboxes that have the =DateAdd
expression you suggested. They don't work because my form returns "#Name?"

I dont know what information you think I'm not providing you with. I'm
only
trying to do exactly what you had suggested.

Is the form "frmDate" open with a date value entered into a text box
named
"txtEndDate"?

Please try to tell us what you have looked at on your own to
trouble-shoot
this so we don't have to play a game of 20 questions. It would really
help
to know what your properties and environment are.
--
Duane Hookom
MS Access MVP

=DateAdd("ww",0,Forms!frmDate![txtEndDate])
=DateAdd("ww",-1,Forms!frmDate![txtEndDate])
=DateAdd("ww",-2,Forms!frmDate![txtEndDate])
=DateAdd("ww",-3,Forms!frmDate![txtEndDate])

Those are the control sources... they're named Wk0, Wk1, Wk2, and Wk3.
And when I run the report they come up as "#Name?"

:

What's in your report that shows #Name? I need to understand the
control
sources etc.

--
Duane Hookom
MS Access MVP

Ok, I cleared the control source completely and put "txtEndDate" in
the
Name
property, and the "#Name?" doesnt show up anymore in Form view, but
it
still
comes up in my report.

:

You stated:
Inside the textbox I typed "txtEndDate."
You just set the control source to txtEndDate rather than the name
of
the
text box. Open the properties dialog of the text box, clear the
control
source property and place txtEndDate in the Name property.

--
Duane Hookom
MS Access MVP



I had created a new form in design view. I clicked the textbox in
the
toolbar
then clicked a spot on in the details area in the form.

That gave me 2 boxes. 1 I assume was a label (where the Text0:
is)
and
the
2nd was the actual textbox. Inside the textbox I typed
"txtEndDate."
And I
saved the form as "frmDate."

Everything worked as it was supposed to in regards to my query.
However
if
I
veiw the form "frmDate" in Form View, I see "#Name?" in the
textbox.


:

My posting had stated :
My solution should work assuming you have a form "frmDate"
with a text box "txtEndDate"
Can you just tell me if this is the case? I have said nothing
about
a
label
on the form. Also, I'm not sure how a form in form view shows
"text0:
#Name".