Link to home
Start Free TrialLog in
Avatar of jtovar3
jtovar3Flag for United States of America

asked on

Creating a report from multiple separate queries

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.
 User generated image
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.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
Avatar of jtovar3


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.
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.

<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...


Avatar of jtovar3


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
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"?

Avatar of jtovar3


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.
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
Avatar of jtovar3


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.
Well, you have to use your own names:

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

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

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?


Avatar of jtovar3


Sorry, I realize now how unhelpful that is haha.


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.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial