How do you sum only unique occurances a field that has a duplicate value in multiple records

[mabjro]

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

[aytee111]

- NEVER carry names across multiple tables. This will come back and bite you. Add an autonumber PK and carry that across to all other tables
- projects should be on a separate table to properties - this too will come back and bite you when you do a second project on the same property
- your query should be on the one table (or the two when you separate them) - i.e. the detail table does not carry cost so should not be included in the query. If each building is separate, what are you summing/why do you need it?

[mabjro]

aytee111,

I need to total all of the properties project cost because I need to do a report that shows total energy saving capital expenditures and the resulting energy savings for an entire portfolio of properties. There is much more to the database than what I have posted here. However, I provided a stripped down example of what I am trying to accomplish in an effort to simplify my not so easy to ask question.

The property name is the primary key and therefore it has to be used as a foreign key in the other table. For some reason, I thought auto number primary keys were frowned upon, but I guess I have it backwards. It may be too late for me to heed your advise on that suggestion.

Projects is a separate table from properties, but maybe I am not understanding what you are telling me. (as a novice it can be difficult to elaborate a question and/or understand the answer)

Your third point is where I think you may solved the problem. I will give that a lot of thought. If I want the query to be the control source of the report, don't I have to include all the fields in the query that are going to appear on the report?

Thank you for your time, I greatly appreciate it.

Jonathan

[aytee111]

Yes you have it backwards, autonumbers are used all the time, never names or descriptions - those change too easily, spelling mistakes, company name changes, etc. I would STRONGLY advise you to make the change now, especially if the database is still under development. It will be a pain, I know, but worth the effort now.

You stated that property name and project cost were on the same table, hence my advice to have two tables, one for buildings and one for projects, and only carry that exact information ("one subject per table"). This is mixing and matching.

You are right in that your query has to carry all information needed on the report. However, any calculated fields can be done either in the query or on the report. And it doesn't matter if values are duplicated in the query, the report will be grouped on property so the duplicated fields will only be shown once on the report anyway. I still don't see why you are summing those values, however. From what you said there is only one value per property, so ..??

[mabjro]

Thank you aytee,

Yes there is only one value per property, but I need to combine all of those values to show the total combined value of all properties.

Property A = 500
Property B = 1000
Therefore the portfolio value = 1500.

All great advice. Thanks again. I have to really think it through. I am not sure how I would put the project cost value on the project table because each line item (record) in the project table does not have a project cost value. I am not itemizing the project cost because the cost is quoted to me many different ways from many different vendors.

Jonathan

[aytee111]

Hopefully you have a vendors table and also a table that carries propertyID and projectID and vendorID and quote cost. If the total project cost is a calculated field then it should not be stored on a table.

A total cost of all properties will be calculated on the report, no need to carry it in the query. Reports will do grouping and you can put the total in the group footer section. Or using the wizard, Access will create it for you.

[mabjro]

Aytee,
Please correct me if I am wrong, if the report has the query as the control source, then I have to include the total cost of all properties in the query in order for the report to total it?
I appreciate your great effort to try to help me and the fact that I do not appear to be able to solve the problem with your help is no reflection on you.
Thank you,
Jonathan

[mabjro]

Sorry, I now see what you meant. I do not have to total nbrProjectCost in the query, I can do it in the report. However, I still have the problem of the report adding an occurrence of project cost to every record. The total is exponentially out of whack regardless of if I total it in the report or if I total in in the query.

Thanks.

[aytee111]

That's ok, don't worry about the learning curve.

The report can do some things for you, including any and all calculations - only the fields used in the calculation need to be in the query. Before going any further with this, create a simple query with just those two tables and then use the report wizard to display the records. Include grouping and include totals for the group - and let Access make the report for you. Then you will see what I am saying.

[mabjro]

aytee111, I wanted to come back around to you on this because it is now solved. Your advice was all relevant and led me down a path that allowed me to solve the problem. The solution was actually simple, but you could not have known what I did not know. I had no idea that if I change "group by" to "sum" on every field in the query, it would omit all the repeat values. Sadly, I did not have a good grasp on how sum worked in a query. Thank you so much for your help.

Jonathan

[aytee111]

You're welcome, glad it has worked out for you,