Need help with an append query

[jzwp11]

With respect to eliminating blank pages, make sure to reduce white space in your design and make sure your design fits within the preset margins (or change the margins accordingly)

Regarding the category & contract type, you first have to make sure that they are in the report's record source which is typically a query. If they are in there, you should be able to add text box controls to your report and bind them to those fields. If those fields are not in the query, you need to add them there first.

[cdell7up]

Please take a look at the rptREv and rptNo I think I might have screwed up the relationships maybe.checklistv5 5 3.zip

[jzwp11]

The contract type ID field of the contract type table can only be joined to the fkContractTypeID field of the RevTbl. You had it joined to the Record_ID field of the RevTbl. Regarding the category; it is related to the question and not anything in the revtbl, so it cannot be on the same level as the revtbl related controls in the report.

I'm not sure what you want to do with the category; you'll have to explain further.

The database with the correction to the queries for the contract type is attached.

[cdell7up]

I want the category to display on each report also, right next to the contract type.

There has been a change on how the reports are supposed respond. From the frmRev , once you select "open reviewers report" or "No's Report" it should allow you to save it to where ever location you want, once saved it can display as a pdf file, at that point one can print it by selecting "Print PDF". I noticed you have it so it just prints automatically, sorry but the users would use too much paper this way. Thanks for the code though.

So what is it I need to do to make the above work.

[jzwp11]

I noticed you have it so it just prints automatically, sorry but the users would use too much paper this way

The code should be showing the report in preview mode as indicated in this line. This just opens the report before sending it to the pdf file (see the following line in the code provided earlier).
DoCmd.OpenReport "reportnamehere", acViewPreview, , MyFilter

I want the category to display on each report also, right next to the contract type.

You cannot do that since the category is related to the question. As an alternate, you will need to create a new grouping level based on the category above the question detail section.

[cdell7up]

so how to i proceed with the grouping level?

[jzwp11]

Open the report in design view. You will first have to adjust the query to which the report is tied in order to add the category field. Since the category is associated with the question via the categoryQuestionTbl, you will need that table as well as the category table. You will have to make a join via the question number field in categoryQuestionTbl and whichever table or query already present that contains the question number field. In RptRev that query is qryRevAnswers. In RptNo, it is No_ReviewerResults.

Now to add a new level to the report, you will need to do the following steps:
1. open the report in design view
2. click on the Design tab c
3. click on the Group & Sort icon.
4. Along the bottom pane (below the report), you will see Add a group
5. Click on that Add a group button and select the category field
6. Click on the rectangle with the four dots that run vertical and drag the category group to below the Record_ID group.
7. Make sure you have the header section for the category
8. Now add the control for the category name to the category header section

[cdell7up]

checklistv61.zip I am not getting the results I desire from these reports. I have done what you suggested but I am not getting the right data. Please take a look.

[jzwp11]

It looks like you have it set up correctly, so how is the data not right?

[cdell7up]

Lets take record number 18 as an example on the rptRev. The reviewer is "Hall" and the Specialist is "Rizzolo" when I click the button to open the report. I get a report that shows not just Hall but everyone else. The report is 96 pages long, it should just be 12 pages long. Same with the No report it is showing all of the reports. My intention is when the report is called from the Reviewers input form that it displays just the data for that reviewer. I am not sure where things went wrong, but at one point it was that way.

[jzwp11]

The issue is not with the report; it is with the query or more specifically a table design issue we did not address previously. And it goes back to this information from one of your earlier posts

The thing about the categories of questions is each category has most of the same questions, but there is a series of questions that set them apart.

Commercial= 1001-1411
Major Systems=1001-1411 and 1501-1507
R&D = 1001-1411 and 1603-1612
Construction = 1001-1411 and a701-1718
A&E = 1001-1411 and 1801-1814
Services (Non Fss) = 1001-1411 and 1901-1910
Supplies (Non FSS) = 1001-1411 and 2001-2009
FSS = 1001-1411 and 2201-2214
FMS = = 1001-1411 and 2301-2306

Since the 1001-1411 question numbers fall under every category they get repeated in the query (and thus the report) multiple times. So in order to solve this we need to save the category that is chosen to assign the question to tblRev. In frmRev we had the category combo box as unbound. We need to add the fkCategoryID field to Revtbl and bind the control on the form to that field. I have done that in the attached and altered the appropriate queries on which the reports are based as well

[cdell7up]

I have created a database "with your help" and I want to share it a sharepoint platform. How do I convert it to sharepoint plus lock it down so that no one without proper access can edit the database.

[jzwp11]

Well, that is something I cannot help you with since I have never used Sharepoint. I would suggest starting a new thread on that specific topic as there are probably others on the forum who are more familiar with integrating Access and Sharepoint.

[cdell7up]

Thanks anyway.

Maybe you can help me with this.

I am trying to reconstruct the above database from scratch. I want to rebuild it. What I have done so far is create a new database and named it. I recreated the AnswTbl, BranchTbl, CategoryQuestionsTbl, CatTbl, ContractTypesTbl, ResponseTbl, and imported from the old database QuesTbl. I also imported all the queries, forms and reports. Ok now what do I need to do to get the qryRevAnswers query to display to questions in the frmRevAnswers form? I retain all the code that is VBA, can you figure out what it is that I am missing from this information?

[cdell7up]

database is attached.