Email Users Who Havent Made Their Picks

[iggles]

I searched the forums here (although I will say the search was frustrating) and didnt see anything. Is there an easy way to send out reminders to users who havent made their picks?

[jhronesz]

I actually set this up last year. There isn't anything really easy about it though. I use the access database version and created a couple of queries within it. One that looks at who turned in picks in a specific week and one that compares that list to the user list. Then I have an email page that pulls the names.

[kingmullet]

Is there anyway you can share the queries for that. I would really like to do that as well.

[iggles]

Yea man, if you can share this I will love you long time.

[jhronesz]

I've attached the queries and the two web pages that I made to use. For the queries, open your database, click get external data. import the queries from this database. I'm not sure if it will work, but give it a try.

Each week, you will have to open the two queries that have "CW" in them in design mode. you will see the week number. change the week number to the current week. that will pull the list of users that haven't turned their picks in. Hope this helps.

[kingmullet]

Thanks so much you the man!!! I am running a sql version of the pool so I am going to convert the queries. I will share that with anyone that wants them as well.

[kingmullet]

SO I made this work pretty well. I created the queries and made a script to load for each week. I then created a scheduled task to run weekly and execute each script. Thanks so much for the initial help.

[jhronesz]

Any insight if there is a way to do this in access?

[kingmullet]

I will work on it. Where are you hosting your page?

[kingmullet]

Try this:
Create a query for each week,
Create a macro that runs the query.
Run the macro using the Windows Task Scheduler Program or some other
scheduling app.

Using the windows task scheduler...
- add a Scheduled Task
- migrate to Microsoft Access as the program you want to run
- when you've completed the wizard, right click to view the properties of
the new task
- you'll need to add the path of your database, in quotes, to the end
of the existing string in the "Run" box.
- then, at the end of the path you just added, enter the following...
/x NameOfMacro
- it should look something like this: "C:\Program Files\Microsoft
Office\Office\msaccess.exe" "C:\My Documents\MyDatabase.mdb" /x NameOfMacro
--the first part may vary depending on the version of Access
you're running--

[jhronesz]

I am hosting my own.

[kingmullet]

Did you try using the macro yet?

[mcrochet]

Hello,

I took the ASP page from the previous post and was able to modify it enough to get the query added to the page instead of running a separate query outside of the code. It basically will report on the current week of the season. I have not added the ability to select a week yet. The query basically checks to see which users are not in the weeklyscoring table for the week. During testing if I added a record for a user to the weekscoring table, the user would not show up in the report.

Let me know if you have any questions or find any errors.

Thanks,

[jhronesz]

I did what someone suggested before. I set up a query for each week and added code to a contact page. All I have to do now is click on the page and it pulls who hasn't turned in their picks. I then have the email message field defaulted to tell them to turn in their picks.

[iggles]

Hello,

I took the ASP page from the previous post and was able to modify it enough to get the query added to the page instead of running a separate query outside of the code. It basically will report on the current week of the season. I have not added the ability to select a week yet. The query basically checks to see which users are not in the weeklyscoring table for the week. During testing if I added a record for a user to the weekscoring table, the user would not show up in the report.

Let me know if you have any questions or find any errors.

Thanks,

Thanks for this! Sorry, super noob though. How do I use it?

[mcrochet]

Sorry for the delay. Haven't checked the forum in a bit.

I simply put the ASP page (missingweeklypicks.asp) in the root of the website. I then modified the menu.asp under the includes folder adding a new menu option under the "<% if IsAdmin() then %>" section of code. I put it under the menu option to Manage Users. It will then only show up when you login in as the administrator.

<a href="manageUsers.asp">Manage Users</a>
<a href="missingweeklypicks.asp">Missing Weekly Picks</a>

Let me know if this makes sense.

[philwojo]

I just installed this and the only problem I see is that if you put in picks and then clear them out there is still some kind of entry for you in the database and then it isn't showing up on this page. I only did that because it is before week-1 and I had a test user in and a test pick to check on some formatting in the Summary Page and other places. I then removed the pick and all other information for that test user on the picks page and that name doesn't show up when I run this script.

Thanks for putting this together though, seems to work great other than that, which I doubt will be a problem during the year.

Phil

[mcrochet]

Yes, I can see that happening. I would need to see what is left behind in the database if you clear the picks. Maybe just need another check. Thanks for testing this out. It has been working well for our pool.

[philwojo]

Yea, not a big deal and I don't think it will be an issue moving forward, but thought I would point it out in case anyone has people that like to make picks, then remove them and might not make it on the email list is all. Like I said probably won't affect my pool though.

Thanks again for the code, great job!