Union Query Help

[Bob Vance]

I am trying to add tblInvoice.ClientDetail to this query so as it falls in
after (tblInvoice.OwnerPercent,"0.0%") thanks for any Help.....Bob
-----------------------------------------------------------------------
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " -- " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,(tblAccountStatus.PaidAmount * -1) AS Credit, NULL,BillID,-1 AS
Flag
FROM tblAccountStatus;

 

[BobT]

Change the UNION to UNION ALL. UNION only returns the records that match
between the queries. UNION ALL returns all records from both sub-queries.

 

[John Spencer (MVP)]

First the difference between UNION and UNION ALL is that UNION removes any
duplicate records, so you only get the unique rows. UNION ALL does not
combine duplicate rows into one.

Second the purpose of a UNION query is to make a set of the queries involved.
It combines records (rows) vertically. So if the first query returns 5 rows
and the second query returns 3 rows, you end up with 8 rows of data (assuming
the record values are unique).

Perhaps what the poster wants is the following. I'm not sure what the poster
expects/desires as a result of the query.

SELECT tblInvoice.OwnerID
, tblInvoice.OwnerName
, tblInvoice.InvoiceDate AS OnDate
, Iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,
funGetHorse(tblInvoice.InvoiceID)
& " -- " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description
, tblInvoice.ClientDetail
, tblInvoice.OwnerPercentAmount AS AmountSummary
, tblInvoice.InvoiceID
, tblInvoice.InvoiceNo
,0 AS Flag
FROM tblInvoice
UNION ALL
SELECT tblAccountStatus.OwnerID
, NULL
, tblAccountStatus.BillDate AS OnDate
, tblAccountStatus.ModeOfPayment AS Description
, NULL
,(tblAccountStatus.PaidAmount * -1) AS Credit
, NULL
,BillID
,-1 AS Flag
FROM tblAccountStatus;



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

 

[Bob Vance]

Thanks John had to jiggle it around abit but finally got it
SELECT tblInvoice.OwnerID
, tblInvoice.OwnerName
, tblInvoice.InvoiceDate AS OnDate
, Iif(tblInvoice.ClientInvoice=true,((tblInvoice.OwnerName) &
(tblInvoice.ClientDetail)),
funGetHorse(tblInvoice.InvoiceID)
& " -- " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description
, tblInvoice.OwnerPercentAmount AS AmountSummary
, tblInvoice.ClientDetail
, tblInvoice.InvoiceID
, tblInvoice.InvoiceNo
,0 AS Flag
FROM tblInvoice
UNION ALL SELECT tblAccountStatus.OwnerID
, NULL
, tblAccountStatus.BillDate AS OnDate
, tblAccountStatus.ModeOfPayment AS Description
, NULL
,(tblAccountStatus.PaidAmount * -1) AS Credit
, NULL
,BillID
,-1 AS Flag
FROM tblAccountStatus;

 

[Bob Vance]

Oops it looked good when altering the query but when I printed it , it was
wrong
tblInvoice.ClientDetail should only appear when there is No funGetHorse and
tblOwnerPercent
Thanks for any help...........Bob

 

[Bob Vance]

Got it Now.............Thanks Bob

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%"))&
[tblInvoice].[ClientDetail] AS Description, tblInvoice.OwnerPercentAmount
AS AmountSummary, tblInvoice.InvoiceID, tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,(tblAccountStatus.PaidAmount * -1) AS Credit, NULL,BillID,-1 AS
Flag
FROM tblAccountStatus;