Access Query: Values disappear when I name/alias my expression the same as the inherited field

Question

I have a query that's pulling data from another. Here's my SELECT statement in SQL:

SELECT
    [Orders].Date,
    [Orders].[Part Number],
    [Orders].Region

Using this, my column headers read as follows:

  Date  ||  [Orders].[Part Number]  ||     Region
  12/1  ||         AAA001           ||  North America
  12/1  ||         BBB002           ||  North America
  12/1  ||         CCC003           ||  North America

Note the fact that Date and Region are labelled as one word, while Part Number has the Form/Field format. To try and avoid this, I add an alias.

SELECT
    [Orders].Date,
    [Orders].[Part Number] AS [Part Number],
    [Orders].Region

When I do this my header appears normally, but all of my values disappear:

  Date  ||  Part Number  ||     Region
  12/1  ||               ||  North America
  12/1  ||               ||  North America
  12/1  ||               ||  North America

If I name it anything else (even if I just add an "s" at the end to make "Part Numbers"), the values re-appear.

  Date  ||  Part Numbers  ||     Region
  12/1  ||     AAA001     ||  North America
  12/1  ||     BBB002     ||  North America
  12/1  ||     CCC003     ||  North America

---

Anyone know where I can start looking to try and fix this?

Edit Here's my full query. I guess here I should note that the first three fields are pulled from a Union query, if that matters. Names were changed for simplicity above, but now they're their actual names. The problem I'm having with is [uQuery Forecast/Orders].[Part Number].

SELECT 
    [uQuery Forecast/Orders].Date, 
    [uQuery Forecast/Orders].[Part Number], 
    [uQuery Forecast/Orders].Region, 
    Val(IIf(IsNull([Orders].[QTY]),0,[Orders].[QTY])) AS [Order QTY], 
    Val(IIf(IsNull([Forecast].[QTY]),0,[Forecast].[QTY])) AS [Forecast QTY], 
    Val(IIf(IsNull([Builds].[QTY]),0,[Builds].[QTY])) AS [Build QTY]
FROM (([uQuery Forecast/Orders] 
    LEFT JOIN [Orders] ON 
        ([uQuery Forecast/Orders].[Part Number] = [Orders].[Part Number]) AND
        ([uQuery Forecast/Orders].[Date] = [Orders].[Date])) 
    LEFT JOIN [Forecast] ON 
        ([uQuery Forecast/Orders].[Part Number] = [Forecast].[Part Number]) AND
        ([uQuery Forecast/Orders].[Date] = [Forecast].[Date])) 
    LEFT JOIN [Builds] ON 
        ([uQuery Forecast/Orders].Date = [Builds].[Date]) AND
        ([uQuery Forecast/Orders].[Part Number] = [Builds].[Part Number])
GROUP BY 
    [uQuery Forecast/Orders].Date, 
    [uQuery Forecast/Orders].[Part Number], 
    [uQuery Forecast/Orders].Region, 
    Val(IIf(IsNull([Orders].[QTY]),0,[Orders].[QTY])), 
    Val(IIf(IsNull([Forecast].[QTY]),0,[Forecast].[QTY])), 
    Val(IIf(IsNull([Builds].[QTY]),0,[Builds].[QTY]));

---

EDIT 2

Here's my Union Query. It's only purpose is to list any and all unique combinations of Date ↔ Part Number ↔ Region. I only have 3 regions, and ~1000 part numbers. The date starts at 9/1/17, so even after 20 years there's only be 7305 entries. All together, that's a maximum of 22,000,000 rows.

SELECT 
    [Orders].[Date] AS [Date], 
    [Orders].[Part Number] AS [Part Number], 
    [Orders].[Region] AS [Region]
FROM [Orders]
UNION SELECT 
    [Forecast].[Date], 
    [Forecast].[Part Number], 
    [Forecast].[Region]
FROM [Forecast]
UNION SELECT 
    [Builds].[Date], 
    [Builds].[Part Number], 
    [Builds].[Region]
FROM [Builds];

Answer 1

First, I'd recommend running a Compact/Repair operation on your database and validate integrity of your data. In my experience, a corrupt database may behave unexpectedly.

Secondly, Modify your UNION query to remove the AS statements from the first SELECT:

SELECT [Date], [Part Number], [Region] FROM Builds
UNION SELECT [Date], [Part Number], [Region] FROM Forecast
UNION SELECT [Date], [Part Number], [Region] FROM Orders;

I still believe you'll want only the DISTINCT results of your union query, as in this example:

SELECT u.DATE
    ,u.[Part Number]
    ,u.Region
    ,Val(IIf(IsNull(o.[QTY]), 0, o.[QTY])) AS [Order QTY]
    ,Val(IIf(IsNull(f.[QTY]), 0, f.[QTY])) AS [Forecast QTY]
    ,Val(IIf(IsNull(b.[QTY]), 0, b.[QTY])) AS [Build QTY]
FROM (
    (
        (SELECT DISTINCT [Date], [Part Number], [Region] from [uQuery Forecast/Orders]) AS u 
        LEFT JOIN Orders AS o ON (u.[Date] = o.[Date])
            AND (u.[Part Number] = o.[Part Number])
        ) LEFT JOIN Forecast AS f ON (u.[Date] = f.[Date])
        AND (u.[Part Number] = f.[Part Number])
    )
LEFT JOIN Builds AS b ON (u.[Date] = b.[Date])
    AND (u.[Part Number] = b.[Part Number]);

In order to display the result of each QTY on the same row, you will need the DISTINCT keyword in your union query to prevent multiple rows for the same Product Number/Date pair.

Answer 2

Your SQL maintains three underlying tables each with Part Number fields: Orders, Forecasts, and Builds. Consequently, in outer query there is a named clash and Access shows the table name qualified by period in query's column name. Same issue would appear for Date and QTY had you included them in outer query's SELECT.

To resolve consider using column aliases in underlying tables to change original Part Numbers and use those names in ON clauses. See adjusted query using derived tables and also uses table aliases. You can also save those nested SELECT as their own queries and referenced them here.

SELECT 
    u.Date, 
    u.[Part Number], 
    u.Region, 
    Val(IIf(IsNull(o.[QTY]), 0, o.[QTY])) AS [Order QTY], 
    Val(IIf(IsNull(f.[QTY]), 0, f.[QTY])) AS [Forecast QTY], 
    Val(IIf(IsNull(b.[QTY]), 0, b.[QTY])) AS [Build QTY]
FROM (([uQuery Forecast/Orders] u
    LEFT JOIN (SELECT [Part Number] As [O_Part_Number], [QTY], [Date] FROM [Orders]) o 
      ON  (u.[Part Number] = o.[O_Part Number]) 
      AND (u.[Date] = o.[Date])) 
    LEFT JOIN (SELECT [Part Number] As [F_Part_Number], [QTY], [Date] FROM [Forecast]) f
      ON  (u.[Part Number] = f.[F_Part_Number])
      AND (u.[Date] = f.[Date])) 
    LEFT JOIN (SELECT [Part Number] As [B_Part_Number], [QTY], [Date] FROM [Builds]) b 
      ON  (u.[Part Number] = b.[B_Part_Number])
      AND (u.[Date] = b.[Date]) 
GROUP BY 
    u.Date, 
    u.[Part Number], 
    u.Region, 
    Val(IIf(IsNull(o.[QTY]), 0, o.[QTY])), 
    Val(IIf(IsNull(f.[QTY]), 0, f.[QTY])), 
    Val(IIf(IsNull(b.[QTY]), 0, b.[QTY]));