Combine Tables with Separate Date Range without overlapping data - Access

Question

I wasn't quite sure how to phrase this question as it may not be possible to do in Access (with my limited knowledge and extensive study of Query and Table Relationships). Unfortunately I can't show screenshots because of sensitive data but I will do my best to express it with characters.

I am trying to combine two simple queries that only pull merchants who are out of a certain date range (overdue for certification). Merchants can be overdue for scans and their SAQ. I can easily make two separate queries for each one but I am wondering if its possible to run one query that will include both.

Example of what I have:

Merchant Account | DBA Name | ECM Name | SAQ Due Date

000000000000000....XXXX........... XXXX........... 10/10/2013

Merchant Account | DBA Name | ECM Name | SCAN Due Date

111111111111111111..ZZZZ.............ZZZZ.............1/12/2012

I have accomplished the above by setting Between Date() and Date()-1000 criteria parameters on a Simple Query. My goal is to produce the below;

Merchant Account | DBA Name | ECM Name | SCAN Due Date | SAQ Due Date

111111111111111111..ZZZZ.............ZZZZ.............1/12/2012.............NULL/NO DATA

0000000000000000..XXXX........... XXXX........... NULL/NO DATA.....10/10/2013

Honestly it may just be easier to export both queries and manually copy/paste the column but I wasn't sure if it was possible to accomplish it with less steps! :-]

Goals:

Pull 2 queries from a table that has both Scan and SAQ columns
Pull overdue dates starting from the current date
Refrain from overlapping data, effectively adding information instead of combining

Things that I have tried:

Union Query
Join Query
Experimenting with Parameters

Thank you!

EDIT

 SELECT [Overall Status Report].[Merchant Account Number], [Overall Status Report].[DBA Name], [Overall Status Report].[PCI Contact Name], [Overall Status Report].[PCI Contact Number], [Overall Status Report].[Email address], [Overall Status Report].[ECM Name], [Overall Status Report].[ECM Email], [Overall Status Report].[SAQ Due Date]
FROM [Overall Status Report]
WHERE ((Not ([Overall Status Report].[ECM Name])="ACCOUNT CLOSED") AND (([Overall Status Report].[SAQ Due Date]) Between Date() And Date()-1000))
UNION
SELECT [Overall Status Report].[Merchant Account Number], [Overall Status Report].[DBA Name], [Overall Status Report].[PCI Contact Name], [Overall Status Report].[PCI Contact Number], [Overall Status Report].[Email address], [Overall Status Report].[ECM Name], [Overall Status Report].[ECM Email], [Overall Status Report].[Scan Due Date]
FROM [Overall Status Report]
WHERE ((Not ([Overall Status Report].[ECM Name])="ACCOUNT CLOSED") AND (([Overall Status Report].[Scan Due Date]) Between Date() And Date()-1000));

This works great but it does not separate the SAQ data and the SCAN data into two separate columns, making it difficult to know which one the merchant is overdue on.

EDIT 2:

SELECT [Overall Status Report].[Merchant Account Number], [Overall Status Report].[DBA Name], [Overall Status Report].[PCI Contact Name], [Overall Status Report].[PCI Contact Number], [Overall Status Report].[Email address], [Overall Status Report].[ECM Name], [Overall Status Report].[ECM Email], NULL AS [Overall Status Report].[SAQ Due Date], [Overall Status Report].[Scan Due Date]
FROM [Overall Status Report]
WHERE ((([Overall Status Report].[ECM Name])<>"ACCOUNT CLOSED") AND (([Overall Status Report].[SAQ Due Date]) Between Date() And Date()-1000) AND (([Overall Status Report].[Scan Due Date]) Between Date() And Date()-1000))
UNION
SELECT [Overall Status Report].[Merchant Account Number], [Overall Status Report].[DBA Name], [Overall Status Report].[PCI Contact Name], [Overall Status Report].[PCI Contact Number], [Overall Status Report].[Email address], [Overall Status Report].[ECM Name], [Overall Status Report].[ECM Email], [Overall Status Report].[SAQ Due Date], NULL AS [Overall Status Report].[Scan Due Date]
FROM [Overall Status Report]
WHERE ((([Overall Status Report].[ECM Name])<>"ACCOUNT CLOSED") AND (([Overall Status Report].[SAQ Due Date]) Between Date() And Date()-1000) AND (([Overall Status Report].[Scan Due Date]) Between Date() And Date()-1000));

Please be specific in what you tried (share the SQL). You might have tried a lot, but you might have made errors when trying. — Erik A, Dec 29 '17 at 20:05
I have tried a Union Query but it combines the data under one Column. So lets say 3 Merchants are overdue for SCAN, and 1 for SAQ...access will include all five with the correct dates under one column that might say SCAN OR SAQ...versus having it in two separate columns — Q weet, Dec 29 '17 at 20:05
This is a 'FULL OUTER JOIN' -- a concept that is not immediately supported in access. It is accomplished by finding the UNION of a Left outer join and a right outer join on the same tables. See the existing solution: https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access — Ryan B., Dec 29 '17 at 20:09

score 1 · Answer 1 · answered Dec 29 '17 at 20:26

1

You need to set NULL As column name as:

SELECT [Merchant Account], [DBA Name], [ECM Name], NULL AS [SCAN Due Date], [SAQ Due Date]
FROM Table1
UNION 
SELECT [Merchant Account], [DBA Name], [ECM Name], [SCAN Due Date], NULL AS [SAQ Due Date]
FROM Table2

answered Dec 29 '17 at 20:26

Ilyes

14,640
4
29
55

I attempted to modify my code to add NULL AS but its giving me a SELECT statement includes a reserved word or an argument name that is misspelled or missing error. I will add my code to my post to show what I did. – Q weet Dec 29 '17 at 20:37
Perhaps I need 2 tables? – Q weet Dec 29 '17 at 20:38

Abecee · Answer 2 · 2017-12-29T21:26:20.693

As you tagged MySQL (and I have no MS Access at hand):

SELECT
  OSR.merchantAccountNumber
  , OSR.DBAName
  , OSR.ECMName
  , (SELECT OSR.SAQDueDate
     FROM OverallStatusReport OSR2
     WHERE OSR.merchantAccountNumber = OSR2.merchantAccountNumber
       AND OSR2.SAQDueDate
           BETWEEN DATE_SUB(NOW(), INTERVAL 3000 DAY) AND NOW()) AS SAQDueDate
  , (SELECT OSR.SCANDueDate
     FROM OverallStatusReport OSR3
     WHERE OSR.merchantAccountNumber = OSR3.merchantAccountNumber
       AND OSR3.SCANDueDate
           BETWEEN DATE_SUB(NOW(), INTERVAL 3000 DAY) AND NOW()) AS SCANDueDate
FROM OverallStatusReport OSR
WHERE (NOT OSR.ECMName = 'ACCOUNT CLOSED')
  AND ((SAQDueDate BETWEEN DATE_SUB(NOW(), INTERVAL 3000 DAY) AND NOW())
       OR
       (SCANDueDate BETWEEN DATE_SUB(NOW(), INTERVAL 3000 DAY) AND NOW())
      )
;

It uses two sub-selects to pull in the respective data.

See it in action: SQL Fiddle. (With data adjusted to proof the concept.)

Please comment, if and as this requires adjustment / further detail.

Combine Tables with Separate Date Range without overlapping data - Access

2 Answers2