Storing one to many records but displaying the manies in one row

[thechazm]

So I have been doing some investigating but have not found a good solution to my current problem. I have two tables.

The first table stores jobs
The second table stores different statuses on the job

Its a one to many relationship but for meetings and other purposes I need to show all of the relavent statuses in one row with the current job.

For example:
Job #1 - A01 - WCT
Job #1 - A02 - RCT
Job #1 - A03 - Null

I would like to somehow do this:
Job #1 - A01(WCT), A02(RCT), A03(Null)

I have tried and used concatinating functions that I have seen and have also developed my own the problem is the functions run to slow and make the interface extremely hard to use.

Any input on this or idea's would greatly be appreciated as I have about 5K or so jobs I need this to work on.

Thanks,

TheChazm

 

[AccessMSSQL]

You should be able to use a crosstab query for this.

 

[spikepl]

If specific status records do not change, but only new status records are added each time, nothing prevents you from adding the status to a concatenated string (kept in a table) at the time when that status is added.

As to "the functions run to slow and make the interface extremely hard to use": if you do not show what you have done then it is not possible to advise on it.

 

[thechazm]

I thought about using a crosstab but unfortunatly I can't see how a crosstab query will resolve the issue. If you can enlighten a little bit I would appreciate it.

Spikepl:
Understand all as far as not posting the code but I feel that's the problem trying to run vba in an active interface accross not just one but 7 fields I need to concatinate is the problem. Plus the amount of records. Since vba is single threaded it just can't keep up on the systems that we have at work.

Also the statuses for each code will/can change because its seperate parts of the primary job/work.

Here is one of the functions I have tried to use from a reputable person:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'    Dim rs As New ADODB.Recordset
'    rs.Open pstrSQL, CurrentProject.Connection, _
'    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(2) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

Another I have tried was from Allen B. and like I said plus my own. It's hard to say but I just feel like the computers just can't process the information fast enough to display the data in a timely manner using vba, thus my comment.

 

[spikepl]

I am not convinced that your computer cannot keep up. Code can be written such that it can stump ANY computer. But also, code can be written such that it executes most efficently. And your probem description does not indicate any HUGE problem, so I suspect the coding could be improved.

The function you listed shows how to concatenate data for one ID - if you use this for your problem, and run through 5000 ID's , then no wonder it is slow.

Show the entire code.

Actually, all the concatenation could be done in one pass without any searching at all. But not by using the above function.

 

[jdraw]

What was the issue with Allen Browne's function??

You may have a design issue if you're finding the current desktops/PCs are too slow.
Perhaps you could show us your tables and relationships, and/or business facts and some code.
You're not the first to say the machines are too slow, but I sort of doubt that. Now it could be that Access isn't the tool for the job????

 

[thechazm]

Guys that's why I ask the question and I appreciate the inputs so far.

So I'll try and post as much relavent information that I can to show what's up but the computer sets at 50% the entire time which is taking up one of the two cores available on the machine. That is why I believe the function in vba not to be adequate and my network bandwidth is hovering around 0.

Anyway so here goes. First I'll post a picture of my relationships which indicates how it is normalized.View attachment Relationships.pdf

While you are looking at that I will post the rest of the information.

Thanks

 

[boblarson]

First I'll post a picture of my relationships which indicates how it is normalized.View attachment 44609

I hate to break it to you but it does not look like it is properly normalized. The key word here is properly.

Also, you should NOT use ID as the ID field name for every table. Is there a reason why you chose to do that?

And, you have a bunch of date fields for the Job table but they shouldn't all be in there, in reality. You should be having a table which stores the typeID and the date for a particular type of date.

One more - it would appear that you have not set any referential integrity so it isn't clear what is a one-to-one relationship and what is a one-to-many.

 

[thechazm]

Spikepl:

As for the vba code that was it because all the other functions operated the same way and gave no better results anyway so displaying them would just be a waste but here is my SQL that I am currently using to display the data. And the code earlier I posted on any of the one to many fields like KEOP or whatever I would use it on and its just to slow. I tried to figure out ways of doing it with SQL but no luck there. It's a lot of data and I have went round and round with other ideas. I also thought about just flatlining the data when they are going to show it in a meeting so all the data would take a few minutes to build but afterwards the interface should be fast since the data is non-normalized but my issue with that is they want to make changes then I would hate to have to rebuild the table every time a change took place. I could possibly setup a secondary function to work on the individual/unique record that gets modified but then that means I would have to tag every job with some kind of boolean tracker and maybe even the many tables. I dunno just thoughts.

SELECT Jobs.[Man CANX], 
Jobs.[100 Hour Plan], 
[Special Status PickList].Priority, 
Jobs.[SAIL Work], 
Jobs.[Contract Work], 
Jobs.JCN, 
Jobs.[Safety of Ship], 
Jobs.[Job Summary], 
Jobs.[Special Status], 
Jobs.[Start Date], 
Jobs.[Stop Date], 
Jobs.[On Schedule], 
KEOPs.KEOP, 
KEOPs.[WKG Status], 
[Zone Managers].[Zone Manager], 
Materials.Material, 
Last(Comments.Comment) AS LastOfComment, 
Jobs.ID, Jobs.[Avail ID], 
Jobs.[Modified Start Date], 
Jobs.[Modified Stop Date], 
Jobs.[Test Date], 
Avails.Avail, 
[Ship Names].[Ship Name], 
[Ship Names].ID AS [SHP ID], 
[CU Phase Approvals].[CU Phase Approval], 
ICNs.ICN, 
SWLINS.SWLIN, 
[Package Statuses].[Package Status], 
[Jobs Support Table].[Released Date], 
[ZM PickList].ZM, 
Jobs.[PMA Approval], 
[Special Status PickList].[Special Status]
FROM ((((((([Ship Names] INNER JOIN (Avails INNER JOIN ((((Jobs LEFT JOIN KEOPs ON Jobs.ID = KEOPs.[Job ID]) 
LEFT JOIN [Zone Managers] ON Jobs.ID = [Zone Managers].[Job ID]) 
LEFT JOIN Materials ON Jobs.ID = Materials.[Job ID]) 
LEFT JOIN Comments ON Jobs.ID = Comments.[Job ID]) ON Avails.ID = Jobs.[Avail ID]) ON [Ship Names].ID = Avails.[Ship Name ID]) 
LEFT JOIN [Jobs Support Table] ON Jobs.ID = [Jobs Support Table].[Job ID]) 
LEFT JOIN [CU Phase Approvals] ON [Jobs Support Table].[CU Phase Approval] = [CU Phase Approvals].ID) 
LEFT JOIN SWLINS ON [Jobs Support Table].SWLIN = SWLINS.ID) 
LEFT JOIN ICNs ON [Jobs Support Table].ICN = ICNs.ID) 
LEFT JOIN [Package Statuses] ON [Jobs Support Table].[Package Status] = [Package Statuses].ID) 
LEFT JOIN [ZM PickList] ON [Zone Managers].[Zone Manager] = [ZM PickList].ID) 
LEFT JOIN [Special Status PickList] ON Jobs.[Special Status] = [Special Status PickList].ID
GROUP BY Jobs.[Man CANX], 
Jobs.[100 Hour Plan], 
[Special Status PickList].Priority, 
Jobs.[SAIL Work], 
Jobs.[Contract Work], 
Jobs.JCN, 
Jobs.[Safety of Ship], 
Jobs.[Job Summary], 
Jobs.[Special Status], 
Jobs.[Start Date], 
Jobs.[Stop Date], 
Jobs.[On Schedule], 
KEOPs.KEOP, 
KEOPs.[WKG Status], 
[Zone Managers].[Zone Manager], 
Materials.Material, 
Jobs.ID, 
Jobs.[Avail ID], 
Jobs.[Modified Start Date], 
Jobs.[Modified Stop Date], 
Jobs.[Test Date], 
Avails.Avail, 
[Ship Names].[Ship Name], 
[Ship Names].ID, 
[CU Phase Approvals].[CU Phase Approval], 
ICNs.ICN, 
SWLINS.SWLIN, 
[Package Statuses].[Package Status], 
[Jobs Support Table].[Released Date], 
[ZM PickList].ZM, 
Jobs.[PMA Approval], 
[Special Status PickList].[Special Status]
ORDER BY Jobs.[Man CANX] DESC , 
Jobs.[100 Hour Plan], 
[Special Status PickList].Priority DESC , 
Jobs.[SAIL Work], Jobs.[Contract Work];

 

[thechazm]

Sorry for that bob and I appreciate your inputs. I know that I have not normalized it completely yet that is true. I normally normalize till it hurts then de-normalize till it works but this is rather a new development and I should have been more stricked on myself as far as the design is concerned but it's coming.

Also I have no one to one relationships they are all one to many in this part so far. Plus I hadle my referal integrity through code intead of relying on access's built in meathods for that. Again sorry I was not clear enough.

 

[thechazm]

So I guess my main question is should I try to concatinate realtime or flatline the data and try to handle it where there is a longer loading time? Or unless someone know's of some flashy SQL that can hadle the operation since the SQL part of access run's on multi-processors (at least last I checked I think it did).

 

[spikepl]

jdraw's standard recipe is quite relevant here: when you post stuff explain it like to an idiot, because you are inside you knowledge domain and we aren't. In other words: i have no clue how the shown SQL relates to your original question.


If your data is like this:

Job #1 - A01 - WCT
Job #1 - A02 - RCT
Job #1 - A03 - Null
Job #2 - A01 - WCT
Job #2 - A02 - RCT
Job #2 - A03 - Null

then you grab the recordset ordered by Job # and Axx, run though it once, and for each record add the content of the third column to your concatenated record with the same Job #. This is a one-pass thing, and if the JOb #" is indexed in your table holding concatenated records it should be pretty quick.

It can even be done without searching for the concatenated record at all, with a little forethought - when you get to a new record in your source data, then you know that, and you also know that you stay on the same record until you hit a new one. For 5000 job records this is a job of a second or two - nothing more.

 

