Results 1 to 3 of 3
  1. #1
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10

    Concatenate Query

    Ill try to make this as simple as possible. I have a couple different ways I'm going about doing a concatenate query, in order to display the data cleanly for the end user. We have Job #'s here that can contain many Part #'s, among other things. I have two (Many - Many) and one (One - Many) relationship tables setup to access the Part #'s and the others. The issue comes into play when I run any of the concatenate queries on them.

    The form that displays the data, is a continuous form made to look like a datasheet. The form has several criteria for sorting through the listing, such as things like Job #, Part #, Date Built, etc. Its a dynamic search form.
    I've tried to use Form.Filter, in a couple different ways, as well as setting the record source dynamically with the parameters. It all comes back to the concatenate part of the query though, as the main source of the slowdown.



    I've tried creating a temporary table created from the results of the concatenate query, although that is also rather slow and I have heard this is not optimal for a variety of reasons. I honestly am not sure how I should go about this. Multivalued fields are also not ideal, as I need to update parts of one of the many sides fairly frequently, and those also pose an issue with queries. If anyone could help point me in the right direction, I would definitely we willing to listen, as this is by far the main issue I am having at the moment

    ***Omitted other controls for shorter code. There is more than just a textbox to filter by
    Code for Dynamic Record Source
    Code:
    Public Sub Filter()
    Dim sql As String
    sql = "SELECT * FROM AllInfoQuery"
    
    SearchTxt.SetFocus
    If Len(SearchTxt.Text) > 0 Then
    sql = sql & " WHERE ((PartNo) LIKE '*" & SearchTxt.Text & "*') OR ((JobNo) LIKE '*" & SearchTxt.Text & "*')"
    End If
    
    UU_AdvancedSearchSubform.Form.RecordSource = sql
    End Sub
    Code for Form.Filter
    Code:
    Public Sub Filter()
    Dim strCriteria As String
    Dim var As Variant
    Dim i As Integer
    
    
    SearchTxt.SetFocus
    If Len(SearchTxt.Text) > 0 Then
    strCriteria = "PartNo & '|' & JobNo  like '*" & SearchTxt.Text & "*'"
    End If
    
    
    UU_AdvancedSearchSubform.Form.Filter = strCriteria
    UU_AdvancedSearchSubform.Form.FilterOn = True
    End Sub
    Concatenate Query for a One to Many relationship using DJoin from this gentleman here:
    Code:
    SELECT AllJobInfo.AutoNumber, AllJobInfo.PartNo, AllJobInfo.Location, AllJobInfo.JobReleaseDate, AllJobInfo.BuiltDate, AllJobInfo.MoreInfo, AllJobInfo.JobStatus, AllJobInfo.Working, AllJobInfo.Plant, AllJobInfo.Description, AllJobInfo.ImageField, DJoin("JobTable.JobNo","JobTable", "[JobTable].[JobID]= " & [AllJobInfo].[AutoNumber] & "") AS JobNo 
    FROM AllJobInfo;
    Concatenate Query for a Many to Many relationship using ConcatRelated from Allen Browne
    Code:
    SELECT AllJobInfo.AutoNumber, ConcatRelated("Designer.TestFullName","Designer INNER JOIN DesignerJUNCTION ON Designer.NameID = DesignerJUNCTION.NameID","DesignerJUNCTION.JobID=" & [AllJobInfo].[AutoNumber],"Designer.TestFullName",", ") AS DesignersFROM AllJobInfo;

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the issue is that ConcatRelated works but is slow? That is to be expected with large dataset. If concatenation is what you want, really don't see any way to make it faster.

    Last SQL is missing a space in front of FROM. I expect that is just a typo in the post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10
    Quote Originally Posted by June7 View Post
    So the issue is that ConcatRelated works but is slow? That is to be expected with large dataset. If concatenation is what you want, really don't see any way to make it faster.

    Last SQL is missing a space in front of FROM. I expect that is just a typo in the post.
    Yeah I had copied and pasted the SQL statement and changed the names for easier reading, I mustve missed that.
    I also used DJoin as well, to concatenate.
    I guess Ill just have to change the way the data is shown then. I figured it would be easy to have it all listed out front, but I may need to hide it behind a report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Concatenate Results of Query
    By bryan0 in forum Queries
    Replies: 3
    Last Post: 03-02-2014, 12:23 AM
  2. Replies: 2
    Last Post: 01-01-2013, 11:15 PM
  3. Concatenate records in Query
    By cleon in forum Queries
    Replies: 3
    Last Post: 04-08-2012, 11:14 AM
  4. Using a query to concatenate data
    By ocampod in forum Queries
    Replies: 15
    Last Post: 03-20-2012, 08:11 AM
  5. Concatenate Query Results
    By Rawb in forum Queries
    Replies: 6
    Last Post: 01-19-2011, 07:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums