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

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Scott McDaniel (EE MVE )

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

ASKER
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.
Scott McDaniel (EE MVE )

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman


<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

jtovar3

ASKER
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
Jeffrey Coachman

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jtovar3

ASKER
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
Jeffrey Coachman

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
jtovar3

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

Well, you have to use your own names:

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

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

Jeffrey Coachman

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
jtovar3

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

;-)