[thechazm]

Thanks spikepl I'll give it some further thought. Also Bob I forgot to mention I use ID as my PK field name bc it's my standard naming convention that I have used to many years. Dunno why it would be bad, if you could shed some light one that I would appreciate it?

Thanks again for all your help. I'll just keep at it and I know something will come along. I just hate brick walls

 

[boblarson]

Thanks spikepl I'll give it some further thought. Also Bob I forgot to mention I use ID as my PK field name bc it's my standard naming convention that I have used to many years. Dunno why it would be bad, if you could shed some light one that I would appreciate it?

Thanks again for all your help. I'll just keep at it and I know something will come along. I just hate brick walls

Using ID as the name of every ID field means that just by looking at it one cannot tell what it is for. So, if you have


JobID
PersonID
ManagerID
StatusID

you can tell a lot from that instead of


ID
ID
ID
ID

 

[thechazm]

Sounds good I'll try and break the old habbit because it makes sense. Thanks

 

[thechazm]

So here is my fix for now. I created a tmp table to store the combined values of all the affected fields that gets emptied when before the form is open using the following fields.

Job ID
SOS
ZM
KEOP
Comments
Materials

And I wrote a function to fill the data:

Function BuildTmpRun()
Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset
Dim ii As Long, jbID As Long, ojbID As Long, buildstr As Variant
Set db = CurrentDb
db.Execute "Delete * from [TmpRun]"
Set rs = db.OpenRecordset("Select * from [SOS]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [TmpRun]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    If ojbID = 0 Then
        ojbID = rs("Job ID")
        buildstr = ""
        buildstr = "Yes"
    End If
    
    If ojbID <> rs("Job ID") Then
        With rs2
            .AddNew
            ![Job ID] = ojbID
            ![SOS] = buildstr
            .Update
        End With
        ojbID = rs("Job ID")
        buildstr = ""
        buildstr = "Yes"
    End If
    buildstr = buildstr & ", " & rs("SOS")
    
    rs.MoveNext
Loop
With rs2
    .AddNew
    ![Job ID] = ojbID
    ![SOS] = buildstr
    .Update
End With
rs.Close
Set rs = db.OpenRecordset("Select * from [(Code) ZML]", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Job ID] = " & rs("Job ID")
    
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Job ID] = rs("Job ID")
            ![ZM] = rs("ZML")
            .Update
        End With
    Else
        If IsNull(rs2("ZM")) = False Then
            If InStr(1, rs2("ZM"), rs("ZML")) = 0 Then
                With rs2
                    .Edit
                    ![ZM] = rs2("ZM") & " / " & rs("ZML")
                    .Update
                End With
            End If
        Else
            With rs2
                .Edit
                ![ZM] = rs("ZML")
                .Update
            End With
        End If
    End If
    rs.MoveNext
Loop
rs.Close
Set rs = db.OpenRecordset("Select * from [KEOPs]", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Job ID] = " & rs("Job ID")
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Job ID] = rs("Job ID")
            ![KEOP] = rs("KEOP") & "(" & rs("WKG Status") & ")"
            .Update
        End With
    Else
        If IsNull(rs2("KEOP")) = True Then
            With rs2
                .Edit
                ![KEOP] = rs("KEOP") & "(" & rs("WKG Status") & ")"
                .Update
            End With
        Else
            With rs2
                .Edit
                ![KEOP] = rs2("KEOP") & " " & rs("KEOP") & "(" & rs("WKG Status") & ")"
                .Update
            End With
        End If
    End If
    
    rs.MoveNext
Loop
rs.Close
Set rs = db.OpenRecordset("Select * from [Comments]", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Job ID] = " & rs("Job ID")
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Job ID] = rs("Job ID")
            ![Comments] = rs("Comment")
            .Update
        End With
    Else
        If IsNull(rs2("Comments")) = True Then
            With rs2
                .Edit
                ![Comments] = rs("Comment")
                .Update
            End With
        Else
            With rs2
                .Edit
                ![Comments] = rs2("Comments") & ", " & rs("Comment")
                .Update
            End With
        End If
    End If
    rs.MoveNext
Loop
rs.Close
Set rs = db.OpenRecordset("Select * from [Materials]", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Job ID] = " & rs("Job ID")
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Job ID] = rs("Job ID")
            ![Materials] = rs("Material")
            .Update
        End With
    Else
        If IsNull(rs2("Materials")) = True Then
            With rs2
                .Edit
                ![Materials] = rs("Material")
                .Update
            End With
        Else
            With rs2
                .Edit
                ![Materials] = rs2("Materials") & ", " & rs("Material")
                .Update
            End With
        End If
    End If
    rs.MoveNext
Loop
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function

Takes less than 3 seconds to do it this way and display the form without any lag after the form is open.

I just linked the tmp table to my main query and used these fields instead of the ones from the real dataset.

Later I'll write a function to update the individual records that get changed and have them update the tmp table when something changes but that will be later.

Again thanks for everyone's inputs and thoughts.

Thanks,

TheChazm