与Excel相比，Access是否具有有限的交叉表？ [英] Does Access have Limited Crosstab compared with Excel?

问题描述

伙计，任何人都可以提供建议......


Access和Excel中的交叉表功能是否存在显着差异？


使用Office XP 2002 ...生成交叉表报表我是在Excel中从Access中的查询数据源开发的。关于这一点的一个好处是能够为交叉表值选择日期标题然后选择特定时间标题。尽可能地尝试，我无法在Access中直接使用交叉表或使用交叉表作为报告数据来复制此​​方法。


我希望我能看到一种方法向您展示Excel输出...很快就会移动到Office 2007，与2003年相比这似乎非常缓慢，这也是我不想仅仅为一个报告打开Excel的部分原因。

我想要建模的数据是： -


为1：M组表的一侧选择一组记录。 M表中的所有记录都不是唯一的。 M侧包含的数据包括表示行的文本字段（成员），日期字段，单独的时间字段和不超过99的整数值。


开解决方案我想展示这样的东西


标题:(来自1：M查询中的1个表格）


Date1 | |日期2等

Time1 |时间2 |时间3 |时间4 ||时间1 |时间2 |时间3 |等等


Fred | 1 | 4 | || 3 | 6 | 4 |

比尔| 3 | | 1 || 1 | 4 | |


我不能使用datepart，因为日期和时间字段是分隔字段。请注意，某些结果可能会有一些空值。


我希望我提供了详细信息。如果有帮助，我可以提供一组源数据。


TIA对此有任何帮助。


头盔

Folks, could anyone advise ...

Is there a significant difference in crosstab capabilities in Access and Excel?

Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from a query datasource in Access. One good thing about this was the ability to select both a date header and then a specific time header for the crosstab value. Try as I might, I cannot see a way to replicate this in Access either directly using a crosstab or using the crosstab as data for a report.

I wish I could see a way to show you the Excel output ... moving soon to Office 2007 which seems very slow in comparison with 2003 and that''s part of the reason that I don''t want ot open Excel for just one report.

The data I am seeking to model is:-

Select a set of records for that match one side of a 1:M set of tables. None of the records in the M table is unique. The M side holds data that includes a text field (member) which represents the row, a date field, a separate time field and an integer number value that will not exceed 99.

On the solution I would like to show something like this

Title : (from the 1 table in the 1:M query)

Date1 || Date 2 etc
Time1 | Time2 | Time3 | Time4 || Time1 | Time2 |Time 3 | etc

Fred | 1 | 4 | || 3 | 6 | 4 |
Bill | 3 | | 1 || 1 | 4 | |

I cannot use datepart since the date and time fields are sepaate fields. Note that there may be some null values for some results.

I hope that I have provided enought detail. I can supply a set of source data if this would help.

TIA for any help on this.

Helm

推荐答案

嗨Helm。遗憾的是，Access查询具有相当有限的数据透视 - 在交叉表查询中只能将一个字段指定为列标题，并且不可能有子标题。 Excel的枢轴设施更加全面，允许按多个字段分组的旋转数据的细分。


使用VBA可以生成编程输出更符合您的要求，但这需要时间来发展。也许其他贡献者可以提出另一种方式？


-Stewart

Hi Helm. Unfortunately, Access queries have fairly limited pivoting - only one field can be designated as the column header in a crosstab query and no sub-heads are possible. Excel''s pivot facilities are much more comprehensive, allowing for sub-divisions of the pivoted data grouped by more than one field.

Using VBA it is possible to produce programmed output that would more closely match your requirements, but this would take time to develop. Perhaps other contributors could suggest another way?

-Stewart

Hello Stewart并感谢您的回应。就像我想的那样。 Access 2007似乎没有在这个领域进行过更新。


我可以通过Access报告获得某种方式，但我仍然坚持使用固定列标题，并且Jan to Dec列标题解决方案不适用于90数据的百分比（大多数数据的寿命有限，为期三个月左右）。通过使用fconcatchild来选择所有记录（想想Dev Ashish产生的那些？），我或许可以接近''与行值'在同一行上的所有数据''。


实际上，我不需要交叉表总和，avg等，只是能够显示数据''喜欢''Excel输出。也许我一直把自己引入歧途！


我会挣扎，并希望有人能提出访问解决方案。顺便说一下，你碰巧知道如何在这个论坛上插入代码或屏幕快照 - 可能有助于更好地展示我正在尝试做的事情:-)


再次感谢


头盔

Hello Stewart and thanks for the response. Much as I thought. Access 2007 doesn''t seem to have been updated in this area.

I can get some way with an Access report but am still stuck with ''fixed'' column headers and the ''Jan to Dec'' column header solution is not appropriate for 90% of the data (most have a limited lifespan of three months or so). I may be able to get close to ''all the data on the same line as the row value'' by using fconcatchild to sub select all the records (think Dev Ashish produced that?).

Actually, I don''t need the crosstab sum, avg etc, just be able to show the data ''like'' the Excel output. Perhaps I''ve been leading myself astray!

I''ll struggle on and hope that someone can suggest an ''Access'' solution. BTW, do you happen to know how to insert code or a screen snapshot onto this forum - might help better show what I''m trying to do :-)

Thanks again

Helm

破解了！


使用访问交叉表查询作为访问报告的记录源。该报告基本上由未绑定的标签和控件组成，可以使用控件集合动态分配给每个记录。从报告记录源生成记录集。


有效。但是，由于我的日期格式为dd / mm，列顺序基于日期而不是月份。将交叉表中的格式更改为mm / dd解决了这个问题但是给出了''US'''mm / dd"列标签。使用MID函数将每个记录分成多个字符串并重新组合以返回到dd / mm。格式。


还发现我可以通过将其他分组字段插入交叉表然后将它们添加到（例如）行标题以供以后使用（例如再次）来获取其他有用数据）作为报告标题中的报告标题（Instr& Mid）。


所以......可以做到。有点乏味，但比倾销到Excel更快......而且更漂亮！


头盔

Cracked it!

Use your access crosstab query as the record source for an access report. The report basically consists of unbound labels and controls which can be dynamically allocated to each record using the controls collection. Produce a recordset from the report recordsource.

That works. However, since my dates are in the format "dd/mm" the column order is based on the day rather than the month. Changed the formatting in the crostab to mm/dd solved that but gives a ''US'' "mm/dd" column label. Used the MID function to split each record into a number of strings and recombined to get back to "dd/mm" format.

Also found that I could get other useful data by inserting other grouped fields to the crosstab and then adding them to (for example) the row heading for later use (again for example) as a report title in the report header (Instr & Mid).

So ... it can be done. Bit tedious but much faster than dumping into Excel ... and prettier too!

Helm

这篇关于与Excel相比，Access是否具有有限的交叉表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！