Re: Changing data under the column heading in a crosstab query

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Robin (Robin_at_discussions.microsoft.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 05:18:01 -0800

Hello,

I changed my WHERE clause with the one you suggested and also changed the
PIVOT statement. When I run the query for 4 days, it leaves the fourth day
off. If I run the query for two days, It show data like this:

A0 A1 A2 A3 A4
                .40 .40

I don't think it is working right,. What did I do wrong??

I have attached my SQL statement below I hope it makes since to you. I
have to warn you., I am pretty new to this. I learn a lot my examples.

Thanks,

Robin

"Michel Walsh" wrote:

> Hi,
>
>
> It should work if your table hold data for just the appropriate week. You
> can add a WHERE clause to be sure of that:
>
>
> WHERE 0 = DateDiff("ww", [Day], [Forms]![frmReportSelect]![txtEndDate] )
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
>
> "Robin" <Robin@discussions.microsoft.com> wrote in message
> news:E4EF54AA-5591-45A5-9FA0-6A4C87689E9D@microsoft.com...
> > Hello;
> >
> > I tried the statement you suggested. I think this only applies if I am
> > only
> > running the report for 3 days. I should have made it more clearer . I do
> > not want to limit the report to 5 days. The report my be printed 1, 2, 3,
> > or
> > 4 dyas. But no more than 5 days. If running the report for 5 days , this
> > gives me the weekly report for all 5 days. Which is what I need as well.
> > I
> > also need to run the report for selective days within the same week. If
> > I
> > ran the report on the first day, that report should reflect Monday. It
> > the
> > report was printed on the second day, that would show data for Monday and
> > Tuesday and so on. I hope this make more sense.
> >
> > I have copied my pivot statement :
> >
> > PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
> > ("A0","A1","A2","A3","A4");
> >
> > Thanks,
> >
> > Robin
> >
> > "Michel Walsh" wrote:
> >
> >> Hi,
> >>
> >>
> >> Try to change your PIVOT to something like:
> >>
> >>
> >>
> >> PIVOT "D" & ( DatePart("w", MyDateTimeField) - 2 ) IN( "D"0", "D1",
> >> "D2",
> >> "D3", "D4")
> >>
> >>
> >>
> >>
> >> Hoping it may help,
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >> "Robin" <Robin@discussions.microsoft.com> wrote in message
> >> news:22566FFD-ECB8-48B3-8F64-D95AA20DE9F5@microsoft.com...
> >> > How do I print the correct information under the right column heading
> >> > if I
> >> > only need information for 3days. I have a crosstab query set up with a
> >> > date
> >> > control where I enter a start and end date. This report is set up to
> >> > run
> >> > weekly (5days). My column headings are fixed where my report prints
> >> > M,T,W,TH,F. If I need to run the report for 3days the information is
> >> > under
> >> > the wrong colunmn headings. The crosstab print the information like
> >> > this
> >> > for a five day report:
> >> >
> >> > D0 D1 D2 D3 D4
> >> > 12 13 16 17 10
> >> >
> >> > If I only want three days worth of information if gives it to me like
> >> > this:
> >> >
> >> > D0 D1 D2 D3 D4
> >> > 16 17 10
> >> >
> >> > Is there a way to conrol what column to print on a report?
> >> >
> >> > Thanks,
> >> >
> >> >
> >> > Robin
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Need help applying SQL statement to Access2000 Query
    ... >WHERE clause, but with an ORDER BY clause. ... >construct the filter string and then open the main form (or report). ... to build the query and open the Main Form or a report ...
    (microsoft.public.access.formscoding)
  • Re: Report Parameters - no results
    ... Is it indicated in the correct spot of my WHERE clause? ... be able to use it for this particular report based on user needs. ... What else can I do to try to correct my sql? ... used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)
  • Re: Report Parameters - no results
    ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... be able to use it for this particular report based on user needs. ... the WHERE clause in your query. ... used with function in query expression" for my where clause. ...
    (microsoft.public.access.reports)
  • RE: Passing Qry as part of Rpt DoCmd.OpenReport
    ... You are, by your description, trying to filter the output of the report. ... If you create a string that is a Where Clause without the word Where, ... And yes my data query is plain vanillia with no where clause only a single ...
    (microsoft.public.access.reports)
  • Re: Report Parameters - no results
    ... Whatever result you get when you press Enter, that's what needs to go into the WHERE clause in your query. ... Quite honestly, I find it much easier to build the WHERE clause from only those boxes that that have a value, and you get to ignore the ones like "". ... Although the article is about applying a Filter to a form, the code is identical to build the WhereCondition for the report. ... Here is my SQL ...
    (microsoft.public.access.reports)