Union Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pon - 08 Nov 2007 19:48 GMT

I have two queries QRY_CURR with the Curr_yr_bal, account, account type and
QRY_PRIOR with Prior_yr_bal, account, account type.
I want to create a report or a query with difference between Curr_yr_bal and
Prior_Yr_balance, But not all the account in QRY_CURR_YR is in QRY_PRIOR_YR
and viceversa.
Iam not sure what query to use or how to proceed.
Please help.
Thank you
Pon

Reply to this Message

KARL DEWEY - 08 Nov 2007 21:24 GMT

Create a union query with just the Account to build an Account list. Then
left join the list to the othe table.
Then create a union query something like this ---
SELECT Products_all.Product, Products.[Product Name], Products.Vendor,
Products.Amount, Products.ProdDate
FROM Products_all LEFT JOIN Products ON Products_all.Product =
Products.Product
UNION SELECT Products_all.Product, Products.[Product Name], Products.Vendor,
Products.Amount, Products.ProdDate
FROM Products_all LEFT JOIN Products ON Products_all.Product =
Products.Product;

Signature

KARL DEWEY
Build a little - Test a little

> I have two queries QRY_CURR with the Curr_yr_bal, account, account type and
> QRY_PRIOR with Prior_yr_bal, account, account type.
[quoted text clipped - 5 lines]
> Thank you
> Pon

Reply to this Message

pon - 09 Nov 2007 19:42 GMT

Thank you very much. With your idea I created this union Query below and got
the data the way I wanted .
I appreciate your help.
Thank you
PON
UNION QUERY

SELECT QRY_CF_SUMMARY_CURR_YR.FUND, QRY_CF_SUMMARY_CURR_YR.ACCT,
QRY_CF_SUMMARY_CURR_YR.TITLE, QRY_CF_SUMMARY_CURR_YR.RUCL,
QRY_CF_SUMMARY_CURR_YR.RUCL_TITLE, QRY_CF_SUMMARY_CURR_YR.CURR_YR_BAL,
QRY_CF_SUMMARY_CURR_YR.Param_Curr_Begin_date,
QRY_CF_SUMMARY_CURR_YR.Param_Curr_end_date, QRY_CF_SUMMARY_PRIOR_YR.FUND,
QRY_CF_SUMMARY_PRIOR_YR.ACCT, QRY_CF_SUMMARY_PRIOR_YR.TITLE,
QRY_CF_SUMMARY_PRIOR_YR.RUCL, QRY_CF_SUMMARY_PRIOR_YR.RUCL_TITLE,
QRY_CF_SUMMARY_PRIOR_YR.PRIOR_YR_BAL,
QRY_CF_SUMMARY_PRIOR_YR.Param_Prior_Begin_date,
QRY_CF_SUMMARY_PRIOR_YR.Param_prior_end_date
FROM QRY_CF_SUMMARY_PRIOR_YR LEFT JOIN QRY_CF_SUMMARY_CURR_YR ON
(QRY_CF_SUMMARY_PRIOR_YR.RUCL = QRY_CF_SUMMARY_CURR_YR.RUCL) AND
(QRY_CF_SUMMARY_PRIOR_YR.ACCT = QRY_CF_SUMMARY_CURR_YR.ACCT);
UNION ALL SELECT QRY_CF_SUMMARY_CURR_YR.FUND, QRY_CF_SUMMARY_CURR_YR.ACCT,
QRY_CF_SUMMARY_CURR_YR.TITLE, QRY_CF_SUMMARY_CURR_YR.RUCL,
QRY_CF_SUMMARY_CURR_YR.RUCL_TITLE, QRY_CF_SUMMARY_CURR_YR.CURR_YR_BAL,
QRY_CF_SUMMARY_CURR_YR.Param_Curr_Begin_date,
QRY_CF_SUMMARY_CURR_YR.Param_Curr_end_date,
null, null, null, null, null, null, null, null
FROM QRY_CF_SUMMARY_CURR_YR LEFT JOIN QRY_CF_SUMMARY_PRIOR_YR ON(
QRY_CF_SUMMARY_CURR_YR.RUCL = QRY_CF_SUMMARY_PRIOR_YR.RUCL) AND
(QRY_CF_SUMMARY_CURR_YR.ACCT = QRY_CF_SUMMARY_PRIOR_YR.ACCT)
WHERE ((((QRY_CF_SUMMARY_PRIOR_YR.RUCL) Is Null) OR(
(QRY_CF_SUMMARY_PRIOR_YR.ACCT) Is Null)));

> Create a union query with just the Account to build an Account list. Then
> left join the list to the othe table.
[quoted text clipped - 17 lines]
> > Thank you
> > Pon

Reply to this Message