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

 

[dhookom]

You should probably search the FAQs on this site prior to asking. faq701-4233 is one possible solution.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

yeah, but no, but!

I appreciate the reply and understand the code, but I was hoping I could do something without VBA!

Hey would you adam & eve it, just when I want to do something without VBA, it seems that's the only solution.

The problem is that the routine for exporting the membership list is a simple 'transfer-to-spreadsheet' using a query as the source.

If I can't somehow manipulate the query to merge the records, i have to completely re-write the entire function.

Where as the simplest option is to re-write the way i store the mutiple trading styles, currently on a 1-many relationship with the master record!

Instead I will have to add a memo/text field to the main master record db and the user will have to manually enter them as a comma separated or similar value, that way the current 'export membership' code will continue to work as it is as well as provide the required additional multi record data will be provided, just by adding the additional field to the query!

there goes my master/child form I just designed ;-)

"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]

This is contradictory

If I can't somehow manipulate the query to merge the records, i have to completely re-write the entire function

- If I read the above it says if you can manipulate the query to concatenate the records you are OK.
- The function provided manipulates the query to concatenate the records
- Therefore no problem

Am I missing something in the logic.

 

[1DMF]

yes manipulate it but within the query itself.

like you set an expression and use IIF for other dynamic data creation based on criteria.

"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]

Using an IIF is really no different than the provided function. IIf is not a native SQL function but a vb function. Only difference is using a UDF versus

You might be able to do this in SQL using nested queries, but I never seen it demonstrated. It would be much more complicated I think.

 

[1DMF]

Only difference is using a UDF versus

what does that mean?

However, it seems I was right in thinking the only way was with VBA nad that re-writes a major function, when another memo field does the job in half the time!

damn it!

"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

 

[dhookom]

You can see how the Concatenate() function works in a sample database at Generic Function To Concatenate Child Records

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Sorry for not being clear. A UDF is a "user defined function." I just thought that it was illogical that you wanted a solution that used a native vb function such as an "iif", but you were not willing to use a UDF because it required a major rewrite. That does not make any sense to me.

I did not say that that vba is the only solution. I said the opposite. I think there is likely a pure sql solution that can be done to concatenate child records using nested queries. I think the solution is probably very complicated. I have seen demonstrations of nested queries to do recursive bill of material calculations. The majority of Access developers will stick with a vb solution.

I have no idea what you mean "another memo field does the job in half the time." That is a meaningless statement. Half the time of what? Based on what? If you are not working with tens of thousands of records, I would not worry about the time.

I still can not see what the issue is. You simply build the query. Add the function in a calculated field, and you are done. There is no "major rewrite" of anything involved. I would recommend going back and rereading the thread, it is very commonly used.

If you are dealing with thousands of records, you are correct that it will not be very fast. In that case you may want to build a temp table based on the results.

 

[dhookom]

I have done a pure Access SQL concatenate into a memo field. I think I added a memo field to the Product Category table in Northwind and the JOINED it to the Products table updating the memo field with the memo field plus the product name field.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Whoa MajP , youy've gone way over my head, what is UDF.

Are you saying that I can with in the query place a function name of a funtion i have written to somehow merge the multiple records together as an expression?

sorry if you didn't understand my other bit's adding a memo field i thought was self explanitary, but I guess not.

what I mean is adding another field of type memo to the main table instead of having a one-many link to the separate records, so the user then simply comma separates a string in a memo field.

does that make more sense?

I'm also sorry you don't understand when I say 'save time', it means not having to re-write an exisiting function to now handle multiple records due to the 1-many relationship.

The current code is simple and very short, no record sets or looping or manipulating before exporting. I am using the native 'export to spreadsheet using this query' method.

As that query now generates multiple records due to the 1-many relationship, that entire funciton used for many purposes by many departments now no longer works.

re-writing that function, would take longer than scapping the 1-many table and adding a memo field to the master table.

is that any clearer?

I still don't fully understand how this UDF works, I'm re-reading duane's code, but i'm a little lost how it works, which makes it cargo cult!

Am I to take it the concaternate function runs for each record retrieved in the query, which is why you say for many records it can become a problem?

hmm, I think I understand, bit hardcore for first thing on a Monday moring!

I'll go have a play and see if I can write my own function.

I take it it has to be a function not a sub and you set the function name to the final string as that is what is returned back to the query as the result?






"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]

Thanks Duane,

finally got my head round the code and came up with my own solution...

Function CSV_QueryCat(ByVal sField As String, ByVal sTable As String, ByVal sID As String, ByVal iRec As Integer)

    Dim rs As Recordset
    Dim sSQL As String
    
    CSV_QueryCat = ""
    
    sSQL = "SELECT " & sField & " FROM " & sTable & " WHERE " & sID & " = " & iRec
    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)

    If rs.RecordCount > 0 Then
        Do While Not rs.EOF
            If (CSV_QueryCat <> "") Then
                CSV_QueryCat = CSV_QueryCat & ", "
            End If
            CSV_QueryCat = CSV_QueryCat & rs.Fields(0)
            rs.MoveNext
        Loop
    End If
    
    Set rs = Nothing
    
End Function

OK it will only work with those tables that use integer primary keys, but most of the tables are so I think this will do what I want for the forseeable future.

Again many thanks Duane for the inspiration!

"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

 

[dhookom]

I'm not sure why you didn't just use the Concatenate function as it is. Congrats on writing your own though ;-)

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Hi Duane,

A few reasons I didn't use your version.

1. had to keep copyright notice, which I didn't want in my application (please don't take offense!)

