Create Query to run automatically and email

[murleyj]

I am trying to create a query to run automatically every Sunday morning and email results. It is an attendance report of employees hours for the previous week for payroll purposes.



The manufacturing software we use is called E2 and is Access based. I have developed many queries that I use to gather information for various other things.

But this one is tough. I am novice at best.

I am attaching the two tables [Attend] and [AttendDet]....I believe it can be done with just [AttendDet].

There is a field named [TicketDate] which is populated by the software timeclock with a 6 digit date like this 06/17/15. But it is a Short Text field.

I need my query to pull all employees attendance from the previous week (Sunday through Saturday) and give me their total hours.

Any help is appreciated.

I have attached the tables and my preliminary query (which is nowhere near done) in a compressed folder.

Thank you!


Database1.zip

[June7]

One step at a time. You need to:

1. get the query correct - what you have really does nothing
Probably needs to be an aggregate (GROUP BY) Totals query that uses DateDiff() function to calculate elapsed time. However, since the date and time parts are in separate fields and the fields are text instead of date/time type, gets complicated. Should save date and time as a single value into date/time type field.

2. figure out code for email process - DoCmd.SendObject is simplest method
Search forum for more, here is one https://www.accessforums.net/program...ook-21903.html

3. figure out code to automate with Windows Task Manager
https://www.accessforums.net/program...ing-21555.html

[murleyj]

Thank you June

I don't have the option of changing the data field type. I am linked to these tables because they are in an access database designed by another company as an underlying part of their software. So the database design and structure are not modifiable.

I was wondering if a query could take the values in the TicketDate field, convert them to correct format and use those values in a new field in a new table.

But I am not sure. I do understand the query has to be built right first and then the automation secondary.

[June7]

Yes, just what I would suggest. Build query that combines the date and time parts and does elapsed time calcs. Use that query as the data source for subsequent queries and reports.

[murleyj]

Yes, just what I would suggest. Build query that combines the date and time parts and does elapsed time calcs. Use that query as the data source for subsequent queries and reports.

Well not exactly. The table already has an elapsed or total time for each day. What I need is a query that will look for all records in the previous week.

And then, for each each [EmplCode] record for the same employee for that week, (for example if there are 5 records for employee # 2 in the[EmplCode] field for that week, I would need a total of those 5 records. There is a field named [TotAdjTim] for each record which shows how many hours in a day the employee worked that he should be paid for. So if he was there 8 hours on 6/15/15 it would show 8 in that record.

So what I need is for a query to be able to tell there are 5 records for the previous week for employee 2 in the [EmplCode] field, and each of those 5 records have an 8 in the [TotAdjTime] field so that employee worked total of 40 hrs that week.

[June7]

Select records by applying filter criteria. Sum records with aggregate functions. I don't have your db now but would be something like:

SELECT EmployeeID, Sum(ElapsedTime) AS TotTime FROM table WHERE Datefield BETWEEN [Enter Start Date] AND [Enter End Date] GROUP BY EmployeeID;

Use the query builder to create an aggregate (GROUP BY) Totals query or build a report and use its Grouping & Sorting features with aggregate calcs in group and report footer sections.

[Micron]

[I am trying to create a query to run automatically every Sunday morning]

If by automatically you mean unattended, I don't think the SendObject method will work because you won't be there to deal with the email security prompt. If I'm correct, you will need to use the COM object for email.