Need help with streamlining a query

[DelbyJones]

I run a preschool with Access 2010. I’m trying to build a query that, without modification, I can merge to e-mails with Word, giving parents a monthly update on their child’s status on various waitlists. (A child can be on up to 4 different waitlists, although some are just one one.)

I have a query which returns a list of all waitlisted children but it has a separate line for each waitlist the child is on. Next I export that query to Excel, then I have to cut and paste by hand for the kids who are on more than one waitlist, waitlist2, waitlist3, and waitlist4 so each child has only one line on the spreadsheet and the family gets one e-mail with all the information.

Ideally I’d like a query which would eliminate the hand work, which is time-consuming. Can anyone point me in the direction for building a query (perhaps a make-table query?) that would return a datasheet with only one line for each child, that has separate columns for any additional waitlist listings for each child?

For example, my merged e-mail says: Your child Sally Smith is now
#3 on the Mon-Wed-Fri AM class waitlist,
#5 on the Tuesday-Thursday AM class waitlist
#1 on the Tuesday-Thursday PM class waitlist

I know I can do this with a report, but I don’t think there’s a quick and simple way to automatically e-mail report pages to different people? (Please correct me if that’s not true.)

Thanks for any help anyone can offer.

[June7]

Reports can be emailed as a pdf attachment using the SendObject method. http://www.fmsinc.com/MicrosoftAcces...endObject.html

Code could loop through a recordset and send individual email to each address in the recordset.

There are other methods to send emails that will involve more code. https://www.accessforums.net/program...ook-21903.html

[DelbyJones]

Thanks for this response. I now consider myself an intermediate user, but I've never written code. I'm going to read up then play around a bit with what you sent you sent me.

Will come back with what I can (and cannot) figure out!

[June7]

Start with review of http://office.microsoft.com/en-us/ac...010341717.aspx