2. had lots of unneeded code relating to different data access methods, and so saved me deleting the wrong bit of code by mistake.

3. I didn't fully understand it and if there is one thing TT has taught me, it's to not use cargo cult programming.

I understood the priciple, once I got my head round it, and so from there as you can see, it took 5 minutes to roll my own solution.

This way I feel I have learnt a lot more and gain far more than if I'd just blindly shoved your code into my application.

Give a man a fish, he'll eat for a day, teach a man to fish, he'll eat for a lifetime ;-)

So thank you Duane for your life lesson





"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]

Just to summarize and answer your questions

1. VBA provides native functions (like Date, format, msgbox, iif, etc.). Users can write their own functions in code called user defined functions (UDF). Access allows these functions to be used in Sql code

2. The original FAQ is a UDF that you can use in a query to concatenate multiple child recods into a new field. I do not understand the confusion, clearly that is was the FAQ states

Are you saying that I can with in the query place a function name of a funtion i have written to somehow merge the multiple records together as an expression?

3. Could you add a memo field to force the user to add the data in a single field? Yes you could. Normally this is considered very bad database design and it is called non-normalized data. Once you add data in this method you can do very little with it. You can not query, sort, enforce data entry rules, etc.

sorry if you didn't understand my other bit's adding a memo field i thought was self explanitary, but I guess not. what I mean is adding another field of type memo to the main table instead of having a one-many link to the separate records, so the user then simply comma separates a string in a memo field.

So I fully understood this; however, it seemed like a such a really bad idea since you already had a proper database structure and a solution that would work.

4.

I'm also sorry you don't understand when I say 'save time', it means not having to re-write an exisiting function to now handle multiple records due to the 1-many relationship.
The current code is simple and very short, no record sets or looping or manipulating before exporting. I am using the native 'export to spreadsheet using this query' method.
As that query now generates multiple records due to the 1-many relationship, that entire funciton used for many purposes by many departments now no longer works.
re-writing that function, would take longer than scapping the 1-many table and adding a memo field to the master table.
is that any clearer?

No. As you stated it took five minutes to rewrite Duane's code or you could have just used it as is.

 

[1DMF]

Thanks for the reply MajP,

I never realised you could have UDF, I dunno why just assumed you could only use the built in functions in that manner, but I'm glad I've found out differently now!

I still don't think you understand the point I was making about the function re-write, but it doesn't matter as I got there in the end with out needing to write a different export function now that I can concatenate from within the query itself.

On a side note, the memo field was an explicit request from the user, so bad design or what ever is irrelivant.

Mine is not to reason why, mine is just to do or die!

If I'm given a request, unless I see something seriously flawed with it or it not being possible, I'll do as I'm told!

When a Director of a company says that any request you get from a certain member of staff MUST be done and with utmost priority over anything else, you just do it, regardless, and if what was requested is a load of rubbish, not my problem!

However, I still decided to ignore the request and create a 1-many relationship, which at first seemed to bite me on the behind when I then ran the query, so going back to a memo field would really just be doing what I was asked to do in the first place. And would have been done a lot quicker!

I don't see why you can't do stuff with a memo field at a later date, as long as it is agreed what the data separator is, manipulating the data wouldn't have been a problem.

But I agree the way I have it is better, hence me doing it this way, I just missed a small piece of the puzzle which yourself and Duane were kind enough to provide.

So thanks to both of you!




"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]

FYI. Although most purist, would stick with the one-to-many structure Access 2007 has actually introduced multi-value fields.

http://office.microsoft.com/en-gb/access-help/guide-to-multivalued-fields-HA001233722.aspx

This actually stores the data behind the scenes in a normalized manner. So you can do things like query a specific value inside the concatenated group.

The original post made it sound that you already had a lot of data in a 1-many relationship and you were trying to concatenate the existing data. The memo field did not seem like an easy solution because that only would allow future input, but not help in concatenating the existing data.

 

[1DMF]

Cool, that would have been just what I wanted for this purpose, is it MS Access which handles this and the backend SQL just sees a standard ntext memo field which is comma separated, a bit like what I was going to do?

Or do you need to have a different version of MS SQL 2000 to do this?

The original post made it sound that you already had a lot of data in a 1-many relationship and you were trying to concatenate the existing data. The memo field did not seem like an easy solution because that only would allow future input, but not help in concatenating the existing data.

Yes, perhaps that was part of the confusion, this was a new addition/feature request. Previously we didn't store this information so it was a brand new filed/table with currently no data.

Here is the actual user's request

Firstly, I need the Database to record details of any trading names that an AR may use.

Basically we need something on the front screen of the AR record which would allow us to put these in. This should probably be a free text field so that the different names can be added without it using up hundreds of database fields.

Sorry if I confused you with my poor explanation!

We got there in the end and with the best solution IMO , I wish I had split out the current master table into more relational tables than we currently have.

However hind sight when it comes to DB design is of no use to anyone, refactoring code and tables isn't easy once you have a production system in place with thousands of lines of procedural code relying on the data being in a particular table.

As I never designed the original database, and have only developed it, I don't feel too bad!

I do try to do things right and with the great help on TT, I'm usually pretty close





"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]

I've hit a snag with this concatenation method.

When the query is used in a TrasferSpreadsheet export, the TradingStyles column concatenated via the UDF is being truncated at 255 chars.

Is it the query expression causing the truncation or the export and how do i get round this problem?

"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]

http://allenbrowne.com/ser-63.html

Read the section on Export.
This has nothing to do with the function but on the issue with exporting. I am not sure what the work around is.