Re: How to create a form based on a crosstab query? (I don't know how many columns there will be!)

Paul H wrote:
On 29 Apr, 14:20, Salad <o...@xxxxxxxxxxx> wrote:

Paul H wrote:

On 29 Apr, 11:07, Roger <lesperan...@xxxxxxxxxx> wrote:

On Apr 29, 2:33 am, Paul H <goo...@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I want to base a form on a crosstab query. The query shows statistics
for a user defined period. The column headings will look something
like this:

ClientID Month01 Month02 Month03 etc..

So if the user selects 3 months in the criteria form, there will be 3
(month) column headings, if he selects 6 months, the will be 6
headings.

At the moment my solution is to somehow (i haven't figured out the VBA
yet) construct a form dynamically. Is there a simpler solution to this
problem?

Thanks,

Paul

assuming that there will be a maximum of 12 months
edit the query, hilight the column heading field, I assume it is
called 'months' and show its properties (right click the column)
in the 'column headings' property add
"month01", "month02", ..., "month12"

this will cause the query to already have 12 columns regardless of
which, if any, have data- Hide quoted text -

- Show quoted text -

Roger,

Thank you...I should have explained in more detail:

The crosstab query actually shows the number of issues of a magazine
that have been delivered to customers, by issue date, between two user
selectable dates. So the crosstab query actually looks like this:

ClientID 01/01/2007 01/02/2007 01/03/2007 etc..
B&Q 55 110 110
Halfords 110 110 110

Notice that the column headings represent the issue date, also, some
months the magazine may not be published at all, so unfortunately your
solution will not work.

Paul

The following link is for a report. But I'm sure you can modify it for
a continuous form.http://www.fmsinc.com/tpapers/access/Reports/monthly/index.html

Flighthttp://www.youtube.com/watch?v=LtnDUSJfbzU- Hide quoted text -

- Show quoted text -


Salad, Thank you for your reply.

I noticed in the text it says:

"Microsoft Access reports reference field names directly. If we want
to use a crosstab query as the RecordSource of a report, its column
names should not change (unless we want to write a lot of code to
handle that)."

Ron's reponse was spot on. You REALLY need to study the article to understand it. It is an excellent article on displaying crosstab data.

Considering the fact that my users may select any number of months, am
I screwed? Have I got to write a load of code to dynamically create my
form?

:O(

Paul


If you want to give your users real flexibility I suppose you can open up a form in design mode and write a bunch of code to create the form's controls. But in crosstabs, afaic, that's nuts. Give your users an acceptable time frame; 12 months; 24 months, whatever. Then work within your format.

Hanging Out
http://www.youtube.com/watch?v=hiFOvrDAxxw


.

Relevant Pages

  • Re: Dynamic Reporting based on Parameterized Crosstab Query
    ... Since the column headings are all dates at a regular interval, ... charge date charged hours are associated with. ... on the first 3 rowwise fields of the crosstab query. ...
    (microsoft.public.access.reports)
  • Re: how to generate a report from Form output
    ... Headings property of the crosstab query. ... A crosstab query has a Column Headings property. ... make out a report with these elements, i tried to create a plain ...
    (microsoft.public.access.reports)
  • RE: Dynamic column in report using crosstab query
    ... H. Frank Situmorang ... "Duane Hookom" wrote: ... Crosstab query that John suggested is a bit extraordinary. ... You need to use a reference year to calculate your column headings. ...
    (microsoft.public.access.reports)
  • Re: Crosstab Help
    ... I would create this with a crosstab query. ... dates as column headings, use "relative" dates. ... "Jason Sweet" wrote in message ... >>Duane Hookom ...
    (microsoft.public.access.queries)
  • Re: Report casues crash
    ... Open the crosstab query in design view. ... List the possible values for the column headings, ... Thanks for the info ans steps to solve my report crashing problem. ... a crosstab query gets its column headings from one of the ...
    (microsoft.public.access.reports)