Add Fake Records to Query from Table

[Anthony]

I have a table that contains real data and then I have a table that has dummy
data that is needed for another program that I'm sending the file too. I
need to figure how to put them in the same colunm in the table. Here is my
statement.
SELECT [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS]![itemId] AS [Station
Name & Item], DBA_ITEMS.itemName AS [Item Desc], DBA_POCKET.pktParQty AS Par,
DBA_POCKET.pktRefillPoint AS Refill
FROM [DocFinity Fake Records], (DBA_ITEMS INNER JOIN (DBA_POCKET INNER JOIN
DBA_DEVICES ON DBA_POCKET.deviceIid = DBA_DEVICES.deviceIid) ON
DBA_ITEMS.itemIid = DBA_POCKET.itemIid) INNER JOIN [item class crosswalk] ON
DBA_ITEMS.itemClass = [item class crosswalk].[Table Class];

I know I have to use the UNION statement, but have not been able to get it
right.

Below is what I'm trying to accomplish. If anyone could help in any way.
Station Name & Item Item Desc Par
Refill
SS-MICU 06022 - Real Data IV SET 5
3
SS-MICU 0000 - Fake Data N/A

 

[Daryl S]

Anthony -

A UNION query will join the results of two separate queries into one as long
as they contain the exact same field list. So you create your two queries so
that the resulting columns have the same names and data types. Then you do a
UNION ALL.

Assuming your fake data is in DBA_ITEMS_FAKE, and that the rest of the
joined tables are real, the UNION ALL would be as below. Notice that the
part above the UNION ALL is exactly what you had (except the semi colon at
the end is removed). The second part below the UNION ALL looks exactly the
same except that I have changed the DBA_ITEMS table name to DBA_ITEMS_FAKE in
all cases.

SELECT [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS]![itemId] AS [Station
Name & Item], DBA_ITEMS.itemName AS [Item Desc], DBA_POCKET.pktParQty AS Par,
DBA_POCKET.pktRefillPoint AS Refill
FROM [DocFinity Fake Records], (DBA_ITEMS INNER JOIN (DBA_POCKET INNER JOIN
DBA_DEVICES ON DBA_POCKET.deviceIid = DBA_DEVICES.deviceIid) ON
DBA_ITEMS.itemIid = DBA_POCKET.itemIid) INNER JOIN [item class crosswalk] ON
DBA_ITEMS.itemClass = [item class crosswalk].[Table Class]
UNION ALL
SELECT [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS_FAKE]![itemId] AS
[Station
Name & Item], DBA_ITEMS_FAKE.itemName AS [Item Desc], DBA_POCKET.pktParQty
AS Par,
DBA_POCKET.pktRefillPoint AS Refill
FROM [DocFinity Fake Records], (DBA_ITEMS_FAKE INNER JOIN (DBA_POCKET INNER
JOIN
DBA_DEVICES ON DBA_POCKET.deviceIid = DBA_DEVICES.deviceIid) ON
DBA_ITEMS_FAKE.itemIid = DBA_POCKET.itemIid) INNER JOIN [item class
crosswalk] ON
DBA_ITEMS_FAKE.itemClass = [item class crosswalk].[Table Class];

The UNION ALL syntax is faster than the UNION syntax, and can be used when
none of the records from the first SELECT will also be in the second SELECT.

--
Daryl S

I have a table that contains real data and then I have a table that has dummy
data that is needed for another program that I'm sending the file too. I
need to figure how to put them in the same colunm in the table. Here is my
statement.
SELECT [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS]![itemId] AS [Station
Name & Item], DBA_ITEMS.itemName AS [Item Desc], DBA_POCKET.pktParQty AS Par,
DBA_POCKET.pktRefillPoint AS Refill
FROM [DocFinity Fake Records], (DBA_ITEMS INNER JOIN (DBA_POCKET INNER JOIN
DBA_DEVICES ON DBA_POCKET.deviceIid = DBA_DEVICES.deviceIid) ON
DBA_ITEMS.itemIid = DBA_POCKET.itemIid) INNER JOIN [item class crosswalk] ON
DBA_ITEMS.itemClass = [item class crosswalk].[Table Class];

I know I have to use the UNION statement, but have not been able to get it
right.

Below is what I'm trying to accomplish. If anyone could help in any way.
Station Name & Item Item Desc Par
Refill
SS-MICU 06022 - Real Data IV SET 5
3
SS-MICU 0000 - Fake Data N/A

 

[John W. Vinson]

A UNION query will join the results of two separate queries into one as long
as they contain the exact same field list. So you create your two queries so
that the resulting columns have the same names and data types.

One small correction: the number of fields and their datatypes must agree, but
the fieldnames are not required to be the same. A UNION query like

SELECT LastName, FirstName, HireDate, Salary
FROM Employees
UNION ALL
SELECT Surname, Forename, ContractStartDate, Rate
FROM Contractors

will work fine if the four fields are Text, Text, Date/Time and Currency
respectively.