I have this card.. its attached in the image, how can i get those that are - 1 has to be shown as A and 0 has to be shown as B.

thanks a lot
Any help please..

In you column heading filed in your cross tab query use the following syntax

Alias:IIF([Field]= True,"A","B")

In you column heading filed in your cross tab query use the following syntax

Alias:IIF([Field]= True,"A","B")

Thanks for your help but looks what happened, all the chart is filled... I need only specific dates.

Break it down further:
IIF([Field] = True, "A", IIf([Field] = False, "B", [Field]))

Break it down further:
IIF([Field] = True, "A", IIf([Field] = False, "B", [Field]))

Thanks a lot. It works perfectly..

Glad we could help!

Glad we could help!

Thanks again for your help.. Another question please, now i want to add all those A which are equivilant to 1 and B are equivalent to 1/2. I think is a bit complicated but for sure you will help me.

If for example i have in January 4A's and 1B, the total should be 4.5

Thanks again....

Are you adding them as a column to the right? And are the numbers 1 to 31 static?

Are you adding them as a column to the right? And are the numbers 1 to 31 static?


Hi..

The total of every Month is placed on the right as Row Heading. The 1-31 are column headings and static. Hope this helps you.

Then create a duplicate query but replace A with 1 and B with .5

Save that query
then create a new query which sums the 1's and .5s

I was thinking the OP wants to sum per month, i.e. row by row. If that's the case then you need to do this:
[1] + [2] + [3] + ... + [31]

Add them all up up to 31.

I was thinking the OP wants to sum per month, i.e. row by row. If that's the case then you need to do this:
[1] + [2] + [3] + ... + [31]

Add them all up up to 31.

Thats right...

That looks like a crosstab query. If it is then you would need to create a new query and select all the fields from the crosstab query into it. You will then be able to perform the addition.

That looks like a crosstab query. If it is then you would need to create a new query and select all the fields from the crosstab query into it. You will then be able to perform the addition.

I cannot make the total it in the same crosstab query?? I you look test2.jpg, on the right hand side there are 1,5,etc under each other, i want the totals there.

See if this gets you the results in your crosstab query.

Here's the alias field:
AliasName: (Select Sum(IIF([Field] = True, 1, IIf([Field] = False, 0.5, Null))) From NameOfTable AS Q WHERE Q.Mont = [Mont])

Under TOTAL (i.e. instead of GROUP BY) for that field select FIRST.

See if this gets you the results in your crosstab query.

Here's the alias field:
AliasName: (Select Sum(IIF([Field] = True, 1, IIf([Field] = False, 0.5, Null))) From NameOfTable AS Q WHERE Q.Mont = [Mont])

Under TOTAL (i.e. instead of GROUP BY) for that field select FIRST.

Managed to get it worked!!! Thanks a lot.

You're welcome.