Time & Attendance Access - Need to summarize data as of a specific date

Anonymous
2022-07-26T17:49:06+00:00

I have a db that maintains all employee's pto, adjustments, etc. in a single table.

Both time already taken and pre-scheduled time are logged in the same column (Date Absent).

The other 2 columns used will be Vac Hrs | Management Adj

I need to create a query/report that prompts for a specific date (dialog box entry) that will then look at 3 columns of data: Date Absent | Vac Hrs | Management Adj and find all transactions through that specific date (Date Absent) and total them (vac hrs, mgmnt adj) for each employee.

I already use the subtotal query to track everything logged but I now need to pull only some data.

Thank you - Leslie

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2022-07-28T00:40:05+00:00

I may be overlooking something, but it seems the SQL order of statements is incorrect. GROUP should be after WHERE

SELECT EmployeeID, Sum(Attendance.VacHrs) AS TotalHrs
FROM Attendance
GROUP BY EmployeeID
WHERE DateAbsent Between Forms!StartEnd!txtStart AND Forms!StartEnd!txtEnd;

Proper order would be

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Was this answer helpful?

2 people found this answer helpful.
0 comments
Answer accepted by question author
ScottGem 68,845 Reputation points Volunteer Moderator
2022-08-05T20:16:41+00:00

What is {Through Date]? is it the date you enter into the form? If so then it should be

>=[Forms]![StartEnd-CONV]![txtEnd]

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
ScottGem 68,845 Reputation points Volunteer Moderator
2022-08-03T17:00:47+00:00

Are you building your queries using Query Design mode or just writing SQL Statements?

If using Design Mode, then you enter < [Forms]![StartEnd-CONV]![txtEnd] in the WHERE row for the date column.

If writing SQL code, you have to include it in the WHERE clause of your query.

Was this answer helpful?

1 person found this answer helpful.
0 comments

38 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. ScottGem 68,845 Reputation points Volunteer Moderator
    2022-07-27T21:52:33+00:00

    Does the form remain open while the query is being run? If so, Try opening the VBE (press Ctrl+G) and in the Immediate window (make sure the form is open and in Form mode) type

    ? Forms!StartEnd!txtStart

    It should return the date entered on the form. Try the same thing with txtEnd.

    Was this answer helpful?

    0 comments
  2. George Hepworth 23,120 Reputation points Volunteer Moderator
    2022-07-27T22:10:41+00:00

    I want to jump back in, even though Scott is carrying on perfectly.

    The controls on the form for "StartDate" and "EndDate" need to be named exactly as the names used in the query.

    For example, your controls are called "txtStart" and "txtEnd", correct?

    And the WHERE clause is

    WHERE DateAbsent Between Forms!StartEnd!txtStart AND Forms!StartEnd!txtEnd;

    If all of that checks out, given that the field in the table is, as shown in the screenshot, DateAbsent, then the query should return any records that qualify.

    What exactly is the error, both number and text description? As is often the case, more than one error could occur in this context, so being specific is very important.

    Also, it wouldn't hurt to confirm that the two controls are designated as Date/Time.

    Also, as Scott mentioned, that the form is open when the query runs and that there are valid dates in both of the contols, txtStart and txtEnd.

    Trouble-shooting is often a tedious process of checking each detail along the way, verifying that our assumptions match the facts in the application exactly.

    And, yes, a new accdb for each year is not a best practice. It prevents you from being able to compare last year's results against this year's results, for example. You have to open both year versions and switch screens back and forth to do that, and that's a PITA. One of the essential advantages of a relational database application is the ability to learn from history.

    Was this answer helpful?

    0 comments