concatenating multiple records to a single field within a query 2

[1DMF]

Hi,

Currently we have an export function which simply does a transferspreadsheet based on a saved query.

However their is a new 1-many relationship to another table that holds additional trading styles.

They want these multiple records merged to a single field appended to the spreadsheet.

Currently when the query is run it now creates mutiple results instead of one for each main DB record due to the additonal records in the trading styles table.

Is there anyway within the query design tool I can merge the mutiple records into a single string so I can then attach via an expression within the query?

hope that makes sense.

Cheers,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Hopefully someone chimes in on this. From googling I thought this was fixed using transferspreadsheet vice outputto.

 

[1DMF]

Thanks.

The problem is although the query expression can handle the string value, the export truncates it.

The MS work round seems to be...

Exported Query Expression Truncated at 255 Characters

Why does it sometimes seem the simplest of things are the hardest to do!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

You know the ironic thing in all this.

Now I have to create a temp table on the fly and turn my query into an append, then export the temp table and then remove the table.

hmmm, and who said I wouldn't have to rewrite the entire export function

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

Well it was quite easy in the end.

I changed the query to a 'create table' to generate the export table, then i edited the table and set the problematic trading style field to a memo field.

Then change the query to an append query.

Created a new simple select.* query against the export table and only had to add a few lines of code to the export function...

            sFile = cDrive & "Provider Notifications\" & sFold & "\AR MailMerge (" & SDate & ").xls"
            sQueryName = "AR Merge Export"
            
            're-written due to Trading Styles truncation problem (24.06.10 - BH)
            
            'empty AR_Merge table
            CurrentDb.Execute ("DELETE FROM AR_Merge WHERE 1=1")
            
            'run append query
            DoCmd.OpenQuery "AR MailMerge", acViewNormal
                                  
            'run export
            ExportExcel sQueryName, sFile

OK I've had to turn of notifications for MS access amend queries other wise you get an annoying popup, but it now exports as desired.

So again thanks for all your help Duanne & MajP

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts