Combine 3 queries or add two queries to existing report

[mcupito]

For reporting purposes, I need 2 additional fields for my report. I attain the information for these fields by 2 different queries. Each query finds the Quarterly and Yearly to-date percentages, respectively.

I tried to add them both as subreports, however nothing I was able to come up with has worked.

When trying to combine these 2 queries along with the other query, which returns values per each quarter's ending date, I receive an error: "Unknown Access database engine error."

I will post the SQL for each query:

Query for the Main report:

SELECT NAV_Tbl.NAV_Date, NAV_Tbl.NAV_ReportDate, NAV_Tbl.NetAssetValue
FROM NAV_Tbl
GROUP BY NAV_Tbl.NAV_Date, NAV_Tbl.NAV_ReportDate, NAV_Tbl.NetAssetValue
ORDER BY NAV_Tbl.NAV_Date;

SQL for Quarter-To-Date:

SELECT ([NetAssetValue]/[prev_value])-1 AS QTD
FROM (SELECT t.*, (select top 1 [NetAssetValue]  from NAV_Tbl AS t2  where t2.NAV_Date < t.NAV_Date  order by t2.NAV_Date desc) AS prev_value FROM NAV_Tbl AS t)  AS t
WHERE (((([NetAssetValue]/[prev_value])-1) Is Not Null));

SQL for Year-To-Date:

SELECT (y1.NetAssetValue/YearEndValue)-1 AS YTD, (SELECT TOP 1 y2.NetAssetValue
        FROM NAV_Tbl AS y2
        WHERE Year(y2.NAV_Date) = Year(y1.NAV_Date) -1
        ORDER BY y2.NAV_Date DESC
    ) AS YearEndValue
FROM NAV_Tbl AS y1;

Has anyone tried to implement something along the lines of what I am trying to do who can offer some advice?

I'll attach a snippet of the report in design view as to hope it helps visualize what I am trying to do.

Also, I apologize for the lack of clarity in the Question Title. It's hard to find an accurate description sometimes.

Thanks.

[imgnothumb]http://bytes.com/attachment.php? attachmentid=74 77[/imgnothumb]

[dsatino]

Well, the 'unknown' db engine error is almost certainly related to your SQL statements. I would test them individually in a query and see which one fails. Then figure out why it fails...probabl y syntax.

Also, it's probably cleaner to just write some custom functions to return the values rather that have multi-layered sql statements.

I'm not even sure of what your percentages are supposed to be representing. Percentage of goal? Percent change over the same period last year?

[mcupito]

@dsatino Thanks for your reply. The SQL statements are all working just as they should. The only issue here is getting them all on 1 report.

I was tried to join them into 1 query and the Unknown Access database error displayed. The individual queries themselves work fine.

This is for a report.

[dsatino]

Did you try the joined sql in a query outside of the report? If it works there and not in the report, then it has to do with how the report works while 'printing'.

[mcupito]

Yes. The joining of all 3 queries is what gave me the "Unknown Access database engine error"

[mcupito]

Instead of trying to do what my question had originally asked, I used each query as a subreport and lined up the fields as subreports.

Thanks.