 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2006Return list of data from query
Im looking for a way to return the information of multiple contacts taken on a per company basis for my own custom bulk emailing (mail merging). The data I have is like this. 1st 2nd 3rd COMPANY FIRSTNAME SURNAME X ABC Co John Smith X ABC Co Jamie MacDonald X ABC Co Jake Peters X XYZ Co Peter Wells X XYZ Co Paul Collins How would I be able to query the data such that I can return a recordset that I can use to get all the contacts (Contacts table) from the Companies (Company table) such that I could go (in a label or something): Dear [1st, 2nd and 3rd] from [Company]. I really enjoyed our meeting the other day.... Dear [John, Jamie and Jake] from ABC Co. I really enjoyed our meeting the other day.... Where John, Jamie and Paul are the 1st, 2nd and 3rd conacts from a company. I can easily do it when there is only one contact but how do you get the information of the 1st, 2nd and 3rd contact in a single record or is there another way to do that? Rather than using your approach, it's far better to use two tables. The first table contains information about the company, the second table contains information about the individuals, with each row in the second table pointing back to the appropriate row in the first table. What you're essentially looking for is something like http://www.databaseanswers.org/data_models/contact_management/index.htm  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Im looking for a way to return the information of multiple contacts > taken on a per company basis for my own custom bulk emailing (mail [quoted text clipped - 28 lines] > the information of the 1st, 2nd and 3rd contact in a single record or > is there another way to do that? Hi Douglas, I dont think I was very clear. My data is already in the form you have described. A company table and a contacts table. There is a foreign key in contacts table pointing to a company. My problem is I need to be able to display contact information for each company like if there are 3 contacts for ABC Co then I need to loop through and display in a sentence.. Dear 1st contact, 2nd contact and 3rd contact where 1st - 3rd are fields in the contacts table marking this info about contacts. Hope that explains. Nice link btw with the data models. > Rather than using your approach, it's far better to use two tables. > [quoted text clipped - 42 lines] > > the information of the 1st, 2nd and 3rd contact in a single record or > > is there another way to do that? Sorry! You're talking about concatenating related records. Take a look at http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hi Douglas, > [quoted text clipped - 60 lines] >> > the information of the 1st, 2nd and 3rd contact in a single record or >> > is there another way to do that? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.