My Union Query returns the correct results. But how do I distinguish or label the two rows? So the user knows which is what. See the red arrows. (new to Access)
Cheers,
Julian
5 3997
Im not 100% sure what your asking for, even with your screenshot. Im guessing that you get 2 identical rows from 2 different tables, and you want to know which table that row came from?
If you include in your query syntax something like this: - SELECT *,'Table 1' as Origin From Table1
-
UNION
-
Select *,'"Table 2' as Origin From Table 2
Im quite fuzzy on Union query's so if the syntax is off, I apologize. However the Idea is to create an extra text field, that says where the row came from.
Hello TheSmileyOne, you are correct, the data comes from two different tables and I need to identify from which table. Will give your suggestion a try.
Thanks for your speedy answer.
Julian.
NeoPa 32,556
Recognized Expert Moderator MVP
You will find Smiley's suggestion will work perfectly for you. The only bit that is unnecessary (not wrong) is the "as Origin" part of line #3 as field names are always determined exclusively from the original SELECT clause in a UNION query.
Welcome to Bytes!
Hello NeoPa, thanks for your reply. I got the code below returning the correct results. Cheers, Julian - SELECT tBewoners.IDBew, tBewoners.Calc, tBewoners.Naam, tBewoners.NrKamer,
-
tAfdelingen.Naam, tBewoners.ZZP, tMasterActiviteitenLijst.Functie AS [all except PV VA],
-
tBewoners.IDAfd, Count(tMasterActiviteitenLijst.Niveau) AS Rest,
-
Sum([tABCArray]![maa]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma A],
-
Sum([tABCArray]![mab]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma B],
-
Sum([tABCArray]![mac]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma C],
-
.
-
etc
-
.
-
-
FROM tMasterActiviteitenLijst INNER JOIN (((tAfdelingen
-
INNER JOIN tBewoners ON tAfdelingen.IDAfd = tBewoners.IDAfd)
-
INNER JOIN tABCArray ON tBewoners.IDBew = tABCArray.IDBew)
-
INNER JOIN tActiviteiten
-
ON tBewoners.IDBew = tActiviteiten.IDBew)
-
ON (tMasterActiviteitenLijst.IDAct = tActiviteiten.IDAct)
-
AND (tMasterActiviteitenLijst.IDAct = tABCArray.IDAct)
-
WHERE (((tMasterActiviteitenLijst.Niveau)<>"VA"))
-
GROUP BY tBewoners.IDBew, tBewoners.Calc, tBewoners.Naam, tBewoners.NrKamer, tAfdelingen.Naam, tBewoners.ZZP, tMasterActiviteitenLijst.Functie, tBewoners.IDAfd
-
HAVING (((tBewoners.Calc)<>"N") AND ((tMasterActiviteitenLijst.Functie)<>"PV")) OR (((tBewoners.Calc)<>"N") AND ((tMasterActiviteitenLijst.Functie)="PV"));
-
-
UNION
-
-
SELECT tBewoners.IDBew, tBewoners.Calc, tBewoners.Naam, tBewoners.NrKamer,
-
tAfdelingen.Naam, tBewoners.ZZP, tBewoners.IDAfd, tMasterActiviteitenLijst.Functie AS [PV only],
-
Count(tMasterActiviteitenLijst.Niveau) AS Rest,
-
Sum([tABCArray]![maa]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma A],
-
Sum([tABCArray]![mab]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma B],
-
Sum([tABCArray]![mac]*[tMasterActiviteitenLijst]![Norm]*[tActiviteiten]![Aantal]) AS [ma C],
-
.
-
etc
-
.
-
-
FROM tMasterActiviteitenLijst INNER JOIN (((tAfdelingen
-
INNER JOIN tBewoners ON tAfdelingen.IDAfd = tBewoners.IDAfd)
-
INNER JOIN tABCArray ON tBewoners.IDBew = tABCArray.IDBew)
-
INNER JOIN tActiviteiten ON tBewoners.IDBew = tActiviteiten.IDBew)
-
ON (tMasterActiviteitenLijst.IDAct = tActiviteiten.IDAct)
-
AND (tMasterActiviteitenLijst.IDAct = tABCArray.IDAct)
-
WHERE (((tMasterActiviteitenLijst.Niveau)="VA"))
-
GROUP BY tBewoners.IDBew, tBewoners.Calc, tBewoners.Naam, tBewoners.NrKamer, tAfdelingen.Naam, tBewoners.ZZP, tBewoners.IDAfd, tMasterActiviteitenLijst.Functie
-
HAVING (((tBewoners.Calc)<>"N") AND ((tMasterActiviteitenLijst.Functie)="PV"));
NeoPa 32,556
Recognized Expert Moderator MVP
That's great Julian.
Remember to set Smiley's post as Best Answer.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Paradigm |
last post by:
I am using Access 2K as a front end to a MYSQL database.
I am trying to run a Union query on the MYSQL database. The query is (much
simplified)
SELECT as ID from faxdata UNION
SELECT as ID ...
|
by: M Stery |
last post by:
Have two base queries used for mailing labels with
id,name,address,city,state,zip that are combined via a union query.
One base query uses home address; the other base query uses biz
address. I...
|
by: shaun palmer |
last post by:
when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).
Your help would be greatly...
|
by: Lyn |
last post by:
Hi,
How do you bind the output columns from a UNION query when the fields from
the two tables have different names? Consider this query (WHERE clauses
omitted)...
SELECT SurnameBirth,...
|
by: mattytee123 |
last post by:
I have about 20 tables, of which I would like to do a union query and
count of how many of each different code there is?
The simplified verson of the table is structured like this.
Code ...
| |
by: Sim Zacks |
last post by:
I am using 8.0 beta 1 on an RH 8 Linux server.
I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is...
|
by: BillCo |
last post by:
I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed...
|
by: RJMac |
last post by:
I’m using ADO 2.6 in VB6 to read in data from a Union query (qryJoinBalanceData) in Access 2000. The Union query contains 3 sub-queries, each of which joins several tables, but they all generate...
|
by: billelev |
last post by:
Hi There,
I am performing a UNION query on a Table containing % formatted values with a Query, also containing % formatted values.
The resulting union query displays the table % values as...
|
by: NigelBrown |
last post by:
Hi All,
I have written the below UNION QUERY (I know its wrong but trying to show what I want to achieve) I would like all three tables to convert to one - this is only a one off every day and the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| | |