Search code examples
sqlms-access

Microsoft Access Query exclude results

I need a bit of help with sql query for MS Access and not sure if it is possible.

We have a database for our clock machine, MySql which I have linked to access in order to pull the clockins from the database and be able to generate a timesheet.

The relevant tables that I am Using Are:

  • Table: Checkinout - Fields: ID, Checktime, Checktype (In or Out) and userid
  • Table: Userinfo - Fields: userid, name, company_id and badgenumber
  • Table: Company - Fields: company_id, company

I have a report form that is used to select the company, reporting period (today, yesterday, custom, etc) and from above selection a timesheet is generated for the specified company which is working nicely.

What I am struggling with is filtering out all clockout (checktype) before a specified time on start time and filtering out all clockins on last day specified. But in between the start and end date checkin and checkouts but me shown as per attached image. To be Filtered out.

My current query that is not working that i have placed in qrycheckin:

SELECT *
FROM checkinLocal AS CHECK1
WHERE (((CHECK1.checktime) Between [Forms]![TimeSheet Report]![txtStartDate] 
      And [Forms]![TimeSheet Report]![txtEndDate]) 
      AND ((Exists (
            Select DISTINCT * FROM checkinlocal as CHECK2 
            WHERE CHECK1.id = CHECK2.id 
            AND checktype="0" AND (checktime=[Forms]![TimeSheet Report]![txtEndDate])))=False));

I have not begun attempting with filtering on start date, only end date.

Not sure if this is all info required. But any assistance with above request will be much appreciated.

Original Result

Company Number name Date Time CheckType
1 Peter Pan 04/08/2021 5:59 1
1 Peter Pan 04/08/2021 17:56 0
1 Peter Pan 04/09/2021 06:01 1
1 Peter Pan 04/09/2021 18:05 0
1 Peter Pan 04/10/2021 05:59 1
1 Peter Pan 04/12/2021 06:02 0
1 Peter Pan 04/12/2021 17:57 1
1 Peter Pan 04/13/2021 06:05 0
1 Peter Pan 04/13/2021 18:00 1
1 Peter Pan 04/14/2021 05:58 0

Required Result

Company Number name Date Time CheckType
1 Peter Pan 04/08/2021 17:56 0
1 Peter Pan 04/09/2021 06:01 1
1 Peter Pan 04/09/2021 18:05 0
1 Peter Pan 04/10/2021 05:59 1
1 Peter Pan 04/12/2021 06:02 0
1 Peter Pan 04/12/2021 17:57 1
1 Peter Pan 04/13/2021 06:05 0
1 Peter Pan 04/13/2021 18:00 1

I got the above sql code from sqltest.net after playing around there: SqlScript:

CREATE TABLE sql_server_test_a 
( 
    ID         NVARCHAR(4000), 
    FIRST_NAME NVARCHAR(200), 
    LAST_NAME  NVARCHAR(200),
    checktime  DATE,
    checktype  NVARCHAR(4) 
); 

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('1', 'Paris', 'Hilton', '03/01/2021', "1");

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('1', 'Paris', 'Hilton', '03/01/2021', "0"); 

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('2', 'Nicky', 'Hilton', '03/06/2021', "1");

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('3', 'Peter', 'Hilton', '03/08/2021', "0");

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('4', 'Roger', 'Hilton', '03/08/2021', "1");

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME, checktime, checktype) VALUES ('5', 'Holly', 'Hilton', '03/04/2021', "0");

and the working sql Query:

SELECT * 
  FROM sql_server_test_a AS Test1
  WHERE checktime Between "03/01/2021" and "03/08/2021"
  AND NOT EXISTS (SELECT * FROM sql_server_test_a AS Test2
        WHERE Test1.ID = Test2.ID 
        and (checktype = "0" and checktime = "03/08/2021"));

But above code does not work in access after replacing the dates with the forms texts boxes. Regards

Solution

  • @June7 thank you for your assistance, with a bit of tinkering of your code that you provided I got it working.

    Final working code was:

    SELECT *
FROM checkinLocal AS A
WHERE [checktime] Between [Forms]![TimeSheet Report]![txtStartDate] 
   And [Forms]![TimeSheet Report]![txtEndDate] + 1 AND [ID] NOT IN (
      SELECT ID FROM checkinLocal  as B WHERE A.[ID] = B.[ID] AND (checktype="0" And checktime >= [Forms]![TimeSheet Report]![txtEndDate] & ' ' & '17:00:00'));

    Your assistance was much appreciated.