Show data source in Union Select All Queries

[Fluffygoldfish]

I have set up a Union Select Query, from several different queries and it
returns the results I want (see below). However I would like to add an
additional column that shows which query the data came from, as my manager
wants to see who is out of normal work and why (i.e. for leave, lieu,
training or absence) at the click of a button.

SELECT [Name], [Surname], [Start Date], [End Date]
FROM [AL between dates]

UNION ALL SELECT [Name], [Surname], [Lieu From], [Lieu To]
FROM [Lieu between dates];

UNION ALL SELECT [Name], [Surname], [First day of absence], [Last day of
absence]
FROM [Absent];

UNION ALL SELECT [Name], [Surname], [Start Date], [End Date]
FROM [Training between dates];


Is there a better way to set it up? Or do I need to make the source
information available in the select query? If so how?

Thanks for any help x

 

[Stefan Hoffmann]

hi,

Is there a better way to set it up?

I think it is sufficent for your needs.

Or do I need to make the source information available in the select query?

Yes, e.g.

SELECT "AL between dates" AS Source,
[Name], [Surname], [Start Date], [End Date]
FROM [AL between dates]

UNION ALL

SELECT "Lieu between dates",
[Name], [Surname], [Lieu From], [Lieu To]
FROM [Lieu between dates]

....

btw, you have to remove the semi-colons.


mfG
--> stefan <--

 

[John Spencer]

SELECT "AL" as Source, [Name], [Surname], [Start Date], [End Date]
FROM [AL between dates]

UNION ALL
SELECT "Lieu" as Source, [Name], [Surname], [Lieu From], [Lieu To]
FROM [Lieu between dates]

UNION ALL
SELECT "Absent" as Source, [Name], [Surname], [First day of absence],
[Last day of absence]
FROM [Absent]

UNION ALL
SELECT "Training" as Source, [Name], [Surname], [Start Date]
, [End Date]
FROM [Training between dates];

A better way MIGHT be to have one table with an additional field to
indicate the "source". It depends on what your tables are doing and
what the differences are between your current tables.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================