Solved

Creating a report from multiple separate queries

Posted on 2011-03-04
14
329 Views
Last Modified: 2012-05-11
So I have very limited knowledge in creating reports, but I guess i want to see if this can be done, and if it can be, what steps do i need to take in order to make this happen. I will upload a sample of the database as well as a picture copy of the report i'd like to generate.

I would like to create a report in the following format.
 sample report
in the image, i've included some code as to what fields i'd like where. I'd want to report observations, recommendations, and updates that are all related to a main audit ID. Then each section would repeat as necessary.

The only way I could think about doing this, is maybe having separate queries then combining them into 1 report, but I dont know how to go about doing that?

Any guidance or suggestions is welcome.
AuditTrackerVersionTest.accdb
0
Comment
Question by:jtovar3
  • 7
  • 5
  • 2
14 Comments
 
LVL 84
ID: 35038763
This strikes me more as a request for work than anything else.

Are you familiar with building reports in Access? If not, I'd suggest you work through some of the online tutorials regarding this. Building Reports can be one of the most difficult parts of Access development, and in fact it is at the reporting building phase when you most often see problems develop if other issues are faulty or poorly built.

It looks like you're working out of (at least) 3 tables, which compunds your issue quite a bit. In many cases you can build a Query that represents this data. If you can, then building your report will be fairly simple. If you can't, then you'll either (a) have to reformat your table structure to a more normalized one or (b) attempt to use Grouping and Subreports to represent your data. This can be difficult to do.
0
 

Author Comment

by:jtovar3
ID: 35038881
sorry. I wasn't trying to make it seem like a request for work... Like I had said, I'm still new at this and was 1) wondering if something like I am suggesting could be done and 2) maybe seeing if anyone knew some helpful tutorials that i could look at.

Subforms could possibly be the way to go, but I want to be able to print out the data like displayed above.
0
 
LVL 84
ID: 35039636
I'm sure it could be done, and your concept of "3 seperate queries" would be the same as "3 seperate subreports". Build reports based on those queries, and then combine them into a single "master" report.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35059873

<Subforms could possibly be the way to go, but I want to be able to print out the data like displayed above.>

Then also try to see if you can join all three tables and get all of the info needed for the report.
Then you also may be able to create a "Grouped" report instead, depending on the structure of the data.

You can also do a Pseudo "Mail Merge" style report in Access.

But again, see if you can create a single query that gives you everything you need.

Something like this roughly as a start perhaps?...
SELECT tbl_Audit.Audit_Name, tbl_Audit.Audit_Date, tbl_Observation.Company_Name, tbl_Observation.Obs_ID, tbl_Observation.IAF, tbl_Recommendations.Rec, tbl_Recommendations.Obs_ID, tbl_Updates.Update
FROM ((tbl_Audit INNER JOIN tbl_Observation ON tbl_Audit.Audit_ID = tbl_Observation.Audit_ID) INNER JOIN tbl_Recommendations ON tbl_Observation.Obs_ID = tbl_Recommendations.Obs_ID) INNER JOIN tbl_Updates ON tbl_Recommendations.Rec_ID = tbl_Updates.Rec_ID;

...though the multiple entries of "Test" in many fields of many of your tables produces an output that is not very useful...
;-)

JeffCoachman

0
 

Author Comment

by:jtovar3
ID: 35072783
I tried using the report wizard, and it is coming up with something that I can use. However, I don't know how to edit which records it shows. Any quick help on editing which records to be displayed?

also, sorry about the TEST stuff. You know.. "sensitive" information hah
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35072859
I'm getting confused...
<I don't know how to edit which records it shows>
I thought this was for a report?
Why would you need to "Edit" the records?


<Any quick help on editing which records to be displayed? >
Do you mean "Filter"?

0
 

Author Comment

by:jtovar3
ID: 35084279
I uploaded a new version of the database with some more data in it. Not too much, but hopefully enough.

I created a report using the report wizard which is basically exactly what i want. EXCEPT, it is showing all the data for all the audits in the audit table. How do I get it to just show the information for 1 audit item at a time. For example, it is currently showing both Audit Name: Testing and Audit Name: Nicer Test. I just want it to show Nicer Test and the information following it.

If possible, i want to be able to put a combo box on a separate form so they can select which audits to run reports for.
AuditTrackerVersionTest.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35084594
Then it seems that the focus of this question is changing from your original question to now filtering.
Also note that you are not stating if the Audit ID is text or numeric.
(I will presume numeric)

Filtering (Not editing) the "Audits" listed in a report can be done in many ways, here is one:

Make a form.
Create a combobox (cboAudits) that lists the various Audits

Then put a button on the form to open the report:
    DoCmd.OpenReport "YourReport", acViewPreview, , "AuditID=" & Me.cboAudits
0
 

Author Comment

by:jtovar3
ID: 35087375
Then put a button on the form to open the report:
    DoCmd.OpenReport "YourReport", acViewPreview, , "AuditID=" & Me.cboAudits

Do I put that in the Build Event for the Button? Also, will I have to edit my report so that it recognizes this? I tried to put it in, but it did not work.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35087481
Well, you have to use your own names:

DoCmd.OpenReport "YourReportName", acViewPreview, , "Audit_ID=" & YourComboboxName

My code was just an example illustration the syntax...

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35087538
Also whenever you say: "it did not work", pPlease describe what you mean.
ex: System crash?, Incorrect result?, ...etc

Saying "it did not work" is kinda like going to your car mechanic and saying, "My car does not work".
;-)

Make sense?

;-)

Jeff
0
 

Author Comment

by:jtovar3
ID: 35087792
Sorry, I realize now how unhelpful that is haha.

ANYWAYS...

In the Arguments section of the Build Event for the button I placed, I put "AuditReport, Report, , [Audit].[ID]=[Me].[cboAudits], Normal"

Where AuditReport is the name of the Report I built using the Report wizard, AuditID is numerical and a field in the pre made report.

The report does not change at all.

Also, this may or may not be the reason, but I created the report using report wizard and created it off of the audit table.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 35088041
<In the Arguments section of the Build Event for the button>
I don't really know what this means, but...

Delete whatever you have there and save and close the form.

Now, re-open the form.
Go into design view.
Right-click the button, select "Build Event", then click "code builder".

This where the code I posted goes.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199653
;-)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now