How to get values correct which are in access database

[#1]

Hi,

Please find attached.

I need to get the number of cases closed by Person(Who) and by WorkItemtype. It is getting values less when compared to values which are in access database. Please could anyone tell me where I am doing mistake.

Thanks.

[#2]

Hey Amelia,

I was able to open it. Looking at it it seems like its showing the right results.

It is working per the data model designed. You have all those null ItemStatus so they wont be considered in the count for 'Closed'

[#3]

Thanks. HistoryID is the ID whihc the persons history is stored so they mentioned as HistoryID but this is not in TblClient table in database side. How can I fix this issue please help me

[#4]

Hi,

Thanks and how can I get that NULL count also. Please help me . Is there anything I need to change the expression?

[#5]

Please help me as it is showing entirely different values I suppose there is a issue with expression how can it wotk will Cross table will it be useful? If so please help me how to use Cross table

[#6]

Amelia please check the attached doc. I changed the formula for Closed (it will consider null as well).

So for example it will look like

Who	WorkItemType	Closed
Aaron McLaughlin	Original Paper Work	2
	-	7
	Total	9

The reason there is a null WorkItemType is because there are 9 client IDs which dont have a workitemtype.

The two tables of interest in your data model are History and Client. Your History has some ClientIDs that are not in Client table thats why you get all those nulls for ItemStatus and WorkitemType.

You can maybe check with your data expert and see why.

[#7]

Hi,

Thanks and now the values are getting doubled for each one and it is showing different for WHO and different for WORKITEMTYPE. But those should be same .

Please help me how to set this completely.  

[#8]

Oops sorry. Changed the formula.

Can you please elaborate on why WHO and WORKITEMTYPE should be same. WORKITEMTYPE is a subset of WHO so for example

WHO	WorkItemType	Closed
Ashleigh Myers	Confirmation of Payment Arrangement	1
	Modifications	1
	Review Proposal/Chairman's Report	1
	-	187
	Total	190

So Ashleigh Myers has total 190 ClientIDs of which 1 for Confirmation of Payment Arrangement, Modifications, Review Proposal/Chairman's Report and 187 with no WorkItemType assigned.

If you still think its incorrect can you post the original Access data for this one Person.What do you think should be the result.  We will use this person for our testing to make things easier.

Thanks