Report based on Crosstab with dates changing daily

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added daily and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
 
You can use relative dates as column headings in your crosstab. Assuming you
want a specific number of dates (28 for example) that end on a date entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)
 
The following is my SQL:
TRANSFORM Sum(qry_Sum_for_rpt_NPLC_Eye_Chart.SumOfQty_of_Defect) AS
SumOfSumOfQty_of_Defect
SELECT qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason
FROM qry_Sum_for_rpt_NPLC_Eye_Chart
GROUP BY qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason,
qry_Sum_for_rpt_NPLC_Eye_Chart.Mold_Number
ORDER BY qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date
PIVOT qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date;

I get where you are going but the only problem is the user doesn't enter a
date, instead off a form they choose criteria as far as Customer, Program,
Mold#, Plant and Time Frame - all are combo boxes. There is a box that has
today's date on the form also. I was wondering if we could use this somehow
instead of [EndDate] - somehow figure out the month based off the current day
or something?

Much appreciated!!
-S

Duane Hookom said:
You can use relative dates as column headings in your crosstab. Assuming you
want a specific number of dates (28 for example) that end on a date entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


SMac said:
I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added daily and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
Click to expand...
Click to expand...
 
You can use any date value in place of Forms!frmDate!txtEndDate. If you want
this to be the end of the current month, set the control source of the text
box to:
=DateSerial(Year(Date()),Month(Date()) + 1,0)

--
Duane Hookom
MS Access MVP


SMac said:
The following is my SQL:
TRANSFORM Sum(qry_Sum_for_rpt_NPLC_Eye_Chart.SumOfQty_of_Defect) AS
SumOfSumOfQty_of_Defect
SELECT qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason
FROM qry_Sum_for_rpt_NPLC_Eye_Chart
GROUP BY qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason,
qry_Sum_for_rpt_NPLC_Eye_Chart.Mold_Number
ORDER BY qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date
PIVOT qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date;

I get where you are going but the only problem is the user doesn't enter a
date, instead off a form they choose criteria as far as Customer, Program,
Mold#, Plant and Time Frame - all are combo boxes. There is a box that has
today's date on the form also. I was wondering if we could use this
somehow
instead of [EndDate] - somehow figure out the month based off the current
day
or something?

Much appreciated!!
-S

Duane Hookom said:
You can use relative dates as column headings in your crosstab. Assuming
you
want a specific number of dates (28 for example) that end on a date
entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control
sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


SMac said:
I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added daily
and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
I got it working! Thanks!
But, one thing, in the report if I run the query like a normal crosstab
(without all the stuff we did), I get:
Defect Reason 1/5/2005 1/6/2005 1/7/2005 1/10/2005 1/11/2005
Broken tab 2
Flash 1
Missing Part 1 1
Mixed parts 2
Shine 1
Sink 2

But with the query we created I get double Defect Reasons like "Missing
Part" on 2 lines and 1 line will mark the number for one day say the 5th and
then the second line will have the number marked for the 11th - why doesn't
it roll up like normal?

Thanks again!

Duane Hookom said:
You can use any date value in place of Forms!frmDate!txtEndDate. If you want
this to be the end of the current month, set the control source of the text
box to:
=DateSerial(Year(Date()),Month(Date()) + 1,0)

--
Duane Hookom
MS Access MVP


SMac said:
The following is my SQL:
TRANSFORM Sum(qry_Sum_for_rpt_NPLC_Eye_Chart.SumOfQty_of_Defect) AS
SumOfSumOfQty_of_Defect
SELECT qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason
FROM qry_Sum_for_rpt_NPLC_Eye_Chart
GROUP BY qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason,
qry_Sum_for_rpt_NPLC_Eye_Chart.Mold_Number
ORDER BY qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date
PIVOT qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date;

I get where you are going but the only problem is the user doesn't enter a
date, instead off a form they choose criteria as far as Customer, Program,
Mold#, Plant and Time Frame - all are combo boxes. There is a box that has
today's date on the form also. I was wondering if we could use this
somehow
instead of [EndDate] - somehow figure out the month based off the current
day
or something?

Much appreciated!!
-S

Duane Hookom said:
You can use relative dates as column headings in your crosstab. Assuming
you
want a specific number of dates (28 for example) that end on a date
entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control
sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added daily
and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
Can't tell without seeing your current SQL view. I expect you are grouping
by one to many fields.

--
Duane Hookom
MS Access MVP


SMac said:
I got it working! Thanks!
But, one thing, in the report if I run the query like a normal crosstab
(without all the stuff we did), I get:
Defect Reason 1/5/2005 1/6/2005 1/7/2005 1/10/2005 1/11/2005
Broken tab 2
Flash 1
Missing Part 1 1
Mixed parts 2
Shine 1
Sink 2

