Is a dynamic crosstab form possible?

**Martin** · Oct 4 '06, 07:55 PM

Re: Is a dynamic crosstab form possible?

Total dynamic form creation is not really a good idea in Access. If your
willing to impose some limits to the form or crosstab query you can get a
pretty convincing result. In the end, it all depends on your exact needs.
Sometimes, your language choice(VB for Access) when you started can be your
worst mistake. IMHO, this language is a very high level language that
doesn't give pin point control like for example C++. But for making an
application quickly from scratch its much better than C++.

HTH
Martin
"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...

Operating in A97.
>
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
>
Using a Pivot table, one is actually launching Excel for data viewing.
I'd prefer the user stay in Access. Creating dynamic crosstab queries
is pretty simple. The problem is that the column count may shrink or
grow depending on the filter.
>
One can easily open up a crosstab query. Simply use the OpenQuery
command. But this limits the use of dbl-clicks and event procedures.
>
Can a form be created that can adjust to the column count changes/column
name changes based on a Crosstab?

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

**paii, Ron** · Oct 4 '06, 09:15 PM

Re: Is a dynamic crosstab form possible?

Check out the "IN" clause

You will be able to define which column will be present even if there is no
data for the column. With proper formatting of the source query to the cross
tab, you will not have the name and number of columns change.

"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...

Operating in A97.
>
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
>
Using a Pivot table, one is actually launching Excel for data viewing.
I'd prefer the user stay in Access. Creating dynamic crosstab queries
is pretty simple. The problem is that the column count may shrink or
grow depending on the filter.
>
One can easily open up a crosstab query. Simply use the OpenQuery
command. But this limits the use of dbl-clicks and event procedures.
>
Can a form be created that can adjust to the column count changes/column
name changes based on a Crosstab?

**salad** · Oct 4 '06, 11:25 PM

Re: Is a dynamic crosstab form possible?

Martin wrote:

Total dynamic form creation is not really a good idea in Access. If your
willing to impose some limits to the form or crosstab query you can get a
pretty convincing result. In the end, it all depends on your exact needs.

Well, a crosstab query gives me the results I want. So does a Pivot
table. The problem with Pivot tables is that the recordsource is
stashed away someplace, not suitable to updating or filtering on the
fly. The problem with a crosstab is that the column count may fluctuate.

Sometimes, your language choice(VB for Access) when you started can be your
worst mistake.

I disagree. It gives me partially what I want. I think Pivot tables
and crosstabs aren't fully functional. They are cute, but not really
practical...par tially implemented.

IMHO, this language is a very high level language that

doesn't give pin point control like for example C++. But for making an
application quickly from scratch its much better than C++.
>

If you have a large team of programmers and lots of money and time, then
C++ is worthwhile.

HTH
Martin
"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...
>

>>Operating in A97.
>>
>>I didn't receive much of a response conserning Pivot tables in Access.
>>Pivot tables are nice, but a CrossTab will work for me too.
>>
>>Using a Pivot table, one is actually launching Excel for data viewing.
>>I'd prefer the user stay in Access. Creating dynamic crosstab queries
>>is pretty simple. The problem is that the column count may shrink or
>>grow depending on the filter.
>>
>>One can easily open up a crosstab query. Simply use the OpenQuery
>>command. But this limits the use of dbl-clicks and event procedures.
>>
>>Can a form be created that can adjust to the column count changes/column
>>name changes based on a Crosstab?

>
>
>
>
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

**person** · Oct 4 '06, 11:45 PM

Re: Is a dynamic crosstab form possible?

On Wed, 04 Oct 2006 19:08:42 GMT, salad <oil@vinegar.co mwrote:

>Operating in A97.
>
>I didn't receive much of a response conserning Pivot tables in Access.
>Pivot tables are nice, but a CrossTab will work for me too.
>
>Using a Pivot table, one is actually launching Excel for data viewing.
>I'd prefer the user stay in Access. Creating dynamic crosstab queries
>is pretty simple. The problem is that the column count may shrink or
>grow depending on the filter.
>
>One can easily open up a crosstab query. Simply use the OpenQuery
>command. But this limits the use of dbl-clicks and event procedures.
>
>Can a form be created that can adjust to the column count changes/column
>name changes based on a Crosstab?

see http://support.microsoft.com/default...;en-us;Q328320

there was a version of this for A97 but if not still available I don't think much will have changed.

If you want an editable for it is more complicated, you have to use a temporary table.

**salad** · Oct 4 '06, 11:55 PM

Re: Is a dynamic crosstab form possible?

paii, Ron wrote:

Check out the "IN" clause
>

Not sure what the benefit of IN is in my quest.

You will be able to define which column will be present even if there is no
data for the column. With proper formatting of the source query to the cross
tab, you will not have the name and number of columns change.

Which totally defeats the purpose. Here's an example; you can have
28-31 data columns, besides the row columns, for a month. The column
counts change. Now one could open up the query without mods and see
exactly what's what. The problem is no event processes.

