Building a Complex Crosstab

[jerry.ranch]

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]

Hi Jerry,

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

TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;

[Bill]

On 20 Feb 2006 14:06:20 -0800, "Anton" <an******@gmail.com> wrote:

Hi Jerry,

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

TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;

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]

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" <an******@gmail.com> wrote:

Hi Jerry,

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

TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;

[Jerome Ranch]

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" <an******@gmail.com> wrote:

Hi Jerry,

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

TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;

[Jerome Ranch]

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.Trait_Name IN ("Seednum","HarvestDate");

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

TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;

[Bill]

On Tue, 21 Feb 2006 06:25:36 -0600, Jerome Ranch <ra*****@mchsi.com>
wrote:

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).

Are you doing this in Access?

Please let me know.

[Jerome Ranch]

yes

Are you doing this in Access?

Please let me know.

[Anton]

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.openrecordset("SELECT * FROM tbl_Shism WHERE
isdate(FirstOfTrait_Value) ORDER BY FirstOfTrait_Value ; ")
Do Until rs.EOF
MsgBox rs("FirstOfTrait_Value")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

[Anton]

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]

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.texttraitvalue) AS FirstofTextTraitValue
SELECT tblGT.EUID
FROM tblGT
GROUP BY tblGT.EUID
PIVOT tblGT.TraitName In
("Seednum_1","HarvDate_1","STATUSCALL_1","SendGHDa te_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.openrecordset("SELECT * FROM tbl_Shism WHERE
isdate(FirstOfTrait_Value) ORDER BY FirstOfTrait_Value ; ")
Do Until rs.EOF
MsgBox rs("FirstOfTrait_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.openrecordset("qryDataImport",dbOpenTable)
rs.Close
Thanks
Jerry


On 22 Feb 2006 13:17:51 -0800, "Anton" <an******@gmail.com> wrote:

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