Background:
tblProperties has (key) txtPropertyName, and nbrProjectCost
tblEquipment has txtPropertyName, and txtProposedEquipment
Tables are linked by txtPropertyName
In the master form I enter the project cost. The nbrProject cost is entered, it is not a calculated value as the number is not the result of adding multiple sums
In the subform, I enter the proposed equipment
The Question:
I am trying to make a report that totals all the nbrProjectCost from all of the properties.
Unfortunately, when I try to sum the nbrProjectCost from every property, the value is repeated for each record in the subform.
For example:
In my master form header, txtPropertyName .... Record 1 = "Building 1", Record 2 = "Building 2" and so on.
In my master form header, nbrProjectCost .... Record 1 = $500, Record 2 = $1000 and so on.
In the subform records, txtProposedEquipment, .... Record 1 = "Equipment A", Record 2 = "Equipment B" and so on.
In my report and/or in the query, when I try to sum the totals of nbrProjectCost for Building1, Building2...etc... the sum math incorrectly looks like this:
500+500+500...+ 1000 + 1000 + 1000... It should look like
500 + 1000 + .... and continue for each property without repeating for each equipment record..
Is this an issue with one to many / many to many relationships? Or is there something else I should be looking at?
Thank you,
Jonathan