Combine Y/N values in one row

[June7]

None of the sample records have that situation.



So which field should take precedence - which code should the Y fall under? If you want it under both, this gets more complicated. Will require a UNION query then use the UNION as source for the CROSSTAB. There is no builder or wizard for UNION, must type or copy/paste in SQLView.

SELECT tracking_number, invoice_number, charge_description_code AS Code FROM shipment_details
UNION SELECT tracking_number, invoice_number, charge_description FROM shipment_details;

Then:

TRANSFORM Nz(Max(IIf([code] In ('AHS','AHW','RES','SAT',"CWT"),"Y","N")),"N") AS Data
SELECT Query1.tracking_number, Query1.invoice_number
FROM Query1
GROUP BY Query1.tracking_number, Query1.invoice_number
PIVOT Query1.code In (SAT,AHW,AHS,RES,CWT);

[NSearch]

The output will now have 7 fields:

tracking_number, invoice_number, SAT, AHW, AHS, RES, CWT

if a tracking number/invoice number pair has cwt in the charge_description, CWT will be set to Y and could also have a Y flag set for any of the other 4 (SAT, AHW, AHS, RES)

Attached is an updated example where you should see a CWT flag set to Y and a RES flag set to Y

Testing_v3.zip

[June7]

I edited previous post, possibly after you read it. Review again.

[NSearch]

Ok, I think I have that part down.

Can you add in the part of the Like statement. I am not sure where to put that.

I need to identify anything that has 'cwt' in the field. It could be 'xxxx cwt' or 'zzzz cwt' or 'yyyy cwt' etc..


I think it might be:

SELECT tracking_number, invoice_number, charge_description_code AS Code FROM shipment_details
UNION SELECT tracking_number, invoice_number, iif(Instr(charge_description,'cwt'),'CWT','') FROM shipment_details;

[June7]

UNION SELECT tracking_number, invoice_number, iif(Instr(charge_description,'cwt')>0,'CWT',") FROM shipment_details;