473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Jun 27 '08 #1
8 5887
On Apr 29, 2:33*am, Paul H <goo...@comcraft.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
Jun 27 '08 #2
On 29 Apr, 11:07, Roger <lesperan...@natpro.comwrote:
On Apr 29, 2:33*am, Paul H <goo...@comcraft.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

Jun 27 '08 #3
Paul H wrote:
On 29 Apr, 11:07, Roger <lesperan...@natpro.comwrote:
>>On Apr 29, 2:33 am, Paul H <goo...@comcraft.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

Flight
http://www.youtube.com/watch?v=LtnDUSJfbzU
Jun 27 '08 #4
On 29 Apr, 14:20, Salad <o...@vinegar.comwrote:
Paul H wrote:
On 29 Apr, 11:07, Roger <lesperan...@natpro.comwrote:
>On Apr 29, 2:33 am, Paul H <goo...@comcraft.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=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
Jun 27 '08 #5

"Paul H" <go****@comcraft.freeserve.co.ukwrote in message
news:94**********************************@a1g2000h sb.googlegroups.com...
On 29 Apr, 14:20, Salad <o...@vinegar.comwrote:
Paul H wrote:
On 29 Apr, 11:07, Roger <lesperan...@natpro.comwrote:
>On Apr 29, 2:33 am, Paul H <goo...@comcraft.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=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

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).
Jun 27 '08 #6
Paul H wrote:
On 29 Apr, 14:20, Salad <o...@vinegar.comwrote:
>>Paul H wrote:
>>>On 29 Apr, 11:07, Roger <lesperan...@natpro.comwrote:
>>>>On Apr 29, 2:33 am, Paul H <goo...@comcraft.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=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
Jun 27 '08 #7
Paul H <go****@comcraft.freeserve.co.ukwrote in
news:94ede0d0-61eb-459d-b875-
50**********@a1g2000hsb.googlegroups.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",rptStartDate,[Issue Date]) as monthOffset,
Count(delivery) as NumDeliveries.

In the report, you can set the column header labels to a Date using
dateadd("m",monthoffset, 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 **
Jun 27 '08 #8

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...@comcraft.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
Jun 27 '08 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
by: Matthew Wells | last post by:
I have a crosstab query based on anothe query. The base query resultset has no null values in its "Quantity" column. However, when I create the new crosstab query from the base query, the records...
3
by: Mike | last post by:
Is it possible to create and display a form based on a string variable that will be set at run-time? In other words, instead of declaring a variable as a user-defined form class: Dim MyForm...
4
by: john | last post by:
I created a form via the wizard with 1 main table and 2 one-on-one tables. As i couldn't add a field to the form (a field that I added to the table after creating the form), I googled out that the...
2
by: Metalzed | last post by:
I have 4 tabels tbOWNERS // PLAYERS ID , NAME 1 Mike 2 Rambo 3 Sam tbCARS // CARS THAT EXIST AND WHO OWNS THEM
1
by: starke1120 | last post by:
Is there a way to open a form based on query type.. Example.. If a certain query result is 1 then open the form to this result.. If the query results are NULL or 0 results, then open open for...
2
by: Matsam | last post by:
Hello, I have an Access form having 3 combos. I need to run a crosstab query when clicking a button in the form. But I want to include the values of the combo boxes in the filter condition of the...
8
by: serhii7777 | last post by:
Hello, I am building a database that keeps track of some events. I need to plot the events on a map. In other words, a query returns a number - digit, the number of incidents of a particular kind. I...
4
by: dgunner71 | last post by:
All, I have a Parent form where the Record Source is a query based on three tables (tblcustomers, tblEquipment, and tblWorkOrders). The Parent form has a subform based on a 4th table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.