By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

Combine 3 queries or add two queries to existing report

100+
P: 294
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT NAV_Tbl.NAV_Date, NAV_Tbl.NAV_ReportDate, NAV_Tbl.NetAssetValue
  2. FROM NAV_Tbl
  3. GROUP BY NAV_Tbl.NAV_Date, NAV_Tbl.NAV_ReportDate, NAV_Tbl.NetAssetValue
  4. ORDER BY NAV_Tbl.NAV_Date;
  5.  
SQL for Quarter-To-Date:
Expand|Select|Wrap|Line Numbers
  1. SELECT ([NetAssetValue]/[prev_value])-1 AS QTD
  2. 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
  3. WHERE (((([NetAssetValue]/[prev_value])-1) Is Not Null));
  4.  
SQL for Year-To-Date:
Expand|Select|Wrap|Line Numbers
  1. SELECT (y1.NetAssetValue/YearEndValue)-1 AS YTD, (SELECT TOP 1 y2.NetAssetValue
  2.         FROM NAV_Tbl AS y2
  3.         WHERE Year(y2.NAV_Date) = Year(y1.NAV_Date) -1
  4.         ORDER BY y2.NAV_Date DESC
  5.     ) AS YearEndValue
  6. FROM NAV_Tbl AS y1;
  7.  
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.

Attached Images
File Type: jpg nav.jpg (47.6 KB, 309 views)
Jan 29 '14 #1
Share this Question
Share on Google+
5 Replies


dsatino
100+
P: 393
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...probably 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?
Jan 31 '14 #2

100+
P: 294
@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.
Jan 31 '14 #3

dsatino
100+
P: 393
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'.
Jan 31 '14 #4

100+
P: 294
Yes. The joining of all 3 queries is what gave me the "Unknown Access database engine error"
Jan 31 '14 #5

100+
P: 294
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.
Feb 5 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.