Help needed: Consolidating Data with a twist (1 Viewer)

[kiwijules0505]

Hi All,

I have a table containing raw data customer which is at an individual customer level. Some of these customers have multiple individual accounts with us, but belong to the same group of companies. I want to create a summary table which shows the consolidated totals per group, but in the case of the one field, "SUSPENSIONFLAG", I want to combine any values that appear in the individual accounts into one field, separated by commas.

Note that if the "SUSPENSIONFLAG" field of the raw data table is blank, then nothing should be added in the "SUSPENSIONFLAG" field of the consolidated table

See example below.

Thanks for any advice you may have.

Raw data:

NameGroupCONSUMERIDBalanceSUSPENSIONFLAGBillingMonthBS PRINT (Wellington)BS PRINT2900641501104.68 B.SUSPMay-08BS PRINT (Auckland)BS PRINT290096100126.04 CREDITMay-08BS PRINT (Dunedin)BS PRINT290055100157.98May-08

Output required:

NameGroupCONSUMERIDBalance SUSPENSIONFLAGBillingMonthBS PRINT188.70 B.SUSP, CREDITMay-08

 

[kiwijules0505]

Apologies all - the format of my table didn't come across correctly. I'm attaching a file containing the raw data and also the required output.

Thanks again
Jules

 

[khawar]

Download the attached sample and open the query OutPut

Khawar

 

[kiwijules0505]

Thank you very much for taking the time to look at my problem - your solution works a treat.

Regards,
Jules

 

[kiwijules0505]

Hi Khawar,

After futher usueage of your code I've come accross an issue with company names containing the apostrophe character , e.g. Bob's Cafe.

Once the program encounter one of these, it produces the following error message "Runtime error 3075. Syntax error (missing operator) in query expression." Any ideas how you can alter the code to avoid this? Funnily enough, there's been a recent thread dealing with this, but I couldn't apply it to my situation.

Also, would it be possible the alter the code so that only unique suspension flags are combined? If, for example, all three instances of BS Print had "CREDIT" as its SuspensionFlag, then the consolidated field should only contain "CREDIT" and not "CREDIT, CREDIT, CREDIT"

Thanks again for your help.

Jules

 

[khawar]

Modified Sample

I modified the sample to solve both of your problems

made a little change in the module and created another query RawDataModified firstly to remove "'" from the group name and secondly not to repeat the suspension flag new query will be used for collecting flags instead of rawdata table and the result is in same query "Output"


Khawar