Query that joins all the people associated with 1 institution into 1 row

[nickblitz]

Hey,

I have this query that displays the institution and the people associated with it.



SELECT Institution.InstitutionID, Institution.[Institution Name], Institution.[Address Line 1], Institution.[Address Line 2], Institution.City, Institution.State, Institution.Zipcode, Institution.Country, Institution.[Member Type], Institution.[Member Status], Institution.Comments, Institution.[Map Coordinates], Institution.Website, Institution.[Fees 2010], Institution.[Fees 2011], Institution.[Fees 2012], Institution.[Fees 2013], People.PeopleID, People.PersonTypeID, People.Salutation, People.[People Name], People.Title, People.Phone, People.Fax, People.Email, People.Bounces, People.[Secondary Email], People.[Bounces 2]
FROM Institution INNER JOIN People ON Institution.[InstitutionID] = People.[InstitutionID];

Can someone show me how to edit the this query to join all the people associated with the institution to display in 1 row?

[orange]

Why 1 line?
Have you tried to Normalize your Table structure? I ask because I see these fields in your Institution table

Institution.[Fees 2010], Institution.[Fees 2011], Institution.[Fees 2012], Institution.[Fees 2013],

I have a feeling you are more comfortable with Excel than Access.

I recommend you review Normalization and database design to get a clearer handle on these concepts that underlie everything you will do with Access or any other DBMS.

see http://forums.aspfree.com/attachment...2&d=1201055452

[nickblitz]

Why 1 line?
Have you tried to Normalize your Table structure? I ask because I see these fields in your Institution table

Institution.[Fees 2010], Institution.[Fees 2011], Institution.[Fees 2012], Institution.[Fees 2013],

I have a feeling you are more comfortable with Excel than Access.

I recommend you review Normalization and database design to get a clearer handle on these concepts that underlie everything you will do with Access or any other DBMS.

see http://forums.aspfree.com/attachment...2&d=1201055452

Because i need to keep track whether they have paid for the various years...

i want to use that query to do a word merge.

can you help me?

[nickblitz]

no one knows how to do that?

help please..

[nickblitz]

i have posted a dummy database in the link which you can download.


Basically i have 2 tables, institution and people.


I have a query called institution query that displays the data from both tables.


i need them in 1 line using a query.. so that i can do a word merge.


http://www.mediafire.com/download.php?17bjjpa4ll8275j

[orange]

I took a quick look at your database.
I have tried to do some Normalization on Tables and have posted a jpg of "possible" relationships and tables.

I say possible because I'm not happy with the Fee and MembershipType.
I moved some fields around because some field belong as attributes of other entities.
There are free data models for reference at http://www.databaseanswers.org

Can you have people in your scope that are not members"? (guests, family...)
Can a Member change membership types from one year to another?
Are there different Fees for different membership types? Do the Fees change from Year to Year?

I recommend you do not have Field and Object names with embedded spaces.

Good luck with your project.