Show data source in Union Select All Queries

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
 
S

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 <--
 
J

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
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top