A query problem

[Gerry Abbott]

Hi All,
I have one table with two tables linked to two index fields,

tblMain, MainId, CashId, CreditId
tblCash Cashid, CashValue
tblCredit, CreditId, CreditValue

Each record is either a credit or a cash record, and so either the CashId or
the CreditId will be completed. Is there a way I can query this info
(without changing the underlying data) to give me a table containing two
fields, the MainId and a Value field, containing the the cash value for
cash records, and credit value for credit records. ?
As usual any advice welcome.

Gerry Abbott


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/03

[Gerry Abbott]

Apologies for the added virus message with the original post , I have
removed it.
I have also found a solution.
If anyone has a better one please post.

SELECT tblMain.Id, tblCash.CashValue,
tblCredit.CreditValue,
IIf([cashValue] Is Null,[creditValue],[cashvalue]) AS [Value]
FROM tblCash RIGHT JOIN
(tblCredit RIGHT JOIN tblMain ON tblCredit.CreditId = tblMain.CreditId)
ON tblCash.CashId = tblMain.CashId;
Gerry Abbott
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:z4*****************@news.indigo.ie...

Hi All,
I have one table with two tables linked to two index fields,

tblMain, MainId, CashId, CreditId
tblCash Cashid, CashValue
tblCredit, CreditId, CreditValue

Each record is either a credit or a cash record, and so either the CashId or the CreditId will be completed. Is there a way I can query this info
(without changing the underlying data) to give me a table containing two
fields, the MainId and a Value field, containing the the cash value for
cash records, and credit value for credit records. ?
As usual any advice welcome.

Gerry Abbott


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/03

[Fons Roelandt]

Hello,

You should try using a UNION query, a union query isn't supported by the
query builder so you'll have to write in sql. Below your text you can find a
example that should work on the database discribed by you in your message.

I have one table with two tables linked to two index fields,

tblMain, MainId, CashId, CreditId
tblCash Cashid, CashValue
tblCredit, CreditId, CreditValue

Each record is either a credit or a cash record, and so either the CashId or the CreditId will be completed. Is there a way I can query this info
(without changing the underlying data) to give me a table containing two
fields, the MainId and a Value field, containing the the cash value for
cash records, and credit value for credit records. ?

Try using this union query:

SELECT tblmain.MainId, tblCash.CashValue
FROM tblCash INNER JOIN tblmain ON tblCash.Cashid = tblmain.Cashid
WHERE (((tblmain.CreditId) Is Null))
UNION SELECt tblmain.MainId,tblCredit.CreditValue
FROM tblCredit INNER JOIN tblmain ON tblCredit.CreditId =
tblmain.CreditId
WHERE (((tblmain.Cashid) Is Null));

NP! Fons