Cross Tabulation Query / Report

[Pronto]

Hello Access gurus,



I need to do something similar to a cross tabulation.



Here is an example, based on a Postgresql backend.





------------------------------------------------------------

-- TABLE DEFINITION

------------------------------------------------------------

CREATE TABLE public.type

(

code char(1) PRIMARY KEY,

type varchar(30)

);



CREATE TABLE public.master

(

id int4 PRIMARY KEY,

master_name varchar(30)

);



CREATE TABLE public.detail

(

id int4 PRIMARY KEY,

code_type char(1) REFERENCES public.type,

id_master int4 REFERENCES public.master,

detail_name varchar(30)

);



------------------------------------------------------------

-- DATA

------------------------------------------------------------

INSERT INTO public.type VALUES('A', 'TA');

INSERT INTO public.type VALUES('B', 'TB');

INSERT INTO public.type VALUES('C', 'TC');



INSERT INTO public.master VALUES(1, 'M1');

INSERT INTO public.master VALUES(2, 'M2');

INSERT INTO public.master VALUES(3, 'M3');



INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');

INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');

INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');

INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');

INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');

INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');

INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');

INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');

INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');

INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');

INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');

INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');



------------------------------------------------------------

-- QUERY

------------------------------------------------------------

SELECT

master_name,

detail_name,

type



FROM

master INNER JOIN detail

ON master.id = detail.id_master



INNER JOIN type

ON detail.code_type = type.code



ORDER by master.id, detail.id;



------------------------------------------------------------





The result of that is:



----------------------------------

master_name | detail_name | type |

----------------------------------

M1 | M1, D1 | TA |

M1 | M1, D2 | TB |

M1 | M1, D3 | TA |

M1 | M1, D4 | TC |

M2 | M2, D1 | TC |

M3 | M3, D1 | TA |

M3 | M3, D2 | TA |

M3 | M3, D3 | TB |

M3 | M3, D4 | TA |

M3 | M3, D5 | TB |

M3 | M3, D6 | TC |

M3 | M3, D7 | TC |

----------------------------------





I need something like this:



----------------------------------------

master_name | TA | TB | TC |

----------------------------------------

M1 | M1, D1 | | |

M1 | | M1, D2 | |

M1 | M1, D3 | | |

M1 | | | M1, D4 |

M2 | | | M2, D1 |

M3 | M3, D1 | | |

M3 | M3, D2 | | |

M3 | | M3, D3 | |

M3 | M3, D4 | | |

M3 | | M3, D5 | |

M3 | | | M3, D6 |

M3 | | | M3, D7 |

----------------------------------------





Does anyone know how to get that with an Access crosstab query, or maybe
with a report? The problem here is that I don't need any aggregation at all,
and apprently, an crosstab query in Access wants you to specify that.





Thanks for any idea you might have.



Philippe Lang

 

[Duane Hookom]

You really don't need to put all your sql code etc in the body of a message
in these news groups. While this is expected in many SQL NGs, it might be
better to place it at the bottom of your email. Place your requirements and
sample data near the top.
You should be able to use a crosstab based on your final sql. Set the
Master_Name as the Row Heading, Type as the Column Heading, and first of
Detail_Name as the Value. You may want to enter all possible values of Type
in the crosstab's Column Heading property.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The resultset you describe is NOT the result of a cross-tab query.
What you are describing Access, nor, AFAIK, PostgreSQL have this
function. I've seen a user-defined function (UDF) that does this, on
the net, or some book, but can't remember where. Perhaps you can
search for it using Google? I believe the function name was LIST().


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDUOHoechKqOuFEgEQKzsACfet3yHrLDk4cq4NKU4dLgsradvOAAniPO
aEQZr2GFCKbpAepwdqXJFxLs
=eK4M
-----END PGP SIGNATURE-----

 

[Pronto]

You really don't need to put all your sql code etc in the body of a message
in these news groups. While this is expected in many SQL NGs, it might be
better to place it at the bottom of your email. Place your requirements and
sample data near the top.
You should be able to use a crosstab based on your final sql. Set the
Master_Name as the Row Heading, Type as the Column Heading, and first of
Detail_Name as the Value. You may want to enter all possible values of Type
in the crosstab's Column Heading property.

Hello,

Thanks for your answer.

I'm not sure your solution works, in fact. For the same Master_Name, there
can be several Detail_Name of the same type. So the aggregate function
"first of" does not really help here.

----------------------------------------
master_name | TA | TB | TC |
----------------------------------------
M1 | M1, D1 | | |
M1 | | M1, D2 | |
M1 | M1, D3 | | |
----------------------------------------

I have the feeling it's not possible to do that with an Access
cross-tabulation. Maybe the best reason for that is (as another person
mentioned) that what I want is NOT a cross-tabulation.

I found a solution, based on a PG Query. I'm not sure it is possible to do
that with an Access-only solution. Is it?

------------------------------------------------------------
-- QUERY
-- This is a Postgresql query.
------------------------------------------------------------
SELECT
master_name,
CASE WHEN type = 'TA' THEN detail_name END as TA,
CASE WHEN type = 'TB' THEN detail_name END as TB,
CASE WHEN type = 'TC' THEN detail_name END as TC
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;

 

[Duane Hookom]

I think a crosstab might work if you add the [Type] field to the Row Heading
as a group by. This will create unique rows for every record. If you want
all occurances then you could concatenate all the Detail_Name values into a
single expression possibly with a CR LF between each.

There is generic concatenation code at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You will
also find some crosstab reports on the same page that show different methods
of displaying a crosstabulation on a report. Some use a crosstab query and
others use either multi-colulmn or multi-subreports.