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

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)."

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
.

Relevant Pages

  • Re: Cross Tab query date format and sort order
    ... to display this in a report rather than have to run as a query, ... I have a crosstab query which has the date as column headings. ...
    (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)
  • RE: Creating a Ward Map
    ... Row Heading of Count ... Then set the Column Headings property to all possible Room values ... Running this report should display a single record with the current occupant ... could create a Crosstab query with the beds as column headings and patients ...
    (microsoft.public.access.reports)
  • Crosstab query report
    ... I have a report based on a crosstab query and the report is set to accept ... The first 3 column headings are the "row heading" in the ... Private Sub Detail1_Format ...
    (microsoft.public.access.forms)
  • Re: Crosstab Report
    ... Specify the column names in the query's Column Headings property. ... Save the report with unbound controls, ... Then in the Open event of the report, generate the crosstab query statement ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.reports)