Combine Y/N values in one row

[NSearch]

Thanks in advance for your help on this. I would like to have tracking_number and invoice number on a single row with the Y/N flag set for SAT, AHW, AHS, RES, but when I run the query below, I get a row for each tracking_number/invoice number pair that has a flag set to Y for any of the fields mentioned.

SELECT tracking_number,invoice_number,
Iif( charge_description_code = 'SAT', 'Y', 'N') AS SAT,
Iif(charge_description_code = 'AHW', 'Y', 'N') AS AHW,
Iif(charge_description_code = 'AHS', 'Y', 'N') AS AHS,
Iif(charge_description_code = 'RES', 'Y', 'N') AS RES
        FROM   shipment_details
        WHERE  tracking_number IS NOT NULL
               AND charge_description_code = 'SAT'
                OR charge_description_code = 'RES'
                OR charge_description_code = 'AHW'
                OR charge_description_code = 'AHS'
GROUP BY tracking_number,invoice_number,charge_description_code

Result

tracking_number	invoice_number	SAT	AHW	AHS	RES
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	N	Y	N	N
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	N	N	N	Y

Expected

tracking_number	invoice_number	SAT	AHW	AHS	RES
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	N	Y	N	Y

[orange]

Can you show us a line or 2 (an example) of your expected output?

[NSearch]

Can you show us a line or 2 (an example) of your expected output?

Hi orange, thanks for the response. I edited my post above.

[orange]

You have more fields in the output than in the input (table design), so I think you're going to need another table with fields for SAT, AHS, AHW and RES.

But I looked at Allen Brown's ConcatRelated function and some test data.

I mocked up your table with some values, created a query QInterim to put data in alphabetic order, then QFinal to use the ConcatRelated function to get a result for your conideration --given the table structure you have.
The test table and values

tracking_number	invoice_number	charge_description_code
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	RES
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHW
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	RES
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	SAT
4Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	SAT
4Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS

QInterim

SELECT Shipment_details.tracking_number
, Shipment_details.invoice_number
, Shipment_details.charge_description_code
FROM Shipment_details
ORDER BY Shipment_details.tracking_number
, Shipment_details.invoice_number
, Shipment_details.charge_description_code;

QInterim result

tracking_number	invoice_number	charge_description_code
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	RES
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHW
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	RES
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	SAT
4Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS
4Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	SAT

QFinal

SELECT DISTINCT QInterim.Shipment_details.tracking_number, QInterim.Shipment_details.invoice_number, ConcatRelated("charge_description_code","QInterim","charge_description_code IN ('SAT','AHS','AHW','RES') and tracking_number & invoice_number ='" & tracking_number & invoice_number & "'") AS ChargDescCode
FROM QInterim;

QFinal result

tracking_number	invoice_number	ChargDescCode
1Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS , RES
2Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHW , RES , SAT
4Zxxxxxxxxxxxxxxxx	0000003V0XXXXXX	AHS , SAT

Hope this is helpful. Please let forum know if you want to pursue your 4 fields with Y/N values.

[NSearch]

Thanks orange for the detailed response, but I am actually not looking to concatenate the fields. I would like the fields separated. Y trumps N, so anytime there is a Y value, I would like them all represented on the same row for any given tracking_number / invoice_number combination.

Current results:

tracking_number	invoice_number	SAT	AHW	AHS	RES
1Z1234	0000003X	N	Y	N	N
1Z1234	0000003X	N	N	N	Y

Expected Results:

tracking_number	invoice_number	SAT	AHW	AHS	RES
1Z1234	0000003X	N	Y	N	Y

[June7]

I would like to see sample raw data that provides the output you expect.

Consider:

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

[orange]

Just looked at this again. I adjusted your query to get things in alphabetic order called it Query2 in attached database.
Then wrote a sub to create the required records - I write them to immediate window, but could put them in anew table with the additional fields.

Run Sub jMay25 in module1

The output looks like this

Records Grouped by Tracking and Invoice Numbers
==================================================

Records Grouped by Tracking and Invoice Numbers
==================================================


Tracking              Invoice     SAT AHS AHW RES 

1Zxxxxxxxxxxxxxxxx, 0000003V0XXXXXX, N, Y, N, Y
2Zxxxxxxxxxxxxxxxx, 0000003V0XXXXXX, Y, N, Y, Y
4Zxxxxxxxxxxxxxxxx, 0000003V0XXXXXX, Y, Y, N, N

Update: Just saw post by June ---much cleaner than my approach.

tracking_number invoice_number AHS AHW RES SAT
1Zxxxxxxxxxxxxxxxx 0000003V0XXXXXX Y N Y N
2Zxxxxxxxxxxxxxxxx 0000003V0XXXXXX N Y Y Y
4Zxxxxxxxxxxxxxxxx 0000003V0XXXXXX Y N N Y

Also, I think there is an error in my effort --I see my group1 is showing AHW and SAT (incorrect), it is correct AHS and RES in June's solution. I'll check my stuff later???? I didn't think of crosstab --haven't used one in a loonnng time.

Update2:
I have updated the output above after reviewing and adjusting the code. I was not processing the first record after a group change. I have also replaced the database with this adjustment.
The crosstab approach offered by June is clearly a better method.

[NSearch]

Thank you both very much!!

June, orange just posted an example of the raw data. I was not aware of the pivot function -- that's great -- thanks for sharing!

[June7]

It's just a CROSSTAB query. It's an option on the ribbon query tools design tab.

Not sure what you mean by 'not being returned'.

Again, provide YOUR raw data.

[NSearch]

Testing.zipHow would I include rows that have N for all 4 values?

please see the attached sample

[June7]

Remove WHERE clause - I edited my earlier post to remove it.

[NSearch]

Remove WHERE clause - I edited my earlier post to remove it.

Perfect, thank you

[NSearch]

Testing_v2.zip

I thought I was finished with the query, but I need to add another flag.

The query below is where we left off. Would you please help adding in the additional flag. This time it is based off of a new field: charge_description. If charge_description contains the letters 'cwt' then set the CWT flag to Y.

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

Please see the attached file.

[June7]

Assuming CWT will never be in the same record as the other 4 values, which seems to be the case in the sample data:

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

[NSearch]

CWT may be in the same record as the other 4 flags.

Also, I need to identify if charge_description contains cwt. charge_description could be 'xxxx cwt', 'yyyyy cwt', 'zzzzz cwt'

If cwt appears in the text, the flag should be set to Y.

I tried the Like '*cwt' command, but it doesnt work.