473,852 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is a dynamic crosstab form possible?

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?
Oct 4 '06 #1
13 17173
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" <oi*@vinegar.co mwrote in message
news:_E******** *********@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 =----
Oct 4 '06 #2
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" <oi*@vinegar.co mwrote in message
news:_E******** *********@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?

Oct 4 '06 #3
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" <oi*@vinegar.co mwrote in message
news:_E******** *********@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 =----
Oct 4 '06 #4
On Wed, 04 Oct 2006 19:08:42 GMT, salad <oi*@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.
Oct 4 '06 #5
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" <oi*@vinegar.co mwrote in message
news:_E******** *********@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?


Oct 4 '06 #6
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
www.heuveltop.nl/BasCB/msac_index.html
Oct 5 '06 #7
On Thu, 05 Oct 2006 00:05:02 GMT, salad <oi*@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>
Oct 5 '06 #8
salad <oi*@vinegar.co mwrote in
news:_E******** *********@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/
Oct 5 '06 #9

"salad" <oi*@vinegar.co mschreef in bericht news:O_******** ********@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
Oct 5 '06 #10

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

Similar topics

1
17691
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
1
3345
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
3
3601
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a 'dynamic crosstab query' but I don't know how to!! I've read the "How to..." on the Microsoft site but it mainly gives an example rather than explain the basics, which I can't work out. My context is:
1
4103
by: mtech1 | last post by:
Access 2002 I am trying to create a dynamic crosstab report that parameters come from 3 different forms. I get runtime error 3070 - The Microsoft Jet database engine does not recognize 'Forms!frmDefaults!ProviderID' as a valid field name or expression, and debug takes me to line 60 below. Any Suggestions Would Be Truly Appreciated!
2
2948
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
1
5169
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own column across the top and Category down the left side. As data is entered, the number of unique dates increases. As a result the
0
2325
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query. upto that i have done my coding part but...
5
3961
by: Albert Wammes | last post by:
Is it possible to create form containing an editable crosstab querie? The only way I can think of is to create a temporary table where the results of the crosstab querie are stored, then presented in a form for edit purposes and when closing the form 'translating' the temporary table back into the original one. (simplified) Example: Table: - id, week, number 1 1 a 1 2 b
14
7871
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which has the following fields: SPID (supervisor ID), total:group by, as row heading Date, total:group by, as column heading Calls handled, total:sum, as value Date, total:where, criteria between and - this is taken from a form,
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11020
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10356
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5934
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.