joining queries!! how!

  • Thread starter Thread starter manish
  • Start date Start date
M

manish

I have two cross tab queries in the following format :
both the queries have the following format

Account may june july aug
------- ------ ------ ------ ------

Account contains the infomation like : courier, salary,
purchases etc... the month columns show the monthly
expenses of each account.

One query is for cash expenses.
The other is for bank payments.
both the queries are based on other queries.

Till here there is no problem.
Now i want to merge the two queries into one so that i can
have one column for "cash-may" and one column for "bank-
may" similarly there should be columns like :
cash-june bank-june cash-july bank-july
--------- --------- --------- ---------
is this possible???
if not how can i merge & sum the data from two queries.
I need to make a report at the end of every month to show
the cash expenses, the bank payments and the total of it.
How do I???
any suggestions will be highly appreciated.

warm regards
manish
 
Hi Manish,

I would think you would start
with a query that for sure gets
all the Account's. Is there a chance
you might have an account in one
crosstab and not in the other?

If so, then Q1:

SELECT Account FROM xtab1
UNION
SELECT Account FROM xtab2;

or maybe get all possible "Account"
from original table in Q1 (probably
quicker, if data lets you).

Then LEFT JOIN this query to
both xtabs on Account. Give xtab
for cash an alias of "Cash", the other
an alias of "Bank"

Drag and drop Account down to grid
from Q1.

Drag and drop fields from aliased tables
down to grid, and give each an appropriate
alias.

Say...for Cash.may

change

Field: may
Table: Cash
Sort:
Show: <checked>
Criteria:
Or:

to

Field: cash-may: may
Table: Cash
Sort:
Show: <checked>
Criteria:
Or:

There may be some reason this
won't work with xtabs...I did not
test it.
 
Hi Gary
Thanks for ur suggestion. However what i did was that i
linked both these queries to a query named budget which
was derived from the original table that contained only
account heads & budget amounts. the original table of
account head was also the table that was used as a look up
column for the tables related to the two queries.
So after i linked them all the details were easy to arrive
at using the design view.
however i thank u a lot for answering my question.

warm regards

Manish
 
Back
Top