CODEWITHSUNDEEP
sqlms-accessms-access-2007
rodel grancioso
rodel grancioso

Reputation: 33

How can I combine these 2 MS Access queries?

SELECT 
    USERINFO.Name, USERINFO.SSN, USERINFO.Badgenumber, 
    FORMAT(CHECKINOUT.CHECKTIME,"yyyy") AS [YEAR], 
    FORMAT(CHECKINOUT.CHECKTIME,"mmmm") AS [MONTH], 
    FORMAT(CHECKINOUT.CHECKTIME,"dd") AS DAYS, 
    FORMAT((Min(CHECKINOUT.CHECKTIME)),"hh:mm am/pm") AS TIMEIN, 
    FORMAT(CHECKINOUT.CHECKTIME,"AMPM") AS [AM/PM], CHECKINOUT.CHECKTYPE
FROM 
    CHECKINOUT 
INNER JOIN 
    USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
WHERE 
    (CHECKINOUT.CHECKTIME >= DATEVALUE(PERIOD_START) AND
     CHECKINOUT.CHECKTIME < DATEADD("d", 1, PERIOD_END) AND
    USERINFO.Name = Insert_Name) AND 
    CHECKINOUT.CHECKTYPE = "I"
GROUP BY 
    CHECKINOUT.USERID, USERINFO.Name, USERINFO.SSN, 
    DateValue(CHECKINOUT.CHECKTIME), FORMAT(CHECKINOUT.CHECKTIME,"AMPM"), 
    USERINFO.Badgenumber, FORMAT(CHECKINOUT.CHECKTIME,"mmmm"), 
    FORMAT(CHECKINOUT.CHECKTIME,"yyyy"), FORMAT(CHECKINOUT.CHECKTIME,"dd"), 
    CHECKINOUT.CHECKTYPE
HAVING 
    (((USERINFO.Badgenumber) < "8000"))
ORDER BY 
    USERINFO.Name, Min(CHECKINOUT.CHECKTIME);

This query is for timein

SELECT 
    USERINFO.Name, USERINFO.SSN, USERINFO.Badgenumber, 
    FORMAT(CHECKINOUT.CHECKTIME,"yyyy") AS [YEAR], 
    FORMAT(CHECKINOUT.CHECKTIME,"mmmm") AS [MONTH], 
    FORMAT(CHECKINOUT.CHECKTIME,"dd") AS DAYS, 
    FORMAT((Max(CHECKINOUT.CHECKTIME)),"hh:mm am/pm") AS TIMEOUT, 
    FORMAT(CHECKINOUT.CHECKTIME,"AMPM") AS [AM/PM], 
    CHECKINOUT.CHECKTYPE
FROM
    CHECKINOUT 
INNER JOIN 
    USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
WHERE 
    (CHECKINOUT.CHECKTIME >= DATEVALUE(PERIOD_START) AND
     CHECKINOUT.CHECKTIME < DATEADD("d", 1, PERIOD_END) AND
    USERINFO.Name = Insert_Name) AND
    CHECKINOUT.CHECKTYPE = "O"
GROUP BY 
    CHECKINOUT.USERID, USERINFO.Name, USERINFO.SSN, 
    DateValue(CHECKINOUT.CHECKTIME), FORMAT(CHECKINOUT.CHECKTIME,"AMPM"), 
    USERINFO.Badgenumber, FORMAT(CHECKINOUT.CHECKTIME,"mmmm"), 
    FORMAT(CHECKINOUT.CHECKTIME,"yyyy"), FORMAT(CHECKINOUT.CHECKTIME,"dd"), 
    CHECKINOUT.CHECKTYPE
HAVING 
    (((USERINFO.Badgenumber) < "8000"))
ORDER BY 
    USERINFO.Name, Min(CHECKINOUT.CHECKTIME);

and this query is for timeout,

How can I combine this 2 queries together?

Upvotes: 2

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

These queries appear to be identical except for a single condition in the respective WHERE clauses. The first query has this:

CHECKINOUT.CHECKTYPE = "I"

while the second one has this

CHECKINOUT.CHECKTYPE = "O"

I think you can combine these two conditions as follows:

WHERE ... AND CHECKINOUT.CHECKTYPE IN ("I", "O")

Full query:

SELECT USERINFO.Name,
       USERINFO.SSN,
       USERINFO.Badgenumber,
       FORMAT(CHECKINOUT.CHECKTIME,"yyyy") AS [YEAR],
       FORMAT(CHECKINOUT.CHECKTIME,"mmmm") AS [MONTH],
       FORMAT(CHECKINOUT.CHECKTIME,"dd") AS DAYS,
       FORMAT((MIN(CHECKINOUT.CHECKTIME)), "hh:mm am/pm") AS TIMEIN,
       FORMAT(CHECKINOUT.CHECKTIME,"AMPM") AS [AM/PM],
       CHECKINOUT.CHECKTYPE
FROM CHECKINOUT
INNER JOIN USERINFO
    ON CHECKINOUT.USERID = USERINFO.USERID
WHERE CHECKINOUT.CHECKTIME >= DATEVALUE(PERIOD_START) AND
      CHECKINOUT.CHECKTIME < DATEADD("d", 1, PERIOD_END) AND
      USERINFO.Name = Insert_Name AND
      CHECKINOUT.CHECKTYPE IN ("I", "O")     -- here is the change
GROUP BY CHECKINOUT.USERID,
         USERINFO.Name,
         USERINFO.SSN,
         DateValue(CHECKINOUT.CHECKTIME),
         FORMAT(CHECKINOUT.CHECKTIME,"AMPM"),
         USERINFO.Badgenumber,
         FORMAT(CHECKINOUT.CHECKTIME,"mmmm"),
         FORMAT(CHECKINOUT.CHECKTIME,"yyyy"),
         FORMAT(CHECKINOUT.CHECKTIME,"dd"),
         CHECKINOUT.CHECKTYPE
HAVING USERINFO.Badgenumber < "8000"
ORDER BY USERINFO.Name,
         MIN(CHECKINOUT.CHECKTIME);

For information purposes, had the logic of the two queries differed greatly, but both queries had the same structure in the SELECT clause, we could have peformed a UNION of the two queries.

Upvotes: 1

Related Questions