I’m pulling data from two separate tables (A, B) into another one (C ) in MSAccess using a SQL statement. Appending all the columns from both feeding tables (A , B ) into the destination table (c ). In order to annotate the data with the feeding source, I’m adding a ‘Source’ column to ( c ). The annotation information in this ‘Source’ column does not exist in the feeding tables. I want it to write the ‘Source’ entry from the value given in the SELECT statement. This works correctly if I was loading just one table but it doesn’t working when I use a UNION ALL to load multiple tables at a time. The below code results in a entry prompt for the ‘Source’ value which is applied to all entries. What am I doing wrong?
INSERT INTO Hist_PO (Plant, Material_No, Date_Delivery, Quantity_Ordered, Quantity_Recieved, Source)
SELECT Plant, Material, [Delivery date], [Scheduled Quantity], [Quantity delivered], “”
FROM (
SELECT Plant, Material, [Delivery date], [Scheduled Quantity], [Quantity delivered], "Source_A"
FROM [Table_A]
UNION ALL
SELECT Plant, Material, [Delivery date], "Source_B"
FROM [Table_B]);
Advertisement
Answer
I figured it out… SAP_Instance labeled with [banana] entries.
INSERT INTO [Test-Table] ( Material_No, Date_Delivery, SAP_Instance ) SELECT Material, [Delivery date], [banana] FROM (SELECT Material, [Delivery date], 'SAP_EU' as [banana] FROM [PO-ME80FN-EU] UNION ALL SELECT Material, [Delivery date], 'SAP_NAM' as [banana] FROM [PO-ME80FN-NAM] );