交叉表数据在表单中 [英] Crosstab data In forms

问题描述

您好，我有一个交叉表生成一个日期范围（简称为Jim Doherty），我想在表单上显示该信息，标题会根据您的选择按日期更改，那么我该如何制作表单显示交叉表中的这些动态日期？


我每次都要重新创建表单吗？或者列表框的行来源？


有什么建议吗？


感谢大家

解决方案

你好，我有一个交叉表，它生成一个日期范围（curtosy ofJim Doherty），我想在表单上显示该信息，标题会根据您的选择而按日期更改，那么如何让表单从交叉表中显示这些动态日期？


我每次都必须重新创建表单吗？或者列表框的行来源？


有什么建议吗？


谢谢各位

嗨Dan，


我把它测试出来，那么从你以前的线程开始工作了吗？请记住，在某种意义上，由于交叉表中列标题的动态特性，它是有限的。要根据可变列名称设置常规连续表单，这些是日期，在这种情况下是不切实际的。


可能是仅显示数据的最简单方法表单是将子表单放在主表单上。在主窗体上，您可以放置​​两个未绑定的文本框（日期条件）来控制提供给子窗体的数据。子表单将基于''查询''作为其源对象。


我附加了一个zip文件，演示了我在说什么，如果你请注意，它将在主窗体打开的那一刻创建子窗体的查询。如果您愿意，此查询仅作为子表单的一种代理数据源，直到您单击屏幕上的按钮，该按钮将根据您提供的那些返回动态列日期。


我认为总体而言仍然如此，因为有限制。


问候


吉姆:)

附加文件

（24.0 KB，95 views）

Hello, i have a crosstab that generates a date range (curtosy of "Jim Doherty")and i would like to display that info on a form, the headers change by date depending upon your selection, so how can i make the form display these dynamic dates from the crosstab?

would i have to recreate the form each time? or the row source of the list box?

any suggestions?

Thank guys

解决方案

Hello, i have a crosstab that generates a date range (curtosy of "Jim Doherty")and i would like to display that info on a form, the headers change by date depending upon your selection, so how can i make the form display these dynamic dates from the crosstab?

would i have to recreate the form each time? or the row source of the list box?

any suggestions?

Thank guys

Hi Dan,

I take it tested it out got that working from your previous thread then?. Remember, in one sense it is limiting because of the dynamic nature of the column headers in the crosstab. To set a conventional continuous form based on the changeable column names these being the ''date'' is impractical in this scenario.

Probably the easiest and simplest way to merely display the data on a form would be to place a subform on a main form. On the main form you could place two unbound textboxes (date criteria) that control the data supplied to the subform. The subform would base itself on a ''query'' as its sourceobject.

I have attached a zip file that demonstrates what I am talking about if you notice it will create a query for the subform the minute the main form opens. This query merely acts as a kind of proxy datasource for the subform if you like, until you click the button on screen that returns dynamic column dates based on those you supply.

Its all very workaroundish I think overall and remains so because of the limitations.

Regards

Jim :)

Attached Files

xtabHolDates.zip (18.5 KB, 115 views)

Hello again thanks for that works a treat, i modified it a little bit so that there are buttons that display the dates by month

but i have another problem... im trying to get the crosstab to show the names (of the staff) as well but im not having any luck, access says that my query contains too many ambiguos outer joins or something.. any ideas?

i also want to sum the columns can that be done?

Thanks again

Hello again thanks for that works a treat, i modified it a little bit so that there are buttons that display the dates by month

but i have another problem... im trying to get the crosstab to show the names (of the staff) as well but im not having any luck, access says that my query contains too many ambiguos outer joins or something.. any ideas?

i also want to sum the columns can that be done?

Thanks again

Dan,

This going to be limited to what you can ultimately get with this. Its a query that you are seeing on screen via a subform and you can really only push the boat out so far with it.

However... I have revised the attached zip file module code and included additional functionality based around what you are asking. It has a sample staff table in there and also the holiday table. The SQL is revised to include the staff in the dataset so you might want to read that and all the amended code in the module to see how it fits into your environment.

I have basically revised the code to read the crosstab headers on the fly, build another SQL statement on the fly by manipulating sum aggregate headers based around the dynamism of the crosstab and then recreating an additional query which is then UNIONED with the crosstab to give the SUM totals. Beyond that..... there is not much more you can do with this workaround!

Regards

Jim :)

Attached Files

xtabHolsRevised.zip (24.0 KB, 95 views)

这篇关于交叉表数据在表单中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！