Loop through a table and create new tables based on a value

[AppDev63]

I have a table with a location ID; multiple locations per ID. I need to loop throught that table and create new tables for each location ID so that I can send a report out to each location, containing only the data for that location ID.

I also have the email address of the location stored in the same table.

How can I loop through this single table, or maybe just filter it programmaticall y somehow for each location ID and then send an email with just the filtered results as an Excel attachment using the email address associated with each location ID?

[JConsulting]

I have a table with a location ID; multiple locations per ID. I need to loop throught that table and create new tables for each location ID so that I can send a report out to each location, containing only the data for that location ID.

I also have the email address of the location stored in the same table.

How can I loop through this single table, or maybe just filter it programmaticall y somehow for each location ID and then send an email with just the filtered results as an Excel attachment using the email address associated with each location ID?

this could go either way...depending on how your table is layed out.

can you give an brief data sample?

if you already have a report built that shows ALL locations, then we're ahead of the game.

you can use the sendobject command to send a report but in order to get the e-mail address out of the table, along with the ID, then wwe need to know more info.

For example, if I do a simple query to say "Select * from yourtable group by ID;"

will I get say 5 different locations each with a different e-mail address?

[AppDev63]

For example, if I do a simple query to say "Select * from yourtable group by ID;"

will I get say 5 different locations each with a different e-mail address?

Yes, that's exactly right. There multiple records per location, and each location has a different email address.

[AppDev63]

this could go either way...depending on how your table is layed out.

can you give an brief data sample?

if you already have a report built that shows ALL locations, then we're ahead of the game.

you can use the sendobject command to send a report but in order to get the e-mail address out of the table, along with the ID, then wwe need to know more info.

For example, if I do a simple query to say "Select * from yourtable group by ID;"

will I get say 5 different locations each with a different e-mail address?

Yes I do have a table that shows ALL locations. If you can help me figure out just the email piece I'd really appreciate it. It's been a while since I posted this.

Thank you!

[JConsulting]

Yes I do have a table that shows ALL locations. If you can help me figure out just the email piece I'd really appreciate it. It's been a while since I posted this.

Thank you!

Here is a code excerpt from a function written to loop through a table and send e-mails. I'm not clear yet how many addresses will go on each of your e-mails....but the method here will give you a starting point

    Dim rs As DAO.Recordset
    Dim strEmailRec  as string
    Dim strSubject  as string
    Dim strMessageText  as string
 
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourLocationTable")
        Do While Not rs.EOF
            strEmailRec =  rs.Fields("[email]").Value
            strSubject = "Your Subject"
            strMessageText = "Your Message"
            'Send the e-mail with the spreadsheet attached. Change TRUE to FALSE if you don't want to review the e-mail
            DoCmd.SendObject acQuery, "YourQuery, "MicrosoftExcel(*.xls)", EmailRec, "", "", strSubject, strMessageText, True, ""
            rs.MoveNext
        Loop
 

J