Show data source in Union Select All Queries

  • Thread starter Thread starter Fluffygoldfish
  • Start date Start date
F

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
 
hi,
Is there a better way to set it up?
Click to expand...
I think it is sufficent for your needs.
Or do I need to make the source information available in the select query?
Click to expand...
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 <--
 
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
'====================================================
 
Back
Top