convert records to fields

[twentyone6]

Hi
I have 2 tables with a one to many relationship (tblhousehold and
tblfamilymembers)

In order to do a mail merge email, I need to create a flat file where
all family members for a household are listed in "one row."

I can almost get there by using a totals query and Grouping by family
name. I am then using and the First and Last functions, which gives me
the first and last family member for each house. What I need is a way
to get the first plus 1, plus 2, ....plus n.

I have fiddled around with using the dcount function and the first
function as a criteria with no success....any ideas?


Thanks
Andy

 

[Rick Brandt]

Hi
I have 2 tables with a one to many relationship (tblhousehold and
tblfamilymembers)

In order to do a mail merge email, I need to create a flat file where
all family members for a household are listed in "one row."

I can almost get there by using a totals query and Grouping by family
name. I am then using and the First and Last functions, which gives me
the first and last family member for each house. What I need is a way
to get the first plus 1, plus 2, ....plus n.

I have fiddled around with using the dcount function and the first
function as a criteria with no success....any ideas?

See if a Crosstab query will give you what you want. It's one of the choices in
the new query wizard.

 

[Andy M]

OK, had a look at crosstab queries, but I cant see how it would what I
need ...but thanks for the suggestion.

 

[Andy M]

Maybe I should explain my problem more:

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 ¦
¦

If it is possible to do this with a cross tab query I'd appreaciate a
nudge in the right direction

Thanks!

Andy