How to count multiple field values in a crosstab query

[Swatskeb]

This is a very extensive question, at lease from my point of view not know how to do it. I have a Access table and I need to sum/total/count (not sure what to call it) a few fields and values in the attached excel spreadsheet. The first sheet with all the data was exported from access, this is not being done in excel.

Essentially what I have is about 6-7 PWCs in that field those need to be the row headings in the crosstab like in the second sheet.

AGCNS
AGGCS
AGPNE
AGELE
etc...

essential what I am trying to get in the columns are the total number of records for each PWC, the total number of each time these appear in the ERROR TYPE field (AT, CA, HMAL, OTHER, TM, WUC).

That's a pretty strait forward crosstab, however I want it also to count a different field for each PWC the DDRSTATUS field so I need how many times that these appear (NOT REVIEWED, ERROR CORRECTED, NO ERROR, ERROR NOT CORRECTABLE) the problem is when I try to add this to the query I end up getting the four of these values tied into the row headings so you end up with this as the row heading but still counting error type, I need both the fields to be counted

AGCNS NOT REVIEWED
AGCNS NO ERROR
AGCNS ERROR CORRECTED
AGCNS ERROR NOT CORRECTABLE
AGGCS NOT REVIEWED


AGGCS NO ERROR
AGGCS ERROR CORRECTED
AGGCS ERROR NOT CORRECTABLE
etc...


Also as a bonus if that can be figured out if you look on the second tab I would like to know if access can total ERROR TYPE like how many times each PWC has an error (AT, CA, HMAL, OTHER, TM, WUC) for each PWC. And calculate percentages.

I know this is alot to ask, but am I way off here can access at least do a crosstab with multiple fields with out giving me multiple row headings, or am I going about this in the entire wrong way? Or is there a way to have excel calculate everything in the back ground?

Thank you.

[rpeare]

I don't think what you're looking for is a crosstab query.

Your spreadsheet isn't really helpful in that it doesn't describe how you are calculating the values that are going into the final product on the second tab of your worksheet and the data on the first sheet doesn't match the totals on the second sheet to make it possible to cross check

Try this query to start with:

SELECT [MONTHLY RATES_Query].PWC, Count([MONTHLY RATES_Query].PWC) AS DDRLoaded, Sum(IIf(Len(Trim([error description]))=0,0,1)) AS TotalErrors, [totalerrors]/[ddrloaded] AS InitialErrorRate, Sum(IIf(InStr([error type],"AT")>0,1,0)) AS ATCount, Sum(IIf(InStr([error type],"CA")>0,1,0)) AS CACount, Sum(IIf(InStr([error type],"HMAL")>0,1,0)) AS HMALCount
FROM [MONTHLY RATES_Query]
GROUP BY [MONTHLY RATES_Query].PWC;

if it's going in the direction you want it should be pretty easy to figure out the rest of it.

[Swatskeb]

I'll try that but I'm not sure where the code goes does this go in VBA editor or can I put this in the columns of a query in design view. I'll try to figure it out in the mean time I'll try to church up the example document to give a better idea what I'm looking for

[rpeare]

the code is SQL query.. just open a new query, go to the SQL view and cut and paste that code in there. I basically just imported the excel file you uploaded into an access table then ran the query based on the assumption that the column headers were your field names. If you do the same thing you'll see how it works.

[Swatskeb]

Ok guys I think I got it for the most part, if you see below this is what my SQL ended up looking like, I appreciate the help so much. I think I have got a better example for you guys now so its more understandable if anyone else has input. If you look at the Totals Query sheet and the attached Rates Excel doc u can see how it matches up. I also attached the design view snap shots if its easier to read.

Essentially how this data base is used is there is a total number of jobs to be reviewed, there are a total number of errors identified (Types: AT-WUC), and then there are a number of them that aren't able to be retroactively corrected, and then there are jobs that weren't reviewed within a specified amount of time.

SELECT [MONTHLY RATES_Query].PWC,
Count([MONTHLY RATES_Query].PWC) AS [NO DDRS LOADED],
Count([MONTHLY RATES_Query]![ERROR TYPE.Value]) AS [TOTAL NUMBER OF ERRORS],
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="AT",1,Null)) AS AT,
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="CA",1,Null)) AS CA,
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="HMAL",1,Null)) AS HMAL,
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="OTHER",1,Null)) AS OTHER,
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="TM",1,Null)) AS TM,
Count(IIf([MONTHLY RATES_Query]![ERROR TYPE.Value]="WUC",1,Null)) AS WUC,
Count(IIf([MONTHLY RATES_Query]![DDRSTATUS]="ERROR NOT CORRECTABLE",1,Null)) AS [NO UNCORRECTABLE ERRORS],
Count(IIf([MONTHLY RATES_Query]![DDRSTATUS]="NOT REVIEWED",1,Null)) AS [NOT REVIEWED]FROM [MONTHLY RATES_Query]
GROUP BY [MONTHLY RATES_Query].PWC;

