REGARDING INCLUDING FIELDS IN A REPORT WHICH ARE NOT IN TABLE or Query

[ritimajain]

HI , I want to create a report based on Query1 and include some fields in a REPORT which are not directly present in Query1. i want to include FIELD EXCHID , CELL in a report which are present and include field :


900_active: I want count in this field and 900_active are those WHERE BAND is GSM900 And BLSTATE = <> "MBL"
1800_active: I want count in this field and 1800_active are those WHERE BAND is GSM1800 And BLSTATE = <> "MBL"
total active: I want total count in this field 900_active + 1800_active
900_inactive: I want count in this field and 900_inactive are those WHERE BAND is GSM900 And BLSTATE = "MBL"
1800_inactive: I want count in this field and 900_inactive are those WHERE BAND is GSM1800 And BLSTATE = "MBL"
total inactive: I want total count in this field 900_inactive + 1800_inactive

I am uploading my db .PLEASE HAVE A LOOK YOU WILL BE ABLE TO UNDERSTAND MY PROBLEM MORE CLEARLY
https://app.box.com/s/xln7gif37b2cj9xmw9vm
PLEASE HELP

[ssanfu]

I have A2000, so I can't open your dB.
I can think of two options...

1) use a union query
2) write some UDFs to generate the count numbers



PS
Typing in all caps is the equivalent of shouting. Please try not to shout......

[ritimajain]

hey i m not shouting at you or any body else , i didn't know caps on is equivalent to shouting sorry for that
Anyway i m new to access that's why options you are suggesting i am unable to get , please simplify it for me. i am uploading my db in A2000
https://app.box.com/s/vf8lh8p6smejlluj9k7o
please have a look and help

[ssanfu]

You said

900_active: I want count in this field and 900_active are those WHERE BAND is GSM900 And BLSTATE <> "MBL"
900_inactive: I want count in this field and 900_inactive are those WHERE BAND is GSM900 And BLSTATE = "MBL"

If [BLSTATE] is null, does that count as active?

Would this be accurate:
900_active: I want count in this field and 900_active are those WHERE BAND is GSM900 And (BLSTATE <> "MBL" OR BLSTATE Is Null)

[ritimajain]

no , see MBL means manual blocking which means inactive so
900_active: 900_active are those WHERE BAND is GSM900 And BLSTATE is not MBL.
give me a way to do this when i write all this in Query access choked so give me a better way to do this in report. Please help

[ssanfu]

Some of the records has Band = GSM900 and BLSTATE = NULL.

		BAND	BLSTATE	Count it?
1	900_Active	GSM900	MBL	No
2	900_Active	GSM900	BLO	Yes
3	900_Active	GSM900		?????????

In record 3, BLSTATE is NULL. How do you want to count it??







Since BLSTATE is not MBL, is this 900_ACTIVE?

[ritimajain]

yes BLSTATE is 900_ACTIVE if it is null or BLO or BLL

[ssanfu]

I modified query1 and created Report1. Is this what you want to do?

I had to delete the tables to get the size down, so

1) Extract D22(2000) SS.mdb from the zip zip file
2) IMPORT the tables TRX and TX

Look at the query and report.

[ritimajain]

this is fine but i want result in this format basically i want count and i also want count of 900_active+1800_active=total active and 900_inactive+1800_active=total inactive

[ritimajain]

To calculate total_active i have written a expression:
total_active: IIf(([BLSTATE]<>"MBL" Or IsNull([BLSTATE])),1,0)
total_inactive: IIf([BLSTATE]="MBL",1,0)But it is giving wrong results wherever both 900_active and 1800_active is 0 in total_active field it is giving 1 (false value) mostly there where BAND is null and 900_inactive and 1800_inactive is 0 in total_inactive field it is giving 1 (false value) where BAND is null and BLSTATE is MBL And

and i made another query and add all the above fields and in total field i add Sum and due to above sum is wrong

[ssanfu]

The first dB was to make sure I was headed in the right direction. You were close in your last post.

I (again) deleted the tables in the attached dB...

[ritimajain]

thanxx a tonne man..!!