Access Union Query

Question

Hi,

I have multiple queries with Calendar dates and selected field dates and their amounts. Each individual query works.

The Union query brings these multiple fields together. There is no errors, but the datasheet view is only showing Est Deposit Date and SumOfNZD Deposit Amt Due, though it is showing it correctly.

How do I fix the SQL below to show all columns?

Answer 1

Hi Jennet,

Since your current query is using this structure:

DateField, AmountField
UNION ALL
DateField, AmountField
UNION ALL

So Access returns only two first output columns which are Est Deposit Date and SumOfNZD Deposit Amt Due. The rows from Final Due Date, Zenova and below are being placed under those same two columns so you can't see them.

There are two approaches you can try to see which suits your preference::

1/ Use generic column names

SELECT
 [Est Deposit Date] AS [Cashflow Date],
 [SumOfNZD Deposit Amt Due] AS [Amount NZD],
 "Deposit" AS [Cashflow Type]
FROM
 [Cashflow Export File_DepositsPart]
UNION ALL
SELECT
 [Final Due Date],
 [SumOfFinal Amount Due NZD],
 "Final Due" AS [Cashflow Type]
FROM
 [Cashflow Export File_FinalPart]
UNION ALL
SELECT
 [Zenova $10k Credit Due Date],
 [SumOfZenova $10k Credit Bal Due NZD],
 "Zenova 10k Credit" AS [Cashflow Type]
FROM
 [Cashflow Export File_Zenova10kCrPart];

2/ Use all original column names

In this case, every SELECT must return all six columns, using Null placeholders for fields that do not apply

SELECT
 [Est Deposit Date],
 [SumOfNZD Deposit Amt Due],
 Null AS [Final Due Date],
 Null AS [SumOfFinal Amount Due NZD],
 Null AS [Zenova $10k Credit Due Date],
 Null AS [SumOfZenova $10k Credit Bal Due NZD]
FROM
 [Cashflow Export File_DepositsPart]
UNION ALL
SELECT
 Null AS [Est Deposit Date],
 Null AS [SumOfNZD Deposit Amt Due],
 [Final Due Date],
 [SumOfFinal Amount Due NZD],
 Null AS [Zenova $10k Credit Due Date],
 Null AS [SumOfZenova $10k Credit Bal Due NZD]
FROM
 [Cashflow Export File_FinalPart]
UNION ALL
SELECT
 Null AS [Est Deposit Date],
 Null AS [SumOfNZD Deposit Amt Due],
 Null AS [Final Due Date],
 Null AS [SumOfFinal Amount Due NZD],
 [Zenova $10k Credit Due Date],
 [SumOfZenova $10k Credit Bal Due NZD]
FROM
 [Cashflow Export File_Zenova10kCrPart];

This will give you all columns, but each row will only have values in the relevant pair of columns.

You can give it a try and let me know the outcome. If you have any other question, please feel free to reach out on the comments of this post. I'll be happy to assist you further.

Thank you for your patience and understanding. Hope to hear from you soon.

---

If the answer is helpful, please click "Yes" and kindly upvote it.

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.