Calculation Query Problems 2

[mcongdon]

I have a table that contains 3 columns that are relevant to this calculation. The query of that table to make the calculation looks something like this:

Quantity        ExecutionPrice     StreetPrice
250              108.217             107.359
5                 97.613              95.613
250              101.529             101.288
100              101.529             101.288

The calculation that I need for reports is going to use the numbers from single rows subtract StreetPrice from ExecutionPrice and multiply by Quantity. Then this number will be added to the same number from the rest of the rows in the query, then multiplied by 10. For this example, the correct calculations would be:

Quantity Execution   Street      Markup/Piece    Concession
250      108.217     107.359     .858              214.5
5         97.613      95.613      2                10
250      101.529     101.288     .241              60.25
100      101.529     101.288     .241              24.1

Total Concessions = 308.85
Multiplied by 10 = 3088.50

Final Answer Should Be 3088.50

I tried using Sums of columns and multiplying like that, but it doesn't work because of order of operations and all that fun mathematical stuff:

SELECT Sum([Execution Price]) AS MUNISumExecution, Sum([Street Price]) AS MUNISumStreet, Sum([Quantity]) AS MUNISumQuantity, Count([Execution Price]) AS MUNITicketCount
FROM tbl_BLOTTER
WHERE (((tbl_BLOTTER.Firm)=0) AND ((tbl_BLOTTER.Type)="Municipal") AND ((tbl_BLOTTER.[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]));

Then when I took (MUNISumExecution-MUNISumStreet)*MUNISumQuantity*10, it returned $20,207. This is not right, and it stems from multiplying the quantities by the whole number concession instead of the individual quantities times the decimal concession.

Ideally, if there is a way to pass the two arrays through the sumproduct function from excel, the calculation would come out correct (Assuming there is a new field that houses the number from Execution-Street in the query).

I know that there is some way to use VBA to pass arrays through Excel functions using ActiveX controls, but I'm pretty new at this and I don't quite understand how to do it. If anyone can help with this or if you have a different idea entirely of how to do this, I would greatly appreciate it.

Thanks!

 

[dhookom]

Try:

SELECT Sum([Execution Price]) AS MUNISumExecution, Sum([Street Price]) AS MUNISumStreet, Sum([Quantity]) AS MUNISumQuantity, Count([Execution Price]) AS MUNITicketCount,
Sum(([Execution Price]-[Street Price])* [Quantity] * 10) AS theCorrectAmount
FROM tbl_BLOTTER
WHERE (((tbl_BLOTTER.Firm)=0) AND ((tbl_BLOTTER.Type)="Municipal") AND ((tbl_BLOTTER.[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]));

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

so this should get you the markup and concession:

SELECT Sum([Execution Price]) AS MUNISumExecution, Sum([Street Price]) AS MUNISumStreet, Sum([Quantity]) AS MUNISumQuantity, Count([Execution Price]) AS MUNITicketCount, Sum([Execution Price]) - Sum([Street Price]) As Markup, (Count([Execution Price]) * (Sum([Execution Price]) - Sum([Street Price]))) As concession
FROM tbl_BLOTTER
WHERE (((tbl_BLOTTER.Firm)=0) AND ((tbl_BLOTTER.Type)="Municipal") AND ((tbl_BLOTTER.[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]));

now for the totals, if you are displaying this information in an Access report, I would just let the report handle the totals. If you're not using Access reports you would have to create a UNION query to also display the totals.

HTH


Leslie

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

http://www.ubuntu.com