rows to columns query

[Andy M]

Hi

what I am trying to do is this:

I have this:

Family Names ¦ First Names
_______________________
Family Name1 ¦ First Name1
Family Name1 ¦ First Name2
Family Name1 ¦ First Name3
Family Name2 ¦ First Name1
etc

What I need is this

Family Names ¦ FirstNameA ¦ FirstNameB ¦ FirstNameC ¦ FirstNameD ¦
__________________________________________________ ___________
Family Name1 ¦ FirstName1 ¦ FirstName2 ¦ FirstName3 ¦ FirstName4
¦
Family Name2 ¦ FirstName1 ¦ FirstName2 ¦ FirstName ¦
¦

I have almost achieved this using a Select Query with Totals, Grouping
by Family Name, and using "first" and "last" for the First Name field,
which gives me 2 first names in a row - which is OK if thats all there
are, but I need the second, third and nth first names also.
I posted this in the ms.public.access.queries forum, but the only
suggestion I got was to use a crosstab query - but I cant see how that
would work

Thanks!

Andy

[tina]

the standard, and AFAIK quickest and easiest, way to "denormalize" data for
presentation purposes, is by using a crosstab query. did you actually try
it? if so, what was the result?

hth
"Andy M" <tw********@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
Hi

what I am trying to do is this:

I have this:

Family Names ¦ First Names
_______________________
Family Name1 ¦ First Name1
Family Name1 ¦ First Name2
Family Name1 ¦ First Name3
Family Name2 ¦ First Name1
etc

What I need is this

Family Names ¦ FirstNameA ¦ FirstNameB ¦ FirstNameC ¦ FirstNameD ¦
__________________________________________________ ___________
Family Name1 ¦ FirstName1 ¦ FirstName2 ¦ FirstName3 ¦ FirstName4
¦
Family Name2 ¦ FirstName1 ¦ FirstName2 ¦ FirstName ¦
¦

I have almost achieved this using a Select Query with Totals, Grouping
by Family Name, and using "first" and "last" for the First Name field,
which gives me 2 first names in a row - which is OK if thats all there
are, but I need the second, third and nth first names also.
I posted this in the ms.public.access.queries forum, but the only
suggestion I got was to use a crosstab query - but I cant see how that
would work

Thanks!

Andy

[Andy M]

Hi Tina,
Yes I've tried it and I can see how I could do a calculation on the
"FirstName" fields if I wanted to - showing for example the total
number of firstnames per family name, or even listing the first and
last firstname record, but that still doesnt get me a listing of every
first name in a family in one row.

Andy

I could have Family Name as a Row Title and First Name as a column
title
tina wrote:

the standard, and AFAIK quickest and easiest, way to "denormalize" data for
presentation purposes, is by using a crosstab query. did you actually try
it? if so, what was the result?

hth
"Andy M" <tw********@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
Hi

what I am trying to do is this:

I have this:

Family Names ¦ First Names
_______________________
Family Name1 ¦ First Name1
Family Name1 ¦ First Name2
Family Name1 ¦ First Name3
Family Name2 ¦ First Name1
etc

What I need is this

Family Names ¦ FirstNameA ¦ FirstNameB ¦ FirstNameC ¦ FirstNameD ¦
__________________________________________________ ___________
Family Name1 ¦ FirstName1 ¦ FirstName2 ¦ FirstName3 ¦ FirstName4
¦
Family Name2 ¦ FirstName1 ¦ FirstName2 ¦ FirstName ¦
¦

I have almost achieved this using a Select Query with Totals, Grouping
by Family Name, and using "first" and "last" for the First Name field,
which gives me 2 first names in a row - which is OK if thats all there
are, but I need the second, third and nth first names also.
I posted this in the ms.public.access.queries forum, but the only
suggestion I got was to use a crosstab query - but I cant see how that
would work

Thanks!

Andy

[Bob Quintal]

"Andy M" <tw********@gmail.comwrote in
news:11*********************@45g2000cws.googlegrou ps.com:

Hi Tina,
Yes I've tried it and I can see how I could do a calculation
on the "FirstName" fields if I wanted to - showing for example
the total number of firstnames per family name, or even
listing the first and last firstname record, but that still
doesnt get me a listing of every first name in a family in one
row.

Andy

I could have Family Name as a Row Title and First Name as a
column title
tina wrote:

>the standard, and AFAIK quickest and easiest, way to
"denormalize" data f

or

>presentation purposes, is by using a crosstab query. did you
actually try it? if so, what was the result?

hth
"Andy M" <tw********@gmail.comwrote in message
news:11**********************@h54g2000cwb.googleg roups.com...
Hi

what I am trying to do is this:

I have this:

Family Names ¦ First Names
_______________________
Family Name1 ¦ First Name1
Family Name1 ¦ First Name2
Family Name1 ¦ First Name3
Family Name2 ¦ First Name1
etc

What I need is this

Family Names ¦ FirstNameA ¦ FirstNameB ¦ FirstNameC ¦
FirstNameD

¦

>_________________________________________________ ____________
Family Name1 ¦ FirstName1 ¦ FirstName2 ¦ FirstName3 ¦
FirstName4 ¦
Family Name2 ¦ FirstName1 ¦ FirstName2 ¦ FirstName ¦
¦

I have almost achieved this using a Select Query with Totals,
Grouping by Family Name, and using "first" and "last" for
the First Name field,
which gives me 2 first names in a row - which is OK if thats
all there
are, but I need the second, third and nth first names also.
I posted this in the ms.public.access.queries forum, but the
only suggestion I got was to use a crosstab query - but I
cant see how that would work

Thanks!

Andy

search the web for a piece of code called fConcatChild

Create a summary or distinct query that gives you the family
name once, and add this function to return the firstnames.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[tina]

ah, there you go, Andy. Bob saved the day. Happy Thanksgiving! :)
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

"Andy M" <tw********@gmail.comwrote in
news:11*********************@45g2000cws.googlegrou ps.com:

Hi Tina,
Yes I've tried it and I can see how I could do a calculation
on the "FirstName" fields if I wanted to - showing for example
the total number of firstnames per family name, or even
listing the first and last firstname record, but that still
doesnt get me a listing of every first name in a family in one
row.

Andy

I could have Family Name as a Row Title and First Name as a
column title
tina wrote:

the standard, and AFAIK quickest and easiest, way to
"denormalize" data f

or

presentation purposes, is by using a crosstab query. did you
actually try it? if so, what was the result?

hth
"Andy M" <tw********@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
Hi

what I am trying to do is this:

I have this:

Family Names ¦ First Names
_______________________
Family Name1 ¦ First Name1
Family Name1 ¦ First Name2
Family Name1 ¦ First Name3
Family Name2 ¦ First Name1
etc

What I need is this

Family Names ¦ FirstNameA ¦ FirstNameB ¦ FirstNameC ¦
FirstNameD

¦

__________________________________________________ ___________
Family Name1 ¦ FirstName1 ¦ FirstName2 ¦ FirstName3 ¦
FirstName4 ¦
Family Name2 ¦ FirstName1 ¦ FirstName2 ¦ FirstName ¦
¦

I have almost achieved this using a Select Query with Totals,
Grouping by Family Name, and using "first" and "last" for
the First Name field,
which gives me 2 first names in a row - which is OK if thats
all there
are, but I need the second, third and nth first names also.
I posted this in the ms.public.access.queries forum, but the
only suggestion I got was to use a crosstab query - but I
cant see how that would work

Thanks!

Andy

search the web for a piece of code called fConcatChild

Create a summary or distinct query that gives you the family
name once, and add this function to return the firstnames.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com