I have all of these reports, (which get data from queries, which get data from tables, that have relationships, etc. etc.) and these reports all make one big report, and this report hinges on whatever department is selected in the ComboBox, on a form. That ComboBox references the departments from a regular, non-SharePoint table. And all was well.
But now, I am needing to run the same reports (meaning all those sub-reports, all those queries, making sure all those linkages are in place) for some new departments. For some departments that are in a linked SharePoint table. So I did a Union query for the Row source in the combobox; I got the new choices for departments from the SharePoint table to show up for the user to pick in the combobox, but it does not spit out data for every department.
So I've been trying to edit the queries and reports, and make identical copies of them, except changing the "Department" field to look at the SharePoint table instead--It's not pretty, and it's not working.
To give an idea of my overall setup, here's a sceenshot of some of the linkages, in my database:
When I said above that with the original choices in the combobox (when it was only getting choices from an Access table called "tblDepartments) always worked right, sometimes I noticed that the departments were choices there, but when I tried to do a report, there was blank data. So if I could get the departments from the Access table (called "tblDepartments") and the sharePoint table (called "IS Development Projects: Access Reports table") linked correctly, somewhere...or get the "Join" properties fixed somewhere, maybe this'd work right. But I can't seem to figure out how to make all departmens from both tables show up,a nd jsut get all data from both departments.
After all this, my thoughts are that maybe it has something to do with the fact that, in that original picture, http://www.rjbfabrication.com/summary.png , the "Requesting Department" from the Access Table (called "tblDepartments") and the SharePoint table (called "IS Development Projects: Access Reports") are not linked. Could this be it? Because theoretically it can jump table-to-table, since the "Project ID" field (in the access table, "tblDepartments") is linked to the "Identification" field (in the SharePoint table "IS Development Projects: Access Reports"), but I think there are join errors or something, somewhere.
First, I forgot to mention that I am using Access 2003.
Second, I've further identified something that may help in coming up with an answer. Let me break down my main report and the different reports that comprise it, first:
Main report, made up of 7 other reports (sub-reports)
The first 5 reports deal with queries that have a field which multiples by a "Department Percentage" figure, pulled from a table called "Department Percentage".
The last 2 reports deal with queries that do not multiply by a "Department Percentage".
(pictures of these things are found below, in this post)
Now not every department under "Requesting Department" in the IS table ("IS Development Projects: Access Reports") is inside of the "Department Percentage" table, so whenever the calculated "Hours Spent" field for the queries that deal with it multiplies by whatever it gets from "Department Percentages" it never comes up with any data because if the department isn't there, there's nothing to multiply by.
Now I'm just thinking that this might be the solution--I'm not a hundred percent sure. It just seems to me that the fact that the queries that are working with these new departments do not have that multiplying-by-percentages-field, while all the queries that do rely on multiplying-by-percentages field do not work. Here are some examples:
One of the queries that does multiply by the percentages table:
If this theory is right, would the only step I need to take be to make sure that all of the departments that are in the SharePoint table ("IS Development Projects: Access Reports") are also in the "tblDepartmentPercentages" table? If THAT is the case, I could manually insert them and theoretically be all right for now; but what would be a good feature to incorporate in order to insure that if new departments are ever added to the SharePoint table, they will automatically be carried over to the tblDepartmentPercentages table so that these queries can still run?
I haven't looked at the links, but if you have a relationship that returns an empty record set you will get an empty report. Run the underlying queries, and trace back if needed, to find which ones are returning empty record sets. Once you find the tables with missing records, you can run append queries prior to opening the report to add any needed records. If appended data can change, you may need to run update queries as well.