By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

A query problem

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.