How to write SQL Server 2008 equivalent query in Access 2010 SQL

Question

I want to get the same output as on the following link but I want it to be done in Access SQL which would be called from Access VBA. I cannot use it directly as that approach uses User Defined Functions and outer apply which are only available in SQL Server and not in Access SQL. How to write this as I have no idea how to write it in Access SQL.

Answer 1

Functions in Access SQL: You define functions in a VBA module. Example:

Public Function MyFirstQueryFunction(vFieldValue) As Variant 
    ' do something useful here 
    MyFirstQueryFunction = vFieldValue 
End Function

Use in query:

SELECT MyFirstQueryFunction([SomeField]) AS [MyCalcValue] FROM [SomeTable];

Outer Joins in Access: This requires a temp table. A general description:

1. Create a temp table to handle your outer join from row sets A & B. Include the key fields shared by A & B in your table. Fields unique to A or B must be nullable.

2. Insert your selected rows from A into temp

3. Update temp with all rows from B that match the keys from A (INNER JOIN)

4. Insert those rows from B that do not match any keys in A (LEFT JOIN)

This will produce a set of: all A & B with matching rows, all A with no B, and all B with no A.