One could create a form. Here you need to specify the number of columns
and assign names to the contols. One could make a form with excess
controls and fake it out and hide columns as required. For something
dealing with days in a month, that would be a piece of cake with lots of
coding required.

Alternatively, I suppose I could fake it out and display "groups" of
data. It may be the only way to go. For example, the columns may be
employee names. So I could show employees A - M in one group, N - P in
another set. Basically a PITA regarding the excess code required.

It would be nice if one could simply drop a recordset into a form that
would display data as a datasheet and have it use the columns of the
query without specifiying all of the controls on the form in advance.

>
"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...
>

>>Operating in A97.
>>
>>I didn't receive much of a response conserning Pivot tables in Access.
>>Pivot tables are nice, but a CrossTab will work for me too.
>>
>>Using a Pivot table, one is actually launching Excel for data viewing.
>>I'd prefer the user stay in Access. Creating dynamic crosstab queries
>>is pretty simple. The problem is that the column count may shrink or
>>grow depending on the filter.
>>
>>One can easily open up a crosstab query. Simply use the OpenQuery
>>command. But this limits the use of dbl-clicks and event procedures.
>>
>>Can a form be created that can adjust to the column count changes/column
>>name changes based on a Crosstab?

>
>
>

**Bas Cost Budde** · Oct 5 '06, 12:35 AM

Re: Is a dynamic crosstab form possible?

Yes there are possibilities here. Check my site, Code modules ->
EditCrosstab, can that do anything for you?

salad schreef:

Can a form be created that can adjust to the column count changes/column
name changes based on a Crosstab?

--
Bas Cost Budde
Holland

https://www.heuveltop.nl/BasCB/msac_index.html

**Tom van Stiphout** · Oct 5 '06, 01:35 AM

Re: Is a dynamic crosstab form possible?

On Thu, 05 Oct 2006 00:05:02 GMT, salad <oil@vinegar.co mwrote:

I think he meant the Column Headings property of the query.
-Tom.

>paii, Ron wrote:
>

>Check out the "IN" clause
>>

>Not sure what the benefit of IN is in my quest.
>

<clip>

**David W. Fenton** · Oct 5 '06, 02:15 AM

Re: Is a dynamic crosstab form possible?

salad <oil@vinegar.co mwrote in
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net:

Can a form be created that can adjust to the column count
changes/column name changes based on a Crosstab?

Well, of course it can.

Create a form with the fields that are always in your crosstab
bound.

Then add a bunch of hidden fields for the dynamic field (i.e., the
crosstab fields that can vary).

When you open the form, the recordsource will populate the
Recordsetclone. All you have to do is count the fields in the
RecordsetClone' s Fields collection, then walk through them and
assign ControlSources and make visible the fields to display the
data.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

**Arno R** · Oct 5 '06, 07:15 AM

Re: Is a dynamic crosstab form possible?

"salad" <oil@vinegar.co mschreef in bericht news:O_XUg.6957 $o71.495@newsre ad3.news.pas.ea rthlink.net...

It would be nice if one could simply drop a recordset into a form that
would display data as a datasheet and have it use the columns of the
query without specifiying all of the controls on the form in advance.

Maybe you could use a "datasheet-form" ??
With Access 2000 and above one can use tables and queries just like that as the SourceObject in a Subform control .
It works great. Set columnwidth to -2 and all columns 'magically' will adjust themselves.

I created a 'Datasheet-form" where one can simply choose the table or query to show from a combo.
You can also control the font, fontsize, forecolor, backcolor and such.

Arno R

**Martin** · Oct 5 '06, 01:55 PM

Re: Is a dynamic crosstab form possible?

If pivot tables and crosstab queries give you what you want and the forms
part doesn't and it doesn't give you a way to remedy it. That is a limiting
factor of the platform\langua ge. Try writing a pivot table or crosstab query
not in a relational database it would take quite a while. However it really
depends on how big a range of columns you need. I did the visible and moved
fields around trick once or twice. The code might be a little lengthy but it
will work well. If that still didn't give you\me enough columns I copied it
added more fields and did the visible trick again. So for instance I needed
between 15 and 30 columns my first form had 15 to 22. Wrote all the code.
Then copied it and used it for a form for columns 23 to 30. This is not
dynamic but it really doesn't matter because the user has no idea nor does
he care. As long as your end user is satisfied and you spent the least
time/money possible implementing your solution I consider that a Success!

"salad" <oil@vinegar.co mwrote in message
news:wGXUg.1007 6$UG4.3743@news read2.news.pas. earthlink.net.. .

Martin wrote:

Total dynamic form creation is not really a good idea in Access. If your
willing to impose some limits to the form or crosstab query you can get

a

pretty convincing result. In the end, it all depends on your exact

needs.

>
Well, a crosstab query gives me the results I want. So does a Pivot
table. The problem with Pivot tables is that the recordsource is
stashed away someplace, not suitable to updating or filtering on the
fly. The problem with a crosstab is that the column count may fluctuate.
>

