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: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,845 Reputation points Volunteer Moderator
    2022-08-05T19:48:39+00:00

    OK, so Date is a reserved word in Access and should NOT be used as an object name. the section of WHERE Clause:

    WHERE ((([Specific Date-CONV Query].[Separation Date])>=[Date]

    indicates that you are comparing the Separation Date to the current date. But by enclosing it in Brackets you are telling Access its an object name. What it should be is

    WHERE ((([Specific Date-CONV Query].[Separation Date])>=Date()

    That tells Access to compare it to the Date function that returns today's date.

    For the second prompt do you have a field named txtEnd in the Start-End Comv Query? or are you referencing the control on the form?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-08-05T19:29:32+00:00

    I get a dialog box prompting for a date:

    I am wondering if it has to do with WHERE below:

    Final Date Query
    SELECT [StartEnd-CONV Query].[Employee File Number and Name], [StartEnd-CONV Query].PTOTaken, [StartEnd-CONV Query].Adjustments, [Specific Date-CONV Query].[Company Code], [Specific Date-CONV Query].[BBF Accrued Hrs], [Specific Date-CONV Query].[Separation Date], [Specific Date-CONV Query].EarnedHrsAvail, [Specific Date-CONV Query].[Rehire Date]
    FROM [StartEnd-CONV Query] INNER JOIN [Specific Date-CONV Query] ON [StartEnd-CONV Query].[Employee File Number and Name] = [Specific Date-CONV Query].[Employee File Number]
    WHERE ((([Specific Date-CONV Query].[Separation Date])>=[Date] Or ([Specific Date-CONV Query].[Separation Date]) Is Null)) Or ((([Specific Date-CONV Query].[Rehire Date])>=[Separation Date]) And ((Forms![StartEnd-CONV]!txtEnd)<>False))
    ORDER BY [StartEnd-CONV Query].[Employee File Number and Name];

    But when I tried this:

    WHERE ((([Specific Date-CONV Query].[Separation Date])>=[StartEnd-CONV Query].[txtEnd] Or ([Specific Date-CONV Query].[Separation Date]) Is Null)) Or ((([Specific Date-CONV Query].[Rehire Date])>=[Separation Date]) And ((Forms![StartEnd-CONV]!txtEnd)<>False)).

    it ends up with a prompt for a Date and a 2nd prompt for [StartEnd-CONV Query].[txtEnd]

    So now I am unsure where the issue resides.

    Was this answer helpful?

    0 comments