But with the query we created I get double Defect Reasons like "Missing
Part" on 2 lines and 1 line will mark the number for one day say the 5th
and
then the second line will have the number marked for the 11th - why
doesn't
it roll up like normal?

Thanks again!

Duane Hookom said:
You can use any date value in place of Forms!frmDate!txtEndDate. If you
want
this to be the end of the current month, set the control source of the
text
box to:
=DateSerial(Year(Date()),Month(Date()) + 1,0)

--
Duane Hookom
MS Access MVP


SMac said:
The following is my SQL:
TRANSFORM Sum(qry_Sum_for_rpt_NPLC_Eye_Chart.SumOfQty_of_Defect) AS
SumOfSumOfQty_of_Defect
SELECT qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason
FROM qry_Sum_for_rpt_NPLC_Eye_Chart
GROUP BY qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason,
qry_Sum_for_rpt_NPLC_Eye_Chart.Mold_Number
ORDER BY qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date
PIVOT qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date;

I get where you are going but the only problem is the user doesn't
enter a
date, instead off a form they choose criteria as far as Customer,
Program,
Mold#, Plant and Time Frame - all are combo boxes. There is a box that
has
today's date on the form also. I was wondering if we could use this
somehow
instead of [EndDate] - somehow figure out the month based off the
current
day
or something?

Much appreciated!!
-S

:

You can use relative dates as column headings in your crosstab.
Assuming
you
want a specific number of dates (28 for example) that end on a date
entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL
of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control
sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added
daily
and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
You were right, I was grouping by 3 things and I took out 1 I didn't really
need and now it works.

Thanks!!!!

Duane Hookom said:
Can't tell without seeing your current SQL view. I expect you are grouping
by one to many fields.

--
Duane Hookom
MS Access MVP


SMac said:
I got it working! Thanks!
But, one thing, in the report if I run the query like a normal crosstab
(without all the stuff we did), I get:
Defect Reason 1/5/2005 1/6/2005 1/7/2005 1/10/2005 1/11/2005
Broken tab 2
Flash 1
Missing Part 1 1
Mixed parts 2
Shine 1
Sink 2

But with the query we created I get double Defect Reasons like "Missing
Part" on 2 lines and 1 line will mark the number for one day say the 5th
and
then the second line will have the number marked for the 11th - why
doesn't
it roll up like normal?

Thanks again!

Duane Hookom said:
You can use any date value in place of Forms!frmDate!txtEndDate. If you
want
this to be the end of the current month, set the control source of the
text
box to:
=DateSerial(Year(Date()),Month(Date()) + 1,0)

--
Duane Hookom
MS Access MVP


The following is my SQL:
TRANSFORM Sum(qry_Sum_for_rpt_NPLC_Eye_Chart.SumOfQty_of_Defect) AS
SumOfSumOfQty_of_Defect
SELECT qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason
FROM qry_Sum_for_rpt_NPLC_Eye_Chart
GROUP BY qry_Sum_for_rpt_NPLC_Eye_Chart.Defect_Reason,
qry_Sum_for_rpt_NPLC_Eye_Chart.Mold_Number
ORDER BY qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date
PIVOT qry_Sum_for_rpt_NPLC_Eye_Chart.Occurrence_Date;

I get where you are going but the only problem is the user doesn't
enter a
date, instead off a form they choose criteria as far as Customer,
Program,
Mold#, Plant and Time Frame - all are combo boxes. There is a box that
has
today's date on the form also. I was wondering if we could use this
somehow
instead of [EndDate] - somehow figure out the month based off the
current
day
or something?

Much appreciated!!
-S

:

You can use relative dates as column headings in your crosstab.
Assuming
you
want a specific number of dates (28 for example) that end on a date
entered
in a text box (Forms!frmDate!txtEndDate). Since we don't know the SQL
of
your query or name of the date field, assume it is [DefectDate].

Set the column heading expression to:
ColHead:"D" & DateDiff("d",[DefectDate],Forms!frmDate!txtEndDate)
Then set the column headings property to:
D0,D1,D2,...D27
This will create 28 columns with D0 being the end date from the form
control. D27 will be 27 days earlier.

To create column labels in your report, use text boxes with control
sources
like:
=DateAdd("d", 0,Forms!frmDate!txtEndDate)
=DateAdd("d", -1,Forms!frmDate!txtEndDate)
=DateAdd("d", -2,Forms!frmDate!txtEndDate)
=...
=DateAdd("d", -27,Forms!frmDate!txtEndDate)

--
Duane Hookom
MS Access MVP


I have a query that gives the following data:

Defect Reason 1/3 1/4 1/5 1/6 1/7
Blush 2 5
Shine 1 1 1
Wax 4 4

Then I created the report, problem is data is going to be added
daily
and
then when the new month starts it will do February and so on.

How do I get the report to automatically see the new date and data?

THANKS!!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
Back
Top