Re: Report based on Crosstab with dates changing daily

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: SMac (SMac_at_discussions.microsoft.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 07:51:13 -0800

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" wrote:

> 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" <SMac@discussions.microsoft.com> wrote in message
> news:9504E35A-19CB-481D-9B12-A444481D4CBB@microsoft.com...
> >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
>
>
>

Relevant Pages

  • RE: Combo Box Problem
    ... I found the SQL view. ... "Duane Hookom" wrote: ... on the report to the records displayed in the report. ... The record source determines what is available to display in your report. ...
    (microsoft.public.access.reports)
  • Re: Haywire Crosstab w/ forms and Reports
    ... from the Group By and put all possible "lead Status' in the column heading ... "Duane Hookom" wrote: ... Yes the Startdate and Stopdate come form a form which launches a report ... based upon the crosstab query. ...
    (microsoft.public.access.queries)
  • Re: cross-table or something alike
    ... I don't understand how you want your report to display. ... Totalsshould sum all values per 2 months ... Duane Hookom schreef: ... CharacterProblem as Column Heading ...
    (microsoft.public.access.reports)
  • Re: MSGraph RowSource?
    ... > What would I use to modify the SQL statement of the saved RowSource ... > Could I do that from the OnOpen of the report? ... >> Duane Hookom ...
    (microsoft.public.access.reports)
  • RE: Batching Totals in Groups of 25 - How?
    ... "Query Too Complex". ... report, each batch of 25 records is correctly sorted A-Z by the LRef Code. ... "Duane Hookom" wrote: ... Save your SQL statement as a query then create another query: ...
    (microsoft.public.access.reports)