Solved

Creating a report from multiple separate queries

Posted on 2011-03-04
14
308 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

13 Experts available now in Live!

Get 1:1 Help Now