构建复杂的交叉表

本文介绍了构建复杂的交叉表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我从公司的Sybase数据库中获取提取/报告。这是一个非常大的交易数据库，非常少（或者很慢和

复杂的）报告/查询，所以相反我有一个包罗万象的查询

运行，每晚都放入一个文件夹，然后通过Access执行我的数据

分析/挖掘。

现在这里是我的问题

DATA（存储为Trait）不存储在每个

特征的单独字段中..这里是一个例子

Identifier Trait_Name Trait_Value

12345 SeedNumber 147

12345 HarvestDate 4/15/2005

12345状态收获

等

每个标识符可能有很多行...因为许多行

是与每个标识符相关联的唯一特征和特征值。

当然Trait_Value字段是字符。

现在我想要做的是构建一个交叉表，以便数据看起来像

喜欢这个..每个标识符一行，每个Trait_Name获取一个字段名称

并将trait_values转换为数字，日期或字符

符合类型他们是数据。对于我想要使用的每个特性，我将有一个单独的字段

。

标识符SeedNumber HarvestDate状态

12345 147 4/15/2005收获

12346

12347 23 5/20/2001收获

12348 Killed

这就是我到目前为止在Access2003，QBE窗口中的
，我建立了这个

标识符种子：Iff（[Trait_Name] = SeedNumber，[Trait_Value]，"等

Group by First

这是正确的方法吗？我怎样才能将

信息转换为正确的格式（日期，数字）....我假设使用

format（）？

也许有一种方法可以更容易（并且有力？）在SQL中执行此操作？

如果是这样，我现在也必须学习。

谢谢

Jerry

I get extracts/reports from a corporate Sybase database. This is a
very very large transactional database, very little (or slow and
complex) reports/queries, so instead I have an all-inclusive query
run, dropped into a folder each night, and then do my data
analysis/mining via Access.

Now here''s my problem

The DATA (stored as a Trait) is not stored in separate fields for each
trait .. here''s an example

Identifier Trait_Name Trait_Value
12345 SeedNumber 147
12345 HarvestDate 4/15/2005
12345 Status Harvested
etc
There may be many rows for each identifier... as many rows as there
are unique trait and trait values assocaited with each identifier. Of
course the Trait_Value field is character.

Now what I want to do is to build a crosstab so that the data looks
like this..each identifier a row, each Trait_Name gets a field name
and the trait_values are converted to numeric, date or character to
conform to the type of data they are. I will have a separate field
for each Trait I want to use.
Identifier SeedNumber HarvestDate Status
12345 147 4/15/2005 Harvested
12346
12347 23 5/20/2001 Harvested
12348 Killed

This is what I have so far

in Access2003, QBE window, I built this

Identifier Seed:Iff([Trait_Name]="SeedNumber",[Trait_Value],"") etc
Group by First

So is this the correct way to do this? And how might I convert the
information to the correct format (Date, numeric).... I presume use
format()?

Maybe there''s a way to more easily (and powerfully?) do this in SQL?
If so, I will have to learn that now as well.

Thanks
Jerry

12345

构建复杂的交叉表

问题描述

推荐答案