Greetings. Let me start the question with the final product, and explain in reverse back to the tables. The product is an Access report that shows one line for each building, with summaries of four types of data for that building. For example, the types might be "Estimates," "Proposals," "Invoices," etc.
The report is powered by a query which is composed of a query for the building descriptions, and then sum totals queries by building, one query for each data type (one sum query for "Estimates," one for "Proposals," etc).
Each sum totals query is simply a query showing totals (sum) for a basic table holding details, and a related subtable with the value. For example, the Estimates are in a table called tEst, and the values are in a subtable called tEstDet. So if there are many estimates per building, this query will total the values for each building.
My problem is that in the final query that runs the report, the Estimate value for a building, although summed correctly in the lower queries, sometimes might show a double or triple value of the correct sum. And, of course, this inflated value shows up in the report.
Also, for the record, all queries were producing correct numbers after I made them using data I already had. The problem arises with some buildings and not others, after new data are added.
I understand that if a grouping in a sum query is related to a table/query with multiple items for that grouping, the grouping will display as many times as there are items in the other object. I took pains to work around this. The report (and queries) were functioning. Somehow adding data makes the totals display more than once. I'm fairly perplexed, and appreciate some insight. Thank you very much.
The report is powered by a query which is composed of a query for the building descriptions, and then sum totals queries by building, one query for each data type (one sum query for "Estimates," one for "Proposals," etc).
Each sum totals query is simply a query showing totals (sum) for a basic table holding details, and a related subtable with the value. For example, the Estimates are in a table called tEst, and the values are in a subtable called tEstDet. So if there are many estimates per building, this query will total the values for each building.
My problem is that in the final query that runs the report, the Estimate value for a building, although summed correctly in the lower queries, sometimes might show a double or triple value of the correct sum. And, of course, this inflated value shows up in the report.
Also, for the record, all queries were producing correct numbers after I made them using data I already had. The problem arises with some buildings and not others, after new data are added.
I understand that if a grouping in a sum query is related to a table/query with multiple items for that grouping, the grouping will display as many times as there are items in the other object. I took pains to work around this. The report (and queries) were functioning. Somehow adding data makes the totals display more than once. I'm fairly perplexed, and appreciate some insight. Thank you very much.