Sometimes, your language choice(VB for Access) when you started can be

your

worst mistake.

>
I disagree. It gives me partially what I want. I think Pivot tables
and crosstabs aren't fully functional. They are cute, but not really
practical...par tially implemented.
>
IMHO, this language is a very high level language that

doesn't give pin point control like for example C++. But for making an
application quickly from scratch its much better than C++.

If you have a large team of programmers and lots of money and time, then
C++ is worthwhile.
>

HTH
Martin
"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...

>Operating in A97.
>
>I didn't receive much of a response conserning Pivot tables in Access.
>Pivot tables are nice, but a CrossTab will work for me too.
>
>Using a Pivot table, one is actually launching Excel for data viewing.
>I'd prefer the user stay in Access. Creating dynamic crosstab queries
>is pretty simple. The problem is that the column count may shrink or
>grow depending on the filter.
>
>One can easily open up a crosstab query. Simply use the OpenQuery
>command. But this limits the use of dbl-clicks and event procedures.
>
>Can a form be created that can adjust to the column count changes/column
>name changes based on a Crosstab?

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet

News==----

http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+

Newsgroups

----= East and West-Coast Server Farms - Total Privacy via Encryption

=----

>

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

**paii, Ron** · Oct 5 '06, 04:05 PM

Re: Is a dynamic crosstab form possible?

"salad" <oil@vinegar.co mwrote in message
news:O_XUg.6957 $o71.495@newsre ad3.news.pas.ea rthlink.net...

paii, Ron wrote:
>

Check out the "IN" clause

Not sure what the benefit of IN is in my quest.
>

You will be able to define which column will be present even if there is

no

data for the column. With proper formatting of the source query to the

cross

tab, you will not have the name and number of columns change.

>
Which totally defeats the purpose. Here's an example; you can have
28-31 data columns, besides the row columns, for a month. The column
counts change. Now one could open up the query without mods and see
exactly what's what. The problem is no event processes.
>

IN will allow your cross tab to return field names Day1 though Day31 so your
report will always have controls matched to field names. The open event of
the report would modify labels to correctly identify the column or hide it
if no data. I did this in a report that supplied a rolling 3 week loading by
work center from data in the work order system. The source query calculates
a column to match one of the "IN" clause value for the day of week the load
is scheduled in the work center.

One could create a form. Here you need to specify the number of columns
and assign names to the contols. One could make a form with excess
controls and fake it out and hide columns as required. For something
dealing with days in a month, that would be a piece of cake with lots of
coding required.

If used in a form, you most likly would need a temp table. Code in the form
would update the underlying tables.

>
Alternatively, I suppose I could fake it out and display "groups" of
data. It may be the only way to go. For example, the columns may be
employee names. So I could show employees A - M in one group, N - P in
another set. Basically a PITA regarding the excess code required.
>
It would be nice if one could simply drop a recordset into a form that
would display data as a datasheet and have it use the columns of the
query without specifiying all of the controls on the form in advance.
>

"salad" <oil@vinegar.co mwrote in message
news:_ETUg.4939 $Y24.3239@newsr ead4.news.pas.e arthlink.net...

>Operating in A97.
>
>I didn't receive much of a response conserning Pivot tables in Access.
>Pivot tables are nice, but a CrossTab will work for me too.
>
>Using a Pivot table, one is actually launching Excel for data viewing.
>I'd prefer the user stay in Access. Creating dynamic crosstab queries
>is pretty simple. The problem is that the column count may shrink or
>grow depending on the filter.
>
>One can easily open up a crosstab query. Simply use the OpenQuery
>command. But this limits the use of dbl-clicks and event procedures.
>
>Can a form be created that can adjust to the column count changes/column
>name changes based on a Crosstab?

**David W. Fenton** · Oct 5 '06, 04:15 PM

Re: Is a dynamic crosstab form possible?

"Martin" <martinj@scheme software.comwro te in
news:1160057575 _7435@sp6iad.su perfeed.net:

If pivot tables and crosstab queries give you what you want and
the forms part doesn't and it doesn't give you a way to remedy it.
That is a limiting factor of the platform\langua ge.

That limitation does not exist in Access -- what he needs is
completely possible.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

**salad** · Oct 7 '06, 04:45 AM

Re: Is a dynamic crosstab form possible?

salad wrote:

Operating in A97.
>
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
>
Using a Pivot table, one is actually launching Excel for data viewing.
I'd prefer the user stay in Access. Creating dynamic crosstab queries
is pretty simple. The problem is that the column count may shrink or
grow depending on the filter.
>
One can easily open up a crosstab query. Simply use the OpenQuery
command. But this limits the use of dbl-clicks and event procedures.
>
Can a form be created that can adjust to the column count changes/column
name changes based on a Crosstab?

Thanks all for the tips.

Is a dynamic crosstab form possible?

Is a dynamic crosstab form possible?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment