By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

rows to columns query

P: n/a
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

Nov 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Nov 23 '06 #2

P: n/a
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
Nov 23 '06 #3

P: n/a
"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

Nov 23 '06 #4

P: n/a
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

Nov 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.