none
Query RRS feed

  • Question

  • Hi all,

     I have 2 tables linked with an Id and a structure like next example. Table 2 are changes of 'nummer' and have an index when they are changed. I will make a query that give me all the filelds of Tabel1 and one extra field 'Changes' witch give me the projects and index of tabel2.

    So the parameter is 'Project'. When i give as parameter 'Project2', than i become all the fields of table1 where project='project2' and a field "Changes" with the project and index name of the last changes of that given project

    <tfoot></tfoot>
    Tabel1
    Id Nummer Project Veld1 Veld2 Veld3
    1 1 Project1


    2 2 Project2


    3 3 Project3


    4 4 Project2


     

    <tfoot></tfoot>
    Tabel2
    id Project Index Datum
    1 Project4 A 1/07/2015
    1 Project2 B 8/07/2015
    1 Project6 C 8/09/2015
    1 Project2 D 11/09/2015
    2 Project2 A 1/09/2015
    2 Project9 B 3/09/2015
    2 Project10 C 8/09/2015
    3 Project2 A 10/09/2015

    The result is given in next example

    <tfoot></tfoot>
    Query3
    Id Nummer Veld1 Veld2 Veld3 Project Change
    1 1


    Project1 Project2-D
    2 2


    Project2 Project10-C
    3 3


    Project3 Project2-A
    4 4


    Project2 -

    I have made a query

    SELECT Tabel1.Id, Tabel1.Nummer, Tabel1.Veld1, Tabel1.Veld2, Tabel1.Veld3, Tabel1.Project, Last([tabel2.Project] & "-" & [tabel2.index]) AS Change
    FROM Tabel1 LEFT JOIN Tabel2 ON Tabel1.Id = Tabel2.id
    GROUP BY Tabel1.Id, Tabel1.Nummer, Tabel1.Veld1, Tabel1.Veld2, Tabel1.Veld3, Tabel1.Project
    HAVING (((Tabel1.Project)="project2")) OR (((Last([tabel2.Project] & "-" & [tabel2.index])) Like "project2*"));

    but tabel 1 is a table with 100000 records and has much more fields than in this example. Tabel2 has also a lot of records. I've tried also to make a union query but both are very slow.

    Anyone a sugestion?

    Monday, September 14, 2015 8:52 PM

Answers

  • for tables the size you have one must address it with less dependency logic

    first: make an aggregate query of table 2 that results in just max date:

    1  11/9/15

    2  8/9/15

    etc

    save that as  'T2Max' query

    next: join T2Max back to Table2 on the ID & Date so you get the project name:

    1  11/9/15 Project 2D

    2  8/9/15  Project 10C

     save that as 'T2' ...at this point - you may need to write this info to a temp table for speed purposes - but only testing will decide that

    Assuming that table 1 is distinct as per your example (no repeating IDs) then make a query that joins Table 1 to T2 (either the query or the temp table). 

    If table 1 is not distinct first make a distinct query of it (you also may need to write this to a temp table for performance reasons)

    A set of simpler queries will perform faster than a single query with multiple logic requirements when applied against very large tables.  Of course your PCs horsepower is a consideration too.  Hope this helps.


    Tuesday, September 15, 2015 1:15 PM

All replies

  • Your problem is the LAST function as Access's LAST is not what you think it is.

    Try This - UNTESTED --

    SELECT Tabel1.Id, Tabel1.Nummer, Tabel1.Veld1, Tabel1.Veld2, Tabel1.Veld3, Tabel1.Project, Nz(Max(table2.Datnum),""), LastChange, Last([tabel2.Project] & "-" & [tabel2.index]) AS Change
    FROM Tabel1 LEFT JOIN Tabel2 ON Tabel1.Id = Tabel2.id
    GROUP BY Tabel1.Id, Tabel1.Nummer, Tabel1.Veld1, Tabel1.Veld2, Tabel1.Veld3, Tabel1.Project
    HAVING Tabel1.Project="project2";


    Build a little, test a little

    Monday, September 14, 2015 9:10 PM
  • Hi Lteu,

    Do you mean that your code could achieve your requirement, but it has the performance issue? In my option, there are many elements to affect the performance, I think the link below might be useful to you:

    # Information about query performance in an Access database
    https://support.microsoft.com/en-us/kb/209126

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 15, 2015 1:51 AM
  • for tables the size you have one must address it with less dependency logic

    first: make an aggregate query of table 2 that results in just max date:

    1  11/9/15

    2  8/9/15

    etc

    save that as  'T2Max' query

    next: join T2Max back to Table2 on the ID & Date so you get the project name:

    1  11/9/15 Project 2D

    2  8/9/15  Project 10C

     save that as 'T2' ...at this point - you may need to write this info to a temp table for speed purposes - but only testing will decide that

    Assuming that table 1 is distinct as per your example (no repeating IDs) then make a query that joins Table 1 to T2 (either the query or the temp table). 

    If table 1 is not distinct first make a distinct query of it (you also may need to write this to a temp table for performance reasons)

    A set of simpler queries will perform faster than a single query with multiple logic requirements when applied against very large tables.  Of course your PCs horsepower is a consideration too.  Hope this helps.


    Tuesday, September 15, 2015 1:15 PM