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

[Paul H]

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

[Roger]

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

On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

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

[Paul H]

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

On 29 Apr, 11:07, Roger <lesperan...@na tpro.comwrote:

On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:
>
>
>
>
>

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

[Salad]

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

Paul H wrote:

On 29 Apr, 11:07, Roger <lesperan...@na tpro.comwrote:
>

>>On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:
>>
>>
>>
>>
>>
>>

>>>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

Flight

http://www.youtube.com/watch?v=LtnDUSJfbzU

[Paul H]

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

On 29 Apr, 14:20, Salad <o...@vinegar.c omwrote:

Paul H wrote:

On 29 Apr, 11:07, Roger <lesperan...@na tpro.comwrote:

>

>On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

>

>>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...hly/index.html
>
Flighthttp://www.youtube.com/watch?v=LtnDUSJ fbzU- 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

[paii, Ron]

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


"Paul H" <google@comcraf t.freeserve.co. ukwrote in message
news:94ede0d0-61eb-459d-b875-50d7b05ff13b@a1 g2000hsb.google groups.com...
On 29 Apr, 14:20, Salad <o...@vinegar.c omwrote:

Paul H wrote:

On 29 Apr, 11:07, Roger <lesperan...@na tpro.comwrote:

>

>On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

>

>>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...hly/index.html

>
Flighthttp://www.youtube.com/watch?v=LtnDUSJ fbzU- 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

Will your user select any number of months? Or will there be some limit, 12,
24, 36 ect. Access has limits on report/form width and control count. Once
you define the max number of months you can use Salad's posting to help
create your query and report(s).

[Salad]

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

Paul H wrote:

On 29 Apr, 14:20, Salad <o...@vinegar.c omwrote:
>

>>Paul H wrote:
>>

>>>On 29 Apr, 11:07, Roger <lesperan...@na tpro.comwrote:

>>

>>>>On Apr 29, 2:33 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

>>

>>>>>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...hly/index.html
>>
>>Flighthttp://www.youtube.com/watch?v=LtnDUSJ fbzU- 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

[Bob Quintal]

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

Paul H <google@comcraf t.freeserve.co. ukwrote in
news:94ede0d0-61eb-459d-b875-
50d7b05ff13b@a1 g2000hsb.google groups.com

>
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

Sometimes you have to compromise. Instead of allowing them to choose
a start date and end date, have them choose a start date and a
number of following months, say 3, 6 or 12.

By limiting the number of choices to a reasonable set of options,
you have mead it easier for them to decide what report to generate,
and easier for you to give them those reports.

The next step is to code the periods in the query to a number, so
say you've decided they will get 12 month reports, you set the first
month to 0, the second to 1, and so on to 11.

So your query contains the following columns:
RptStartDate,
ClientId,
datediff("m",rp tStartDate,[Issue Date]) as monthOffset,
Count(delivery) as NumDeliveries.

In the report, you can set the column header labels to a Date using
dateadd("m",mon thoffset, rptStartDate)

The report will look and feel like the dates were the actual column
headers.

There is a way to have columns appear in a crosstab even if there is
no data so that you just get a blank column if the magazine is only
published bi-monthly.

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[Paul H]

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


Thanks to all who posted.

I have agreed with the client that he can view the results for a 6
month period only.

Paul

On 29 Apr, 09:33, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

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