Two Queries

M

Melinda

What would be the best avenue to bring two queries together to form one
report? I have a query that sums of the budget for our company, encumbered
amounts and modifications for 5 pots of money so to speak. The SQL is listed
below and then....

SELECT tblBudget.CostCenter, tblBudget.Dept, tblBudget.date, tblBudget.SAC1,
tblBudget.[100money], tblBudget.[100moneyadj], [100money]+Nz([100moneyadj],0)
AS [100 Money], tblBudget.SAC2, tblBudget.[200money],
tblBudget.[200moneyadj], [200money]+Nz([200moneyadj],0) AS [200 Money],
tblBudget.SAC3, tblBudget.[300money], tblBudget.[300moneyadj],
[300money]+Nz([300moneyadj],0) AS [300 Money], tblBudget.SAC7,
tblBudget.snowmoney, tblBudget.snowmoneyadj, [snowmoney]+Nz([snowmoneyadj],0)
AS [Snow Money], tblBudget.abmoney
FROM tblBudget
GROUP BY tblBudget.CostCenter, tblBudget.Dept, tblBudget.date,
tblBudget.SAC1, tblBudget.[100money], tblBudget.[100moneyadj],
[100money]+Nz([100moneyadj],0), tblBudget.SAC2, tblBudget.[200money],
tblBudget.[200moneyadj], [200money]+Nz([200moneyadj],0), tblBudget.SAC3,
tblBudget.[300money], tblBudget.[300moneyadj],
[300money]+Nz([300moneyadj],0), tblBudget.SAC7, tblBudget.snowmoney,
tblBudget.snowmoneyadj, [snowmoney]+Nz([snowmoneyadj],0), tblBudget.abmoney;

I have another query that lists all the purchases made within the company,
that needs to be subtracted from the initial budget. That query is listed
below.

SELECT DISTINCTROW [tblpurchaseorder Query].[TaxID/SSN], [tblpurchaseorder
Query].[Vendor Name], [tblpurchaseorder Query].PurchaseOrder,
[tblpurchaseorder Query].tblpurchaseorder_Year, [tblpurchaseorder
Query].SACItem, [tblpurchaseorder Query].Encumbered, [tblpurchaseorder
Query].RecTicketNo, [tblpurchaseorder Query].PurchaseOrderNumber,
[tblpurchaseorder Query].DVtype, [tblpurchaseorder Query].InvoiceNo,
[tblpurchaseorder Query].VoucherNo, [tblpurchaseorder Query].MaterialRecDate,
tblticketdetails.Quantity, [quantity]*[priceperunit] AS TotalCost,
tblticketdetails.PricePerUnit, tblticketdetails.Unit,
tblticketdetails.Comments, tblticketdetails.Description,
Sum(tblmodification.ModAmount) AS SumOfModAmount
FROM ([tblpurchaseorder Query] LEFT JOIN tblticketdetails ON
[tblpurchaseorder Query].RecTicketNo = tblticketdetails.RecTicketNo) LEFT
JOIN tblmodification ON [tblpurchaseorder Query].PurchaseOrder =
tblmodification.PurchaseOrder
GROUP BY [tblpurchaseorder Query].[TaxID/SSN], [tblpurchaseorder
Query].[Vendor Name], [tblpurchaseorder Query].PurchaseOrder,
[tblpurchaseorder Query].tblpurchaseorder_Year, [tblpurchaseorder
Query].SACItem, [tblpurchaseorder Query].Encumbered, [tblpurchaseorder
Query].RecTicketNo, [tblpurchaseorder Query].PurchaseOrderNumber,
[tblpurchaseorder Query].DVtype, [tblpurchaseorder Query].InvoiceNo,
[tblpurchaseorder Query].VoucherNo, [tblpurchaseorder Query].MaterialRecDate,
tblticketdetails.Quantity, [quantity]*[priceperunit],
tblticketdetails.PricePerUnit, tblticketdetails.Unit,
tblticketdetails.Comments, tblticketdetails.Description;


The end result I want to show in a report is I need the purchases subtracted
from the entire budget, by SAC (Spending Accounts). I need this report to
reflect the balance of each SAC. I have worked on this for a couple of days
now and I don't get the correct results coming back. Sometimes I get the
intital budget amounts and no modifications. If someone could point me in
the proper direction I would greatly appreciate it.

Thanks,
Melinda
 
Top