Report Format Help

[sysop470]

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..

 

[DCrake]

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

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

 

[sysop470]

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.

 

[vbaInet]

Break it down further:

IIF([Field] = True, "A", IIf([Field] = False, "B", [Field]))

 

[sysop470]

Break it down further:

IIF([Field] = True, "A", IIf([Field] = False, "B", [Field]))

Thanks a lot. It works perfectly..

 

[vbaInet]

Glad we could help!

 

[sysop470]

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....

 

[vbaInet]

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

 

[sysop470]

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.

 

[DCrake]

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

 

[vbaInet]

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.

 

[sysop470]

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...

 

[vbaInet]

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.

 

[sysop470]

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.

 

[vbaInet]

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.

 

[sysop470]

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.

 

[vbaInet]

You're welcome.