Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014
    Posts
    37

    Post Unanswered: Union ALL Query running on forever!

    The Goal: Using an audit table which stores the history of changes to fields on any form (based on info from http://www.fontstuff.com/access/acctut21.htm). Provide the user with a read only history of changes they make, so they can answer the question. "What did I just do?"

    Background Info:

    An excerpt from this table is provided below:

    Code:
    snippet 1:
    
    AuditTrailID    DateTime    UserName    FormName    PK-FieldName    PK-Value    Action    FieldName                                OldValue                    NewValue
    2070    10/5/2014 11:54:20 AM    SLynde    SubsampleDataSubModFrm    RecNo    305504    EDIT    Count                    3                                7
    2071    10/5/2014 11:54:40 AM    SLynde    SubsampleDataSubModFrm    RecNo    305504    EDIT    Count                    7                                 8
    2072    10/5/2014 11:54:47 AM    SLynde    SubsampleDataSubModFrm    RecNo    305504    DELETE            
    2073    10/5/2014 12:08:27 PM    SLynde    SubsampleDataSubModFrm    RecNo    305483    EDIT    Count                      0                                  5
    2075    10/5/2014 1:12:05 PM    SLynde    SubsampleDataSubModFrm    RecNo    305483    EDIT    ScientificName    Chironomidae (127917)    hydropsyche (115453)
    2076    10/5/2014 1:13:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305483    EDIT    Count                      5                                   10
    2077    10/5/2014 1:13:46 PM    SLynde    SubsampleDataSubModFrm    RecNo    305483    EDIT    ScientificName    Hydropsyche (115453)    Hydropsyche betteni (115454)
    2078    10/5/2014 1:15:48 PM    SLynde    SubsampleDataSubModFrm    RecNo    305483    DELETE
    I created my query to find all the records the records for a particular sample based on this table and by linking it to another Query that has the sample information... here's the SQL.


    Code:
    Snippet 2:
    
    SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.[PK-FieldName], tblAuditTrail.[PK-Value], tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue, qrySubSampleData.[Scientific Name], qrySubSampleData.LifeStage, qrySubSampleData.LabProject AS MyLabProject, qrySubSampleData.[SubandSample] AS MySubandSample
    FROM tblAuditTrail LEFT JOIN qrySubSampleData ON clng(tblAuditTrail.[PK-Value]) = qrySubSampleData.RecNo
    WHERE (((tblAuditTrail.UserName)=fosUsername()) AND ((qrySubSampleData.SubandSample)=[Forms]![BenchDataModFrm]![SubandSample]));
    the query runs in about 2 seconds and provides this output

    Code:
    Snippet 3
    AuditTrailID    DateTime    UserName    FormName    PK-FieldName    PK-Value    Action    FieldName    OldValue    NewValue    Scientific Name    LifeStage    MyLabProject    MySubandSample
    675    9/29/2014 6:50:37 PM    SLynde    SubsampleDataSubModFrm    RecNo    305456    EDIT    Count    1    2    Ialysus    I    14-ESC02-001    15497-A
    676    9/29/2014 6:50:37 PM    SLynde    SubsampleDataSubModFrm    RecNo    305456    EDIT    Count    2    3    Ialysus    I    14-ESC02-001    15497-A
    677    9/29/2014 6:50:43 PM    SLynde    SubsampleDataSubModFrm    RecNo    305448    EDIT    Count    57    58    Chironomidae    I    14-ESC02-001    15497-A
    678    9/29/2014 6:51:10 PM    SLynde    SubsampleDataSubModFrm    RecNo    305450    EDIT    Count    11    12    Oligochaeta    I    14-ESC02-001    15497-A
    679    9/29/2014 6:51:10 PM    SLynde    SubsampleDataSubModFrm    RecNo    305450    EDIT    Count    12    13    Oligochaeta    I    14-ESC02-001    15497-A
    680    9/29/2014 6:51:10 PM    SLynde    SubsampleDataSubModFrm    RecNo    305450    EDIT    Count    13    14    Oligochaeta    I    14-ESC02-001    15497-A
    681    9/29/2014 6:51:10 PM    SLynde    SubsampleDataSubModFrm    RecNo    305450    EDIT    Count    14    15    Oligochaeta    I    14-ESC02-001    15497-A
    682    9/29/2014 6:51:10 PM    SLynde    SubsampleDataSubModFrm    RecNo    305450    EDIT    Count    15    16    Oligochaeta    I    14-ESC02-001    15497-A
    All good, but...


    What I found was that when a record is deleted. It no longer belongs to a sample so the link to this information and the sample it came from was lost. To address this I stored the LAST information for all the audited fields before the record was deleted, including the sampleid for the parent record. Here's a snippet:

    Code:
    Snippet 4
    
    AuditTrailID    DateTime    UserName    FormName    PK-FieldName    PK-Value    Action    FieldName              OldValue    NewValue
    2149    10/5/2014 4:17:52 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    NEW            
    2150    10/5/2014 4:17:58 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    EDIT    Count                 0               25
    2163    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    ScientificName                     127917
    2164    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Count                                  25
    2165    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    LifeStage                             I
    2166    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    TaxanomistIdent                  99
    2167    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    TCR                                    1
    2168    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Flagged        
    2169    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Unique                                Yes
    2170    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Mounted                              No
    2171    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Exclude                              0
    2172    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    LRScan                               0
    2173    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    SubSampleID                      21282
    2174    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    DELETE
    All good so far....

    I then created a query to provide information identical to snippet 3. This query runs in about 2 seconds and here's the output:

    Code:
    Snippet 4
    
    AuditTrailID    DateTime    UserName    FormName    PK-FieldName    PK-Value    Action    FieldName    OldValue    NewValue    Scientific Name    LifeStage    MyLabProject    MySubandSample
    2163    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    ScientificName  127917   14-ESC02-001    15497-A
    2164    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Count  25   14-ESC02-001    15497-A
    2165    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    LifeStage  I   14-ESC02-001    15497-A
    2166    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    TaxanomistIdent  99   14-ESC02-001    15497-A
    2167    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    TCR  1   14-ESC02-001    15497-A
    2168    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Flagged     14-ESC02-001    15497-A
    2169    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Unique  Yes   14-ESC02-001    15497-A
    2170    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Mounted  No   14-ESC02-001    15497-A
    2171    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    Exclude  0   14-ESC02-001    15497-A
    2172    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    LRScan  0   14-ESC02-001    15497-A
    2173    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    LAST    SubSampleID  21282   14-ESC02-001    15497-A
    2174    10/5/2014 4:23:41 PM    SLynde    SubsampleDataSubModFrm    RecNo    305510    DELETE      14-ESC02-001    15497-A
    Continued... next post

  2. #2
    Join Date
    Sep 2014
    Posts
    37
    To combine the output of both queries into one, I did a UNION ALL (I don't expect any data to show up in both datasets) query.

    Code:
    Snippet 5
    
    SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.[PK-FieldName], tblAuditTrail.[PK-Value], tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue, qrySubSampleData.[Scientific Name], qrySubSampleData.LifeStage, IIf(IsNull([LabProject]),DLookUp("[LabProject]","qrySubSampleData","[SubSampleID] = " & [Forms]![BenchDataModFrm]![SubsampleID]),[LabProject]) AS MyLabProject, IIf(IsNull([SubandSample]),DLookUp("[SubandSample]","qrySubSampleData","[SubSampleID] = " & [Forms]![BenchDataModFrm]![SubsampleID]),[SubandSample]) AS MySubandSample
    FROM (tblAuditTrail INNER JOIN tblAuditTrail AS tblAuditTrail_1 ON tblAuditTrail.[PK-Value] = tblAuditTrail_1.[PK-Value]) LEFT JOIN qrySubSampleData ON clng(tblAuditTrail.[PK-Value]) = qrySubSampleData.RecNo
    WHERE (((tblAuditTrail.Action)="last" Or (tblAuditTrail.Action)="Delete") AND ((tblAuditTrail_1.FieldName)='SubSampleId') AND ((tblAuditTrail_1.NewValue)=[Forms]![BenchDataModFrm]![SubsampleId]))
    
    
    UNION ALL 
    
    
    SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.[PK-FieldName], tblAuditTrail.[PK-Value], tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue, qrySubSampleData.[Scientific Name], qrySubSampleData.LifeStage, qrySubSampleData.LabProject AS MyLabProject, qrySubSampleData.[SubandSample] AS MySubandSample
    FROM tblAuditTrail LEFT JOIN qrySubSampleData ON clng(tblAuditTrail.[PK-Value]) = qrySubSampleData.RecNo
    WHERE (((tblAuditTrail.UserName)=fosUsername()) AND ((qrySubSampleData.SubandSample)=[Forms]![BenchDataModFrm]![SubandSample]));
    THE PROBLEM: The UNION ALL query runs FOREVER. It seems to continue until the system runs out of resources. Am I approaching this the wrong way? Is there a better way to attack this problem? A little guidance from the masters would be greatly appreciated.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •