I am trying to create a form that takes sums from certain fields and displays it next to its appropriate field name. I've attached pictures of it in datasheet form (how it's displayed) and its design form.
In more detail, the description items on the left are the "fields" (that I will call "tags" from now on and will be listed in other tables in a column called Associated Bid Items). I've basically tagged several other items in tables and queries throughout my Access App. I am trying to collect all items with the same tags/fields and add up Total MH (man-hour) units associated with these items.
The code I have in the second textbox in the second image is:
=DSum("[Total MH]","qry-PipingHandleMH","[Associated Bid Item]=[Text_BidItem].Value")
where Total MH is what I am summing up, qry-PipingHandleMH is the query I'm pulling from, and Text_BidItem is the first textbox in the second image. I want to sum only the Total MH where the Associated Bid Item (column in other tables where the tags are listed) matches Text_BidItem.
NOW THE PROBLEM: Several items in other tables and queries have already been tagged with each of the tags shown in the first image. Each of those descriptions/tags in the first image should have a Total MH associated with it. The SQL code above does not separate the totals into its rows appropriately as shown in the first image. Here is the longer version of the code:
=DSum("[Total MH]","qry-PipingHandleMH","[Associated Bid Item]=[Text_BidItem].Value")+DSum("[Total MH]","qry-PipingMakeupMH","[Associated Bid Item]=[Text_BidItem].Value")+DSum("[Total MH]","qry-PipingValvesMH","[Associated Bid Item]=[Text_BidItem].Value")+DSum("[Total MH]","qry-PipingWeldingMH","[Associated Bid Item]=[Text_BidItem].Value")+DSum("[Total MH]","qry-CoatingAGMH","[Associated Bid Item]=[Text_BidItem].Value")+DSum("[Total MH]","qry-CoatingUGMH","[Associated Bid Item]=[Text_BidItem].Value")
Basically the same code applied to different tables but added together. That's is whats really in the second textbox in the second image currently. As you can see in the first image it is all applied to the "Large Bore Piping 2" and greater" row. Why isn't the summation of Total MH seperating into its respective rows? DSum() is working but the filtering and separation is just all wrong.
Must concatenate variable parameters. Reference to Text_BidItem is a variable parameter.
=DSum("[Total MH]","qry-PipingHandleMH","[Associated Bid Item]=" & [Text_BidItem])
If [Associated Bid Item] is a text type then will need apostrophe delimiters:
=DSum("[Total MH]","qry-PipingHandleMH","[Associated Bid Item]='" & [Text_BidItem] & "'")
Advise no spaces or punctuation/special characters (underscore only exception) in naming convention.