Calculations in a report

[JonoB]

I have a query that returns about 300 records. Each record contains FundID, DateID and Value. The query automatically returns a single value for the current month and a single value for the previous month - so for 150 funds, there are 2 records per fund, totalling 300 records.

I need to create a report on this. The report must be 150 rows long (i.e. one row per Fund), showing the fund name, value for current month and value for previous month. I can get the report to shows the values for each fund one underneath eacother (thus making the report 300 rows), but I need them to be next to eachother, as various calculations then have to be performed on the difference between the two values.

Thanks for the help.

 

[PaulBricker]

For this example I'll call your existing query "QryA"
Create a query using this SQL. I'll call this "QryB".

SELECT QryA.FundID, Max(QryA.DateID) AS MaxOfDateID, Min(QryA.DateID) AS MinOfDateID
FROM QryA, QryA AS QryA_1
WHERE (((QryA_1.DateID)<QryA.DateID))
GROUP BY QryA.FundID;

Then create a new query with this SQL

SELECT QryB.FundID, QryB.MaxOfDateID, QryA.Value, QryB.MinOfDateID, QryA_1.Value
FROM (QryB INNER JOIN QryA ON (QryB.MaxOfDateID = QryA.DateID) AND (QryB.FundID = QryA.FundID)) INNER JOIN QryA AS QryA_1 ON (QryB.MinOfDateID = QryA_1.DateID) AND (QryB.FundID = QryA_1.FundID);

This should get the values into one record for each FundID. Then create your report from this query.

Paul