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