MS SQL Excel of Excel Files, Case Statements or Alternative

I have an excel file I'm using to process data. It comes in via a .csv file and I wanted to output quite a few different sheets each having processed the data in different ways. Once the initial setup is complete, the person executing this task will not be me. After exploring many methods of doing this, (setting up an SQL or Access database for example/using excel functions that read and processed the data) I settled on an Excel file querying itself after importing the .csv.

I found out how using the following method: How to run a SQL query on an Excel table?

The dialect of SQL was unfamiliar to me, and the only reference I could find was: https://support.microsoft.com/en-us/help/136699/description-of-the-usage-of-joins-in-microsoft-query Which works in most cases. In some instances, Jet SQL works but other times it returns errors.

I have three columns in the table, Cust Status, 'MinDateFiledBorrower' & 'MinDateFiledCoBorrower'. Either or both of the Date fields may have data in them, or either may be NULL. I am attempting to get the Minimum date of both the Borrower and the CoBorrower. I can't seem to find syntax for Case Statements in JetSQL, and the errors I'm receiving on my attempts lead me to suspect they aren't supported.

The data:

Cust Status    'MinDateFiledBorrower'    'MinDateFiledCoBorrower'
B-1001         9/15/2004 0:00            11/1/2006 0:00
B-1002         9/17/2004 0:00            11/9/2006 0:00
B-1003         10/7/2004 0:00            NULL 
B-1004         NULL                      10/14/2004 0:00    
B-1005         9/23/2004 0:00            12/21/2006 0:00
B-1007         10/19/2004 0:00           2/12/2007 0:00
B-1008         10/22/2004 0:00           2/8/2007 0:00
B-101          NULL                      12/11/2001 0:00    
B-1010         10/25/2004 0:00           NULL
B-1011         10/28/2004 0:00           6/8/2007 0:00
B-1012         11/4/2004 0:00            6/28/2007 0:00
B-2298         5/12/2005 0:00            NULL
B-23           NULL                      2/26/2007 0:00
B-230          NULL                      5/15/2012 0:00
B-2300         NULL                      5/24/2005 0:00
B-2301         NULL                      6/30/2005 0:00

My Most recent attempt:

select
    `DischargeCombine$`.`Cust Status`
    ,`DischargeCombine$`.`'MinDateFiledBorrower'`
    ,`DischargeCombine$`.`'MinDateFiledCoBorrower'`
    ,case when `DischargeCombine$`.`'MinDateFiledBorrower'` is null then `DischargeCombine$`.`'MinDateFiledCoBorrower'`
    when `DischargeCombine$`.`'MinDateFiledCoBorrower'` is null then `DischargeCombine$`.`'MinDateFiledBorrower'`
    when `DischargeCombine$`.`'MinDateFiledBorrower'`>=`DischargeCombine$`.`'MinDateFiledCoBorrower'` then `DischargeCombine$`.`'MinDateFiledCoBorrower'`
    when `DischargeCombine$`.`'MinDateFiledBorrower'`<`DischargeCombine$`.`'MinDateFiledCoBorrower'` then `DischargeCombine$`.`'MinDateFiledBorrower'` end as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$` `DischargeCombine$`

Returns the error "Didn't expect 'DischargeCombine$' after the SELECT column list." It doesn't have line markers, but I'm guessing it means the first DischargeCombine$ of the Case Statement.

Any assistance will be greatly appreciated!

Solution

MS Access Jet/ACE SQL Engine does not support the ANSI-92 CASE statement of which I raised a ticket among other missing ANSI methods to the dialect.

Instead of CASE, consider a nested IIF function for conditional logic. Below adjustment also uses a short table alias to avoid the long, repeating identifier:

select
     d.`Cust Status`
    , d.`'MinDateFiledBorrower'`
    , d.`'MinDateFiledCoBorrower'`
    , iif(d.`'MinDateFiledBorrower'` is null, d.`'MinDateFiledCoBorrower'`,
          iif(d.`'MinDateFiledCoBorrower'` is null, d.`'MinDateFiledBorrower'`,
              iif( d.`'MinDateFiledBorrower'` >= d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledCoBorrower'`, 
                  iif(d.`'MinDateFiledBorrower'` < d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledBorrower'`, null
                     )
                 )
             )
        ) as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$`  d

Alternatively, consider SWITCH (an Access SQL specific method borrowed from VBA):

select
     d.`Cust Status`
    , d.`'MinDateFiledBorrower'`
    , d.`'MinDateFiledCoBorrower'`
    , switch(d.`'MinDateFiledBorrower'` is null, d.`'MinDateFiledCoBorrower'`,
             d.`'MinDateFiledCoBorrower'` is null, d.`'MinDateFiledBorrower'`,
             d.`'MinDateFiledBorrower'` >= d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledCoBorrower'`, 
             d.`'MinDateFiledBorrower'` < d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledBorrower'`
            ) as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$`  d

As an aside, MS Access is actually a GUI to the Jet/ACE engine (.dll files) which is what you use to query Excel workbooks and can also query native Jet/ACE tables, and even query CSV files. In fact, one does not need the MSAccess.exe program installed to query .mdb or .accdb and likewise .xls, .xlsx, .xlsm, .xlsb files.