Hep with Crosstab field names (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
HI! in my db i am trying to summarize the output through cross-tab query.

The challenge i am facing is to give the field name a unique name to avoid multiple columns.

The field "RejectReason" has different reason names data


A) How do i get the below categorized as "Record No exists" so there is only one column and not 4 columns as below.

Record No [146] exists for the current Transaction
Record No [151] exists for the current Transaction
Record No [12167] exists for the current Transaction
Record No [146] exists for the current Transaction

And

B) The below categorized as "Record is not in Clear State"

Record[ 004221] is not in Clear State.

any help is much appreciated.
thanks
 

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
i am uploading my db for clarity, if anyone can help me.
 

Attachments

  • CrossTab v1.1.accdb
    1.4 MB · Views: 44

theDBguy

I’m here to help
Staff member
Local time
Today, 01:19
Joined
Oct 29, 2018
Messages
21,358
Hi. I am thinking you might be able to add a calculated column for your header by maybe using the Switch() function.
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
Thanks TheDBGuy, how can i do that, can you give me example i tried to google but not really getting start.
kindly note i have to use just the start of the data, Record No..... and Record..... as field names
 

plog

Banishment Pending
Local time
Today, 03:19
Joined
May 11, 2011
Messages
11,613
In the cross-tab, replace the RejectReason field with this:

Reason: Iif([RejectReason] Like "*exists for the current Transaction", "Record Exists For Current Transaction", [RejectReason])

Then when you realize the Clear State reason causes the same issue, modify the above code to accomodate it.
 

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
Thanks Plog for the code, i tried to modify to add the other column. Below is the code but i am doing something wrong

Reason: IIf([RejectReason] Like "*exists for the current Transaction","Record Exists For Current Transaction",[RejectReason] & IIf([RejectReason] Like "*is not in Clear State.","Record is not in Clear State"))
Below is the outcome for the second one.
Code:
Record[          004221] is not in Clear State.Record is not in Clear State

Thanks mike60smart for the example, but i was looking for the change in name which i am accomplishing by simple query
 

June7

AWF VIP
Local time
Today, 00:19
Joined
Mar 9, 2014
Messages
5,424
So how many reject reasons are possible? How many columns and what names do you want?

Reason: IIf([RejectReason] Is Null, "None", IIf([RejectReason] Like "*exists*", "Record Exists For Current Transaction", IIf([RejectReason] Like "*is not in Clear State*", "Record is not in Clear State", [RejectReason])))

Example with Switch().

Reason: Switch([RejectReason] Is Null, "None", [RejectReason] Like "*exists*", "Record Exists For Current Transaction", [RejectReason] Like "*is not in Clear State*", "Record is not in Clear State", True, [RejectReason])

Be aware number of characters allowed in cell of query design grid is 1024. This expression has 219, 2 less than the nested IIf() - Access query will drop some of the spaces I included.
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
Thanks June7 for the code, it suffices my requirement for time being. I was not aware, how the switch function was used in query. Thanks for that.

on the below
Code:
So how many reject reasons are possible? How many columns and what names do you want?

So far i have taken sample of 1 month and found only these reject scenarios. I probably have to take atleast 6 months sample to find anymore different types.

I have got an idea how to write the code will try using this in crosstab query.

But how do i write this in the property sheet under column headings
I tried the below
Code:
Like "exists for the current Transaction",Like "is not in Clear State."

thanks
 

June7

AWF VIP
Local time
Today, 00:19
Joined
Mar 9, 2014
Messages
5,424
You put the actual names you want for column headers, not the criteria.

"None", "Record Exists For Current Transaction", "Record is not in Clear State"

However, this does not account for the various other values that will be returned. All headers must be known and specified in the list (months of year is one example). If you want a 'catch all' name for all others, then adjust the IIf() or Switch to return a name such as "Other" when the first 3 parameters are not met.
 

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
Thanks June7 for the suggestion, however i have tried to put the IIf()
it throws me an error and does not allow me to move forward in the query
"the expression you entered is missing closing parenthesis"
 

June7

AWF VIP
Local time
Today, 00:19
Joined
Mar 9, 2014
Messages
5,424
Show attempt. Are you trying to use an "Other" category?
 

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
HI! June7
i tried to use the "Others" with IIf(). I am getting the "Others" but i am not able to display the rest of the reasons. Below is my code
1Reason: IIf(Switch([RejectReason] Like "*exists*","Record Exists For Current Transaction",[RejectReason] Like "*is not in Clear State*","Record is not in Clear State",True,[RejectReason]),"X","Others")

I did with the below without IIf() and getting "Others"
Reason: Switch([RejectReason] Is Null,"Others",[RejectReason] Like "*exists*","Record Exists For Current Transaction",[RejectReason] Like "*is not in Clear State*","Record is not in Clear State",True,[RejectReason])

i wanted to write the query with the IIf() in the 1st quote mentioned above.
thanks
 
Last edited:

June7

AWF VIP
Local time
Today, 00:19
Joined
Mar 9, 2014
Messages
5,424
Still not clear to me what you want. If you want all other reasons to be counted under "Other", try:

TRANSFORM Count([Update Host Posting].PostingDate) AS CountOfPostingDate
SELECT [Update Host Posting].BusinessDate, [Update Host Posting].Status
FROM [Update Host Posting]
GROUP BY [Update Host Posting].BusinessDate, [Update Host Posting].Status
PIVOT Switch([Status]="Success","None",[RejectReason] Like "*exists*","Record Exists For Current Transaction",[RejectReason] Like "*is not in Clear State*","Record is not in Clear State",True,"Other") In ("None","Record Exists For Current Transaction","Record is not in Clear State","Other");

Your expression mixing IIf and Switch doesn't make sense. Where does "X" come from? This is a value in field?
 

lookforsmt

Registered User.
Local time
Today, 12:19
Joined
Dec 26, 2011
Messages
672
Thanks June7

Never mind on the query. i got an idea on how to go about. Thanks once again for the time and help.

God bless
i will close the thread as solved
 

Users who are viewing this thread

Top Bottom