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?

 

[John Big Booty]

Have a look at the various options that are available in a Totals Query. You'd probably need to use the Count option for the people.

 

[nickblitz]

Have a look at the various options that are available in a Totals Query. You'd probably need to use the Count option for the people.

hello, thanks for replying. i do not want to display how many people are there.

each institution has around 3 contacts.

the above query displays the data like this.

i need it to be in 1 row.. 1 institution with all the contacts in 1 row.

 

[John Big Booty]

If you want to bring that query down to a single record for each institution, you will either need to remove the contacts from the query, or use a Totals query and Group By the institution details and count or use one of the other options to get the Contacts to a single record.

 

[nickblitz]

If you want to bring that query down to a single record for each institution, you will either need to remove the contacts from the query, or use a Totals query and Group By the institution details and count or use one of the other options to get the Contacts to a single record.

defeats the purpose if i remove the contacts...

can you help to edit my query above?

 

[John Big Booty]

If you want to see all the contacts as one record, you'll first need to run a query or procedure to collect all the contacts in a De-normalised form, and then use that in a second query that matches that denormalised data against each institution.

 

[nickblitz]

If you want to see all the contacts as one record, you'll first need to run a query or procedure to collect all the contacts in a De-normalised form, and then use that in a second query that matches that denormalised data against each institution.

thanks. :banghead:

and how do i do that??

 

[MarkK]

So, Nick you've got people working on this for you at two different sites. Should get twice as a good an answer in half the time, eh?
Cheers,

 

[nickblitz]

So, Nick you've got people working on this for you at two different sites. Should get twice as a good an answer in half the time, eh?
Cheers,

i havent got an answer yet..

 

[nickblitz]

no one can really help me?

 

[John Big Booty]

Search this forum on the subject of de-normalising data, it comes up on a regular basis.

 

[nickblitz]

Search this forum on the subject of de-normalising data, it comes up on a regular basis.

i didnt find any. :banghead:

 

[nickblitz]

perhaps you need a copy of my database for you to understand my problem?

 

[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

 

[John Big Booty]

If you want to post a copy of your DB. Then do a compact and repair on it then put it in a Zip file and post the zip file.

 

[nickblitz]

If you want to post a copy of your DB. Then do a compact and repair on it then put it in a Zip file and post the zip file.

sorry i didnt know. you cant use mediafire?

 

[John Big Booty]

sorry i didnt know. you cant use mediafire?

No; I won't use mediafire.

 

[nickblitz]

No; I won't use mediafire.

ok done. attached.