What I am wondering first of all is it possible to remove all of the zeros like the option that's in excel or is there a way to return like a blank string like "" instead of a zero for the count functions. Also is it possible to calculate the percentages like in the excel columns for example "Reviewed Rate" in the excel is calculated

=(B2-M2)/B2

. The percentage total comes from the "NO DDRS LOADED" field. The "TOTAL NUMBER OF ERRORS", "NO OF UNCORRECTABLE ERRORS", and "NOT REVIEWED" fields would be a percentage of the total. I tried to calculate in access using the two fields but it gave me an error about calculating a subquery.

Thank you so much for the help.

[rpeare]

First if you want a null value to appear in any column where there was no activity I think you want to use SUM:

AT: Sum(iif([MONTHLY RATES_Query]![ERROR TYPE] = "AT", 1, null))

If you have nothing but null values as evaluated by the immediate if (IIF) statement the value will be null. Just be aware that if you use a null value in another formula you will have to account for it with a nz([AT],0). Let's say you were adding the AT, CA and HMAL columns if you tried to add those together and some of the values were null you'd end up with a null sum.

Second, this is an aggregate query so you have to create an expression that combines the two formulas.

So if your error percentage is calculated by dividing [TOTAL NUMBER OF ERRORS]/[NO DDRS LOADED] you simply substitute in the formulas that drive each of those fields like:

ErrorRate: Count([MONTHLY RATES_Query]![ERROR TYPE])/Count([PWC])

[Swatskeb]

Hey I'm sorry to bother you guys again. Made a few adjustments to the totals and having a small issue, wondering if anyone knows a workaround. Here is the SQL I have in there now.

SELECT DDRs_Table.PWC,
Count(DDRs_Table.PWC) AS [NO DDRS LOADED],
Sum(IIf([DDRs_Table]![ERROR TYPE].[Value],1,Null)) AS [TOTAL NO ERRORS],
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="AT",1,Null)) AS AT,
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="CA",1,Null)) AS CA,
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="HMAL",1,Null)) AS HMAL,
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="OTHER",1,Null)) AS OTHER,
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="TM",1,Null)) AS TM,
Sum(IIf([DDRs_Table]![ERROR TYPE]![VALUE]="WUC",1,Null)) AS WUC,
Sum(IIf([DDRs_Table]![DDRSTATUS]="ERROR NOT CORRECTABLE",1,Null)) AS [NO UNCORRECTABLE ERRORS],
Sum(IIf([DDRs_Table]![DDRSTATUS]="NOT REVIEWED",1,Null)) AS [NOT REVIEWED]
FROM DDRs_Table
WHERE (((DDRs_Table.PWC)="AG130"
Or (DDRs_Table.PWC)="AGCNS"
Or (DDRs_Table.PWC)="AGCTK"
Or (DDRs_Table.PWC)="AGDBF"
Or (DDRs_Table.PWC)="AGELE"
Or (DDRs_Table.PWC)="AGENG"
Or (DDRs_Table.PWC)="AGFLT"
Or (DDRs_Table.PWC)="AGGCS"
Or (DDRs_Table.PWC)="AGPNE"
Or (DDRs_Table.PWC)="AGWPN") 
AND ((DDRs_Table.RANGESTOP) Like "*" & [Enter RANGESTOP] & "*"))
GROUP BY DDRs_Table.PWC;

My issue is the TOTAL NO ERRORS field in the query causes the NO DDR LOADED and NO UNCORRECTABLE ERRORS field to increase because of a multivalued field, this only happens when using the Sum(IIf function like in the code above. However when I go back to using the Count( function like in the code below for the TOTAL NO ERRORS the multivalued field does not affect the other totals.

TOTAL NO ERRORS: Count([DDRs_Table]![ERROR TYPE])

Below I have uploaded a screenshot of the multivalued field and then the results of the query using Count( followed by using sum(IIf.

In this example for AGCNS there are 11 total jobs, 1 job has an AT error that was correctable, 1 job has a HMAL and WUC error one or both of which was uncorrectable, however we only count entire jobs as uncorrectable not all the errors in that job. Therefore there should be only one uncorrectable error.

Is there an expression I could use to prevent the two fields highlighted in yellow below from increasing to account for the extra check in the mulitvalued field.

I tried to upload my back end database but unfortunately it is to big. If it would help I can try to email a copy of the back end to anyone willing to help.

Thanks

[rpeare]

I didn't realize that field was a multi value field. Save yourself a lot of time and aggravation and normalize your database rather than using calculated/multi value fields. They are a horrible, horrible, horrible addition to MS access and cause newer users no end of trouble.

I don't know if it would work (I don't know how your data is actually stored) but instead of saying [Error type] = "AT", you might have to use something like instr([Error Type], "AT") >0