Count/Sum Metadata that exists in the same record

[GlennOwns]

We have a SharePoint 2010 Issue tracker at work that we pull metrics off of.

So we add a record and assign it to a person. Lets say another person ALSO worked on this item, so we MUST give them some credit.

So Tech1 has a record of working on an item. Tech2 and Tech3 are placed in the multi-value contribute field.

Data:

ID=Issue1, AssignedTo=Tech1, Contributors=Tech2, Tech3
ID=Issue2, AssignedTo=Tech2, Contributors=Tech1
ID=Issue3, AssignedTo=Tech2, Contributors=Null (None)

So the count will go like this: Tech1=2, Tech2=3, Tech3=1

How do I get these to add up in an Access 2010 query?

It's easy to do a count(AssignedTo) grouped by AssignedTo (which would give me Tech1=1, Tech2=2. Note: Tech3 wouldn't show up because Contributors haven't been specified).

So now I would like to count the multifield contributors. Again, I was able to do a count(Contributors.value) to bring up a total of Tech1=1, Tech2 =1, Tech3=1, but I have no idea how to merge these without crashing the database...

Any help would be great. Thank you!

 

[Isskint]

Would you need to merge them? The 2 queries give you the numbers of Assignments and Contributions, which can be displayed in a report (or form) aginst those 2 categories. If you want to display a total for each Tech on a report, Group by Tech name and do a running sum over the group.

I suppose you could generate a query based on the table that holds your Techs names, IDs etc and use dlookup() to get the values from the Assigned and the Contributed queries

=((NZ(Dlookup("[AssignedCount]","[AssignmentQuery]","[Tech]='" & [Tech] & "'"),0)) + (NZ(Dlookup("[ContributionCount]","[ContributorsQuery]","[Tech]='" & [Tech] & "'"),0)))

 

[GlennOwns]

I was able to merge the data a while ago by using union...