Question Search form with one-to-many concactenated fields

[NeutronFlux]

Hello. I've created a database of my company's suppliers (~400-500 entries). It has a continuous form (SupplierList) to search through the list of suppliers, along with search tools (keywords, categories, etc.). Originally, I worked off a flat table from Excel with suppliers in the format,

Name | Category | Contact 1, 2, ... | Phone # 1, 2, ... | Email 1, 2, ... | Website

I also displayed the items like this in SupplierList. The form is meant to allow employees to quickly browse through our suppliers and their main contact information. More details and options would be presented by clicking on a supplier in the list.

At some point, I converted my tables into relational tables. In this case, I have tables called Suppliers and Contacts setup as

Table [Suppliers] 
Supplier_ID, Name, Category and Website

Table [Contacts]
Contact_ID, Supplier_ID, Contact_Name, Phone, Email

This benefits other parts of my application, but it makes it difficult to display a supplier with multiple contact information on form SupplierList. Through queries alone, I would either end up with repeating suppliers on the list or one contact information per supplier. I've used Allen Browne's ConcatRelated function, to get around this problem. But the performance hit is unbearable, especially when I add in search criteria. I believe it re-runs the function again for every contact field each time the query is accessed. For instance, using the function directly in the query would cause the program to freeze for about 10 seconds (on a very powerful computer) each time the query is accessed (query is to hide some records such as suppliers that have not yet received approval). Placing the function in the form elements gives better performance when displaying an unfiltered list, calculating only for rows currently visible on screen, which takes about 2 seconds each time I scroll without freezing. But, adding in search criteria (form filter) freezes the program for about 10 seconds.

To summarize my problem, I would like to have my one-to-many Supplier-Contact records displayed on a continuous form, showing one line per supplier with all that supplier's contacts on the same line, like what Allen Browne's ConcatRelated does. But, I would like to be able to display and filter these results without having to wait more than 2 seconds every time the form re-queries.

Thanks.

 

[spikepl]

For inspiration:

I have, like you, a situation with parent info and child info. I display it in a datasheet. To avoid repeating the parent name, I made a function, which displays a blank if the parent ID in one record is the same as in the preceding record. I use the Static declaration in the funciton, so the thing remembers the ID from previous record and compares with the one of the current record. This works, but can give some weird effects in Access2007.

Public Function ShowQuestionOld(MyQuestion As String, MyType As String, MyQuestionID As Long) As String

    Static oldID As Long

    If MyType = "DropDown" Or MyType = "DropDownList" Or MyType = "MultiSelect" Then

        If MyQuestionID <> oldID Then
            ShowQuestionOld = MyQuestion
        Else
            ShowQuestionOld = " "
        End If
    Else
        ShowQuestionOld = MyQuestion
    End If

    oldID = MyQuestionID

End Function

 

[NeutronFlux]

For inspiration:

I have, like you, a situation with parent info and child info. I display it in a datasheet. To avoid repeating the parent name, I made a function, which displays a blank if the parent ID in one record is the same as in the preceding record. I use the Static declaration in the funciton, so the thing remembers the ID from previous record and compares with the one of the current record. This works, but can give some weird effects in Access2007.

Public Function ShowQuestionOld(MyQuestion As String, MyType As String, MyQuestionID As Long) As String

    Static oldID As Long

    If MyType = "DropDown" Or MyType = "DropDownList" Or MyType = "MultiSelect" Then

        If MyQuestionID <> oldID Then
            ShowQuestionOld = MyQuestion
        Else
            ShowQuestionOld = " "
        End If
    Else
        ShowQuestionOld = MyQuestion
    End If

    oldID = MyQuestionID

End Function

Thanks for the reply. It's an interesting approach for me to consider.

In my case though, the reason for me keeping things to one line isn't just a grouping issue, but also to try to minimize the space needed. Some of the computer monitors at my company are small CRT's/laptop screens at 640x480 or 800x600 resolution. Many of my co-workers also aren't too young, so they will complain if I reduce text size.

But, if my desired solution is not possible on Access, I might adopt this solution and see if I can re-arrange some of the other elements on the form to create more space. Or, I might finally start migrating stuff from Access over to a web platform and MS SQL.

 

[spikepl]

minimize the space needed.

Exactly - and rather scroll up/down thanleft/right. Becasue it deals with

Through queries alone, I would either end up with repeating suppliers on the list

 

[spikepl]

And Plan-B: Add a field with the concatenated stuff to the original table. Yes it will be denormalized, but so long as you reconcatenate automatically after any change, there should not be a problem.