Building a Complex Crosstab

[jerry.ranch@pioneer.com]

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

[Anton]

Re: Building a Complex Crosstab

Hi Jerry,

Try pasting the following into the SQL section of a new query - check
that the table name is correct.

TRANSFORM First(tbl_Trait s.Trait_Value) AS FirstOfTrait_Va lue
SELECT tbl_Traits.Iden tifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Iden tifyer
PIVOT tbl_Traits.Trai t_Name;

[Bill]

Re: Building a Complex Crosstab

On 20 Feb 2006 14:06:20 -0800, "Anton" <antonric@gmail .com> wrote:
[color=blue]
>Hi Jerry,
>
>Try pasting the following into the SQL section of a new query - check
>that the table name is correct.
>
>TRANSFORM First(tbl_Trait s.Trait_Value) AS FirstOfTrait_Va lue
>SELECT tbl_Traits.Iden tifyer
>FROM tbl_Traits
>GROUP BY tbl_Traits.Iden tifyer
>PIVOT tbl_Traits.Trai t_Name;[/color]

I have Access 2000. I cannot find the TRANSFORM keyword in the help
file.

Is it in A2000? What does it do?

Thanks.

[Jerome Ranch]

Re: Building a Complex Crosstab

Wow Anton
Works great
I had a few syntax erros I had to work out, but I understand.
Is there a way to assign the proper format to the fields, text dates
to dates, text to numeric, etc? These seems not trivial since this
method does not explicitly identify the pivot columns.
Thanks for your help.
I learned something today with this..very powerful
Jerry

On 20 Feb 2006 14:06:20 -0800, "Anton" <antonric@gmail .com> wrote:
[color=blue]
>Hi Jerry,
>
>Try pasting the following into the SQL section of a new query - check
>that the table name is correct.
>
>TRANSFORM First(tbl_Trait s.Trait_Value) AS FirstOfTrait_Va lue
>SELECT tbl_Traits.Iden tifyer
>FROM tbl_Traits
>GROUP BY tbl_Traits.Iden tifyer
>PIVOT tbl_Traits.Trai t_Name;[/color]

[Jerome Ranch]

Re: Building a Complex Crosstab

Anton
In my limited knowledge, the only way I can think of to convert these
columns to proper format is to write this qry out to a table, and then
change the format of the fields. Not very elegant.
Jerry


On 20 Feb 2006 14:06:20 -0800, "Anton" <antonric@gmail .com> wrote:
[color=blue]
>Hi Jerry,
>
>Try pasting the following into the SQL section of a new query - check
>that the table name is correct.
>
>TRANSFORM First(tbl_Trait s.Trait_Value) AS FirstOfTrait_Va lue
>SELECT tbl_Traits.Iden tifyer
>FROM tbl_Traits
>GROUP BY tbl_Traits.Iden tifyer
>PIVOT tbl_Traits.Trai t_Name;[/color]

[Jerome Ranch]

Re: Building a Complex Crosstab

Looks like I can use the IN with the PIVOT function to specify which
Trait_Name entries I wish to convert to fields (specify order here as
well).

ex.
PIVOT tbl_Traits.Trai t_Name IN ("Seednum","Har vestDate");



[color=blue]
>Try pasting the following into the SQL section of a new query - check
>that the table name is correct.
>
>TRANSFORM First(tbl_Trait s.Trait_Value) AS FirstOfTrait_Va lue
>SELECT tbl_Traits.Iden tifyer
>FROM tbl_Traits
>GROUP BY tbl_Traits.Iden tifyer
>PIVOT tbl_Traits.Trai t_Name;[/color]

[Bill]

Re: Building a Complex Crosstab

On Tue, 21 Feb 2006 06:25:36 -0600, Jerome Ranch <ranchjp@mchsi. com>
wrote:
[color=blue]
>Looks like I can use the IN with the PIVOT function to specify which
>Trait_Name entries I wish to convert to fields (specify order here as
>well).
>[/color]
Are you doing this in Access?

Please let me know.

[Jerome Ranch]

Re: Building a Complex Crosstab

yes
[color=blue]
>Are you doing this in Access?
>
>Please let me know.[/color]

[Anton]

Re: Building a Complex Crosstab

Hi agian Jerry,

When you run the query there is a variety of data types contained in
the Trait_Value field. If you want to sort/filter by date (or any other
type) you can do so at runtime eg: cdate, cint, cstr etc. Additionally,
you can also turn the crosstab query into a make table query and
reference records containing dates...or whatever else you want.
Possibly something like: (You need a reference to DAO 3.6)

Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordse t("SELECT * FROM tbl_Shism WHERE
isdate(FirstOfT rait_Value) ORDER BY FirstOfTrait_Va lue ; ")
Do Until rs.EOF
MsgBox rs("FirstOfTrai t_Value")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

[Anton]

Re: Building a Complex Crosstab

Jerry,

Before you go running that little bit of code I just posted you might
like to change the msgbox to debug.print and show the debug window to
see the results, else you might be there for a while. Also note that
you can use the sql statement in the recordsource of the form to filter
the records. This should give you enough control over the data that you
want to show. Have fun Anton

[jerry.ranch@pioneer.com]

Re: Building a Complex Crosstab

Anton
Thanks for all your replies and help
This is where I am at now
I built the qry in SQL as you suggested

Here it is

TRANSFORM First(tblGT.tex ttraitvalue) AS FirstofTextTrai tValue
SELECT tblGT.EUID
FROM tblGT
GROUP BY tblGT.EUID
PIVOT tblGT.TraitName In
("Seednum_1","H arvDate_1","STA TUSCALL_1","Sen dGHDate_1");

Note I used the IN statement to order and choose the TraitName I want


Here's an example of the results..nice, but as we agree, the format of
the fields is wrong. The numeric Seednum_1 and date Harvdate_1 and
SendGHDate_1 are still text values



EUID Seednum_1 HarvDate_1 STATUSCALL_1 SendGHDate_1
31341863 217 04/26/2004 Harvest 01/19/2004
31341864 79 04/14/2004 Harvest 01/19/2004
31341865 9 04/19/2004 Harvest 01/19/2004
31341866 54 04/19/2004 Harvest 01/19/2004
31341867 238 04/14/2004 Harvest 01/19/2004


There are indeed two more things to do.
1) Get these fields into the proper format, and
2) write this query to a table (so it can be joined with other tables
and have some use!)

As you suggest the best would be to do both simultaneously

I don't see how to convert these data in this query. Indeed Cdate()
takes a text date and converts it to real date,but CInt() does not
work for me when I use Cint() in a test database to convert a text
nuumeric to a real numeric. I get a "function not defined" error ..
so I used CDbl() and that does work.
I don't see how to use these conversions in this query SQL. I tried
placing them in the IN statement, but that clearly is wrong syntax.

So I did some reading on manipulating recordsets in my VBA
handbbooks.. I know enough to be dangerous, but not useful.
The code you suggested to write this out as a table is what needs to
done, but I really don't know how to implement it. I presume this
would be a module associated with the db. DAO 3.6 is referenced in
the datbase already. How do I call it? Does it run when I execute
the query?

Anton's code example
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordse t("SELECT * FROM tbl_Shism WHERE
isdate(FirstOfT rait_Value) ORDER BY FirstOfTrait_Va lue ; ")
Do Until rs.EOF
MsgBox rs("FirstOfTrai t_Value")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing


Why would I have to loop through to write this out to a table?
And there is still no way to make the data conversions

(if I call the qry above qryDataImport)

Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordse t("qryDataImpor t",dbOpenTab le)
rs.Close


Thanks
Jerry




On 22 Feb 2006 13:17:51 -0800, "Anton" <antonric@gmail .com> wrote:
[color=blue]
>Jerry,
>
>Before you go running that little bit of code I just posted you might
>like to change the msgbox to debug.print and show the debug window to
>see the results, else you might be there for a while. Also note that
>you can use the sql statement in the recordsource of the form to filter
>the records. This should give you enough control over the data that you
>want to show. Have fun Anton[/color]