Aid combining 2 queries into one report

rbuser

Here is what I'm trying to do

Report Results I'm looking for:
Invoice Number | SALES | COGS

The trick is that sales is the sum of credits - debits where the account
is an income account, and the cogs is the sum of debits - credits where
the account is a cost of goods account.

I figured, because there are 2 calculations that are separately based
conditions, I would need 2 queries.

the basis of the queries are:

select [invoice number],
round(sum(ifnull([credit amount],0))-sum(ifnull([debit amount],0)),2) as
Sales
from genledg
where [date] between '9/1/2006' and '9/30/2006'
and [account number] in
(select [account_id] from catagory
where ([account type] = 'INCOME' or [account type] = 'CONTRA INCOME'))
group by [invoice number]

^^^ Gives me my sales #'s

select [invoice number],
round(sum(ifnull([debit amount],0))-sum(ifnull([credit amount],0)),2) as
COGS
from genledg
where [date] between '9/1/2006' and '9/30/2006'
and [account number] in
(select [account_id] from catagory where
([account type] = 'COST OF GOODS' or [account type] = 'CONTRA COST OF
GOODS'))
group by [invoice number]

^^^ Gives me my COGS #'s

So now, how do I get those into a single report that shows Invoice,
Sales (0 if no amount), Cogs (0 if no amount).

Any help would be most grateful!

Thanks in advance

-CK

nicocizik

Hi CK,

I would recommend two options.

1. Try placing two subreports side by side, each one connected to a
separate dataset. These subreports will traverse the data of each dataset
independently.

2. If these two datasets are 1 to 1 with the Invoice Number, you should be
able to left join the two datasets you have below into a single dataset. I
believe most databases allow the joining of subqueries.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com