Report based on Calculated Fields in Several Queries 1

[mcongdon]

I am working on a database and I have 6 queries calculating values from one table. It is a stock trading database, and the queries calculate commissions for each type of stock. One is CD, one is Municipal Bond, one is Corporate Bonds and so on. All the information that each query is using is stored in the table tblBLOTTER. I have a report that has 6 subreports to show the calculated numbers from these queries. I was wondering if there is any way to create a chart from 6 different queries with calculated fields. It will be a pie chart showing which product has which portion of the commissions for the day. Any help would be amazing, I'm stuck :-/

 

[lespaul]

what are your queries?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[mcongdon]

que_CDsDAY

SELECT Sum([Execution Price]) AS CDSumExecution, Sum([Street Price]) AS CDSumStreet, Sum([Quantity]) AS CDSumQuantity, Count([Execution Price]) AS CDTicketCount
FROM tbl_BLOTTER
WHERE (((tbl_BLOTTER.Firm)=0) AND ((tbl_BLOTTER.Type)="CD") AND ((tbl_BLOTTER.[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]));

que_CG4BOBDAY

SELECT Sum([Execution Price]) AS CG4SumExecution, Sum([Street Price]) AS CG4SumStreet, Sum([Quantity]) AS CG4SumQuantity, Count([Execution Price]) AS CG4TicketCount
FROM tbl_BLOTTER
WHERE (((tbl_BLOTTER.Type)="CG4 Bob") AND ((tbl_BLOTTER.[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]) AND ((tbl_BLOTTER.Firm)=0));

The rest follow the same form. The calculation that needs to be in the chart is (for one section of the pie chart):

=Abs(([CG4SumExecution]-[CG4SumStreet])*[CG4SumQuantity]*10)

the next section would be:

=Abs(([CDSumExecution]-[CDSumStreet])*[CDSumQuantity]*10)

The pie chart will be of these calculated numbers. Right now, they are calculated in each subreport on the main report. Thanks!

 

[PHV]

Why not using a single query like this ?
SELECT Type, 10*Abs((Sum([Execution Price])-Sum([Street Price]))*Sum([Quantity])) As theValue
FROM tbl_BLOTTER
WHERE [Trade Date]=[Forms]![frm_DAILYBLOTDATE]![cboStartDate] AND Firm=0
GROUP BY Type

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mcongdon]

Thank you so much. I'm new to this and I don't really know the ins and outs of SQL. That's perfect!