The function works fine - although it is painfully slow. I've only got 75 records in my test data, and I'm looking at a good couple of minutes to run the query.
To explain: I've got a list (form) of records whose record source is said query. Where there are multiple people associated with the records, I need to show them as a string, i.e. instead of Record1 - PersonA, Record1 - PersonB, Record1 - Person C as 3 records, I need to show Record1 - PersonA, PersonB, PersonC.
To compound the problem, when someone views one of these records, they may add PersonD to Record1, so I have to requery the list form to reflect this and other changes. Furthermore, I am also using this query as a source for other queries, which feeds other forms, and each one is taking just as long to open.
What I have tried is running a query that looks for records with multiple people first and then using that as the source for the concatenate query, but this hasn't made any difference.
I'd love to hear some suggestions. This is a major performance hit, and I know the users aren't going to like it... RuralGuy, are you out there!
Here is a different way to do this, BUT, there are MANY limitations:
Using a X-Tab query we can get the values in columns, and then use a 2'nd query to concatenate the column values as you want.
So using your example specifically: And please bear with me as it does work !!!!!
First (Limitation #1) you will need already to know in advance the values of your data of PersonID's. To do this you can run a unique record query of PersonID's just before running your X-Tab query. This will predefine your column headings output for your X-Tab.
Secondly (limitation #2) the number of records returned (i.e. CountOf Unique PersonID) result of this must be less than 255 as a X-Tab cannot cater for more than that. (you only have 75 records, let alone unique users so you are fine so far)
Thirdly (limitation #3) a string concatenantion of the (UserID Unique Characterlength + 3) * CountofUniqueUserID's must be less than 2049 characters - will explain further on...
If you can guarantee condition #2, then we can work out better ways to deal with #3.
In my example here, CONC = Table, REC = Record Field, PER = Person Field
SO Create a X-Tab Query:
TRANSFORM IIf(IsNull(First([PER])),Null,First([PER]) & ", ") AS PERSNAME SELECT REC FROM CONC GROUP BY REC PIVOT Right([PER],1);
And in the Query Properties FIX your column headings as "A","B","C","D","E","F", .......(The total string length must be less than 2049 characters)
This will give 1 Column per potential person for your records. Now we need to concatenate, so we write another query to do this with a great big formula that uses [A] & & [C] & [D] & [E] etc. and a 2'nd formuala that refers to this but strips off the last comma.
Yes, this makes a lot of assumptions, but it works. Your userID's may not be different per last character etc. but the concept works fine.
And you can write VBCode to generate your "A", "B", "C"... and your [A] & & [C]....and modify your query from the results of the unique recordset of PersonID's.
If you can post a table that has the potentail values i.e more realistic data, then I can set it up for you if this needs more clarification.
Thanks for your response. Unfortunately, I think that the limitations of your method are too restrictive. I anticipate that the number of records is going to reach four figures within a few months, at which point the method would fail.
I am relatively happy with how my current method works, i.e. I understand most of the function and the results are as expected. I just think that I need something that will make the concatenation function only operate on the relevant records only, rather than the whole set of data.
Does anyone have any other suggestions? I'd be really interested to hear from anyone else that has used this method before too.
You've answered your own problem - Why can't you just reduce the Allen Browne recordset that the users need to requery for:
Create a new True/False field in your main table that holds your records, call it "Processed" and ensure all values are set to False. And a memo field that holds the query and concatenated values you want to run.
Set the Allen Browne dataset to only process records where Processed = False. After you have Processed all records, set Processed = True and the results are populated into the memo field.
When your users either add a record, modify a record, or delete a record in your "Many" table, go set that individual record value to Processes = False.....
Now when Allen Browne Processes it will only process modified records.
Without understanding or seeing your app (perhaps you could explain the process flow a little more) , I would personally make it totally dynamic - i.e. only on a need to know basis. Don't do everything up front "just in case". Only when the query needs to be run, then run it, and don't use AllenBrowne.
Just return your record set for that one query only, concatenate the values from the table only relevant to that single record with multiple users, and run the query.
A very simple example:
Code:
dim rs as recordset
dim perss as string
'Open the "many" table PERSRECS and only get values where REC_ID is the same as that of field FromRecID on your mainform.
Set rs = CurrentDb.OpenRecordset("SELECT * FROM PERSRECS WHERE REC_ID = " & Forms!f_MainForm!FormRecID & ";")
'Loop until the end of the data returned (if any)
Do While Not rs.EOF
'Start concatenating PERS_ID's
perss = perss & rs!PERS_ID & ", "
rs.MoveNext
Loop
'Strip off the unnecessary last comma and space
perss = right(perss, len(perss) - 2)
Now use perss in your query to return results.
Only a "need to know basis" e.g. OnCurrentRecord, or OnSelectComboBoxChoice. Yes it may be marginally slower interactively for the user (probably won't notice), but acceptable I would think.
Have you also checked indexing etc to see why AllenBrowne is so slow?
Also - what are the queries you are running and why, perhaps looking at the app from a different angle may throw a different light on why your doing what you are - maybe the function can work differently?
I always have plenty of ideas - it's just finding the most appropriate - so if you tell me a little more we will find a good solution.
Thanks a million for getting back to me on this one.
Yes, it sounds like this could potentially be the solution that I'm looking for. I can somewhat follow your suggestion, although must confess that I am an Access novice in many respects.
Would it be reasonable to upload my database so that you can take a look at the VBA and queries?
In a nutshell, you have a form that shows a list of all 'cases'. Where a case has multiple 'suspects' (I work for the police), I need to display each suspect name in a field on this form. In order to do so, I have to run the Allen Browne query. The thing is, whenever someone modifies one of these cases and then returns to the case list form, I have to requery the form. Given that there will be up to 8 concurrent users, this requerying is going to happen many times over the course of a day. There just must be a more efficient way of doing it!
Absolutely - Please load and I will have a look - obviously remove sensitive client data. If you can make up some fictitious data and populate a few - just to give me an idea, + number of records you expect (roughly) per table then I can randomise data, populate and test.
Don't forget to compact database first and second, either rar or zip. If still too big will give you eMail address.
i've not looked at your solution, but you need a query to select the unique records in which you are interested.
so you get
Bill, 1
Jim, 2
Fred, 2
Mike, 3
now you need a column with a function
extendedlist: myconcat(usename)
this will run a function called myconcat for the current usename, which will examine all the records for the usename and return a string formatted as you want.
this will be quite an advanced function to write (but it may be part of the solution you already have. (I just thing its easier to do the concats as a separate function
Be sure to change the locations in the shortcut, to hold down Shift when opening and to update the linked tables - of course, you knew that already...
The queries in question:
qry_Get_Multiple_Suspects - this is to find those where there are multiples
qry_Get_Multiple_Suspects_Names - to find the associated names
qry_cases_with_multiple_suspects - this runs the concatenation function
The form frm_Case_List is the problem form. Double-click any case to view the details. When you close this form, the frm_Case_List requeries.
I know that the solution is to limit the recordset to just those found by qry_Get_Multiple_Suspects, but the execution of that is beyond me.
I had a good look at your database, I assume your users are insistent that they see all suspects per case on the main screen....perhaps you oversold or raised expectations a little high. It's not the best method to show multiple data (e.g. dropdownlist)....However:
One way we can do this more speedily (for the users anyway), is to note when the data will change, and only concatenate when those changes take place. In other words, the users that change suspect data / case data will pay the penalty of "waiting" for an individual concatenation change rather than those who have to open the form and wait for it to compile.
The changes as I have seen them are as follows:
Users change a suspect connected to a case (i.e they change the suspect to that of a different suspect from the dropdownlist.
Users delete a suspect connected to a case.
Users add a suspect to a case.
Users Modify the surname of a suspect.
Users Delete a suspect.
And there may be more.........
The main thing I have done is to write a small concatenate function which works almost exactly like the one you have anyway - which works per case ID. I have also written 2 more functions which call the new concatenate function: One that concatenates every case value (should you ever wish to do so), and one that uses a suspect ID, and finds their relevant cases, and concatenates those - i.e per Suspect (as a Suspect may have many cases.)
You can paste the following straight into a new Module:
Code:
Option Compare Database
Public Del_Conf As Long
Public Chn_Conf As Long
Function ReConc(C_ID)
Dim rs_Join As Recordset
Dim perss As String
'Open the "many" table tbl_CS_Junction and only get values where CASE_ID is the same as that of passed parameter.
Set rs_Join = CurrentDb.OpenRecordset("SELECT tbl_Suspects.Suspect_Surname FROM tbl_Suspects INNER JOIN tbl_CS_Junction ON tbl_Suspects.Suspect_ID = tbl_CS_Junction.CS_Suspect_ID WHERE (((tbl_CS_Junction.CS_Case_ID)=" & C_ID & ")) ORDER BY tbl_Suspects.Suspect_Surname;")
'Loop until the end of the data returned (if any)
Do While Not rs_Join.EOF
'Start concatenating PERS_ID's
perss = perss & rs_Join![Suspect_Surname] & ", "
rs_Join.MoveNext
Loop
'Strip off the unnecessary last comma and space
If Len(perss) > 2 Then
DoCmd.RunSQL ("UPDATE tbl_CASES SET Case_MeConc = """ & Left(perss, Len(perss) - 2) & """ WHERE CASE_ID = " & C_ID & ";")
End If
End Function
Function ReConc_All()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tbl_Cases")
Do While Not rs.EOF
ReConc (rs![Case_ID])
rs.MoveNext
Loop
End Function
Function ReConc_SusNameChange(SUS_ID As Long)
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_CS_Junction WHERE CS_Suspect_ID = " & SUS_ID & ";")
Do While Not rs.EOF
ReConc (rs![CS_Case_ID])
rs.MoveNext
Loop
End Function
Your frm_Case_List uses qry_Case_List which in turn calls your old concatenate function. I have added an extra memo field to your table called Case_MeConc. We will store the concatenated values here - regardless of multiple suspects - i.e even if there is only a single suspect, we will still store the single suspect. Your query qry_Case_List must now reference this memo field rather than the old concatenate function. Currently this will return empty values as we haven't concatenated anything yet. You may want to place a button on the frm_case_List that runs the ReConc_All function so the users can "update" if they want, but shouldn't be necessary. You may want to run it first time so that memo field is initialised.
Now we need to find the places where users can effect a concatenation change, so lets look at the form frm_CS_and_Suspects_Subform.
Here suspects can be added, modified, deleted from cases. I struggeld with the delete option as the delete is only carried out very late in the event sequencing so what I have done is to set an indicator that holds the Case_ID if suspects are modified, deleted, or added.
Code:
Private Sub CS_Suspect_ID_AfterUpdate()
Del_Conf = CS_Case_ID
End Sub
Private Sub Form_AfterInsert()
Del_Conf = CS_Case_ID
End Sub
Private Sub Form_Delete(Cancel As Integer)
Del_Conf = CS_Case_ID
End Sub
Then when the users close the parent form - frm_cases, we check the placeholder to see if it has a Case_ID value - i.e have the users modified a case by changing suspect data. If they have then we run the concatenate function only for that case, and then requery the main form frm_Case_List to see the effect.
Code:
Private Sub Form_Close()
If Del_Conf <> 0 Then
ReConc (Del_Conf)
Del_Conf = 0
End If
Forms![frm_case_list].Requery
End Sub
And that works fine. Now we have to find other places where users can update Suspects - e.g what if they change the suspects surname? Or move a suspect from one case to another? (currently not working though)
frm_Suspect_Details has the field Suspect_Surname. So we can do a similar thing for changing surnames except this time we store the suspectID and not the CaseID, and run the function ReConc_SusNameChange which in turn gets all the suspect cases and in turn runs the concatenate function for the cases:
Code:
Private Sub Suspect_Surname_Change()
Chn_Conf = Suspect_ID
End Sub
and do a similar thing as earlier when closing the form:
Code:
Private Sub Form_Close()
If Chn_Conf <> 0 Then
ReConc_SusNameChange (Chn_Conf)
Chn_Conf = 0
End If
End Sub
Now you get the idea, this works just fine so you may have to find more places where users can change data. Also the other thing you will need to sort out is when users want to see their screen updated. I.e I can open up the main screen, and see suspects per case. Someone else has added a suspect to a case and the data has been stored and concatenated in tbl_cases, but my screen does not show the update yet - ie. my screens needs refreshing / requerying (NOT Reconcatenating though). (See previous use of Forms![frm_case_list].Requery)
I don't know your business process flows so I can't tell you when you should do this - maybe have another button (else [Shift][F9] works just fine).
I feel like i've missed something, so if you struggle with the above as it doesn't work, let me know...
I would also be interested in how much quicker it takes to run the function Re_ConcAll (if at all)
Finally, if you start to get data conflicts where a user is updating a case, and another is unknowingly running the concatenation funtion (which also updates the memo field in the same table), then I suggest you create a new table with only Case_ID and concatenation field and set as a 1:1 relationship with tbl_Cases. So we concatenate to the new table instead. However for each new case we have in tbl_cases, we will have to have a new one in the new table - but that we can check etc and place in function if we require - it's quite easy. If you need to do this let me know and we can do that also.
Well, it worked like a charm! The difference in performance is massive - I implemented the changes everywhere that you suggested i.e. whenever suspect details are changed, and it no longer takes forever and a day to open the case list form! I knew that there would be a solution to this, but I can't thank you enough for helping me to get there so quickly.
As for the data conflicts, well, I'll cross that bridge when I come to it. To be honest, it is not that frequently that the concatenation will need to be ran, so I don't think that it will be a huge problem. I am considering making it so when a case is entered, the case list form closes and is reopened when the case form is closed - it loads so quickly now, this shouldn't really affect performance at all.
Incidentally, when I ran the ReConc_All function, I didn't notice much difference in speed compared to the Allen Browne concatenation function, but that's somewhat inconsequential since there's no longer a need to run the function against all of the data!
Really, thanks so much for your help - you clearly went to some trouble to look at my database and the solution that you cam up with was perfect. If I'm ever in South Africa, I'll buy you a pint!
And to anyone else reading this thread, I can vouch for this solution working well where concatenation of records is desired.
. It's not the best method to show multiple data (e.g. dropdownlist)....However: