Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Project table has a
    PK of pID and an
    Indexed (No duplicates) of Tracking_num

    Submissions has a
    PK of sID and an
    Indexed (Duplicates OK) of sTracking_num

    their one to many relationship is based on Tracking_num and sTracking_num
    Is there a more proper way to setup the tables? Maybe that was part of the problem with the keys you were seeing?

    I will play around with those queries and see what I can come up with.

    Thank you for all the time you spent looking at the database.

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, I see those keys and indexes when I look at the tables again. You have unique/no duplicates on those fields so that should be fine.

    I would normally use the autonumber PK on Projects and have a long integer FK on Submission named something like PidFK or similar and have the relationship on those fields. But I think your tracking_nums should work fine. They are short text but I don't think the speed difference (depending on operational table sizes) would be significant.

    I think I may have some issue with this laptop. I notice when I try to rename an object on the nav pane that the rename box closes almost immediately. I have reverted to creating a small MyRename function in vba. My guess is there may be something underlying these little idiosyncrasies.

    Here are a few stats:

    Distinct projects 473
    Submissions 1367
    Distinct stracking_num 432
    ------
    SELECT Project.*
    FROM Project left JOIN Submission ON
    Project.Tracking_num = Submission.sTracking_num
    WHERE (((Submission.sTracking_num) Is Null));

    Projects with no submission 41
    ------
    Projects with duplicate Max(due) 4
    pid
    65
    100
    227
    283

    Note: These 4 records/duplicates get included in the results of QueryJED2


    Let us know how things go. And perhaps someone else will see/offer a technique to resolve the issue and speed things up.
    Last edited by orange; 06-19-2020 at 07:56 AM.

  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I had another go at this today and managed to get a query to get the info from
    your data where there was:

    A) a single Max(Due) value for a Project 's and submissions (428 records), and
    B) multiple/duplicate Max(Due), so got info from the Max(Sid) of the Group (4 records)

    The approach was to get all the records where there was a single Max(Due) for the submissions for a Project.
    Then, for those records where there were duplicate Max(Due) values, get the Max(Sid) and related info for that Project.
    Then UNION the 2 sets of info to get the final values.

    Here is the final query:

    Code:
    SELECT Project.pID
    	,Project.Tracking_num
    	,QueryJED1.sTracking_num
    	,QueryJED1.MaxOfDue
    	,Submission.sID
    	,Submission.ReviewNum
    	,Submission.Reviewer
    	,Submission.Completed
    	,Submission.Due
    FROM (
    	QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
    		AND (Project.pID = QueryJED1.pID)
    		AND (QueryJED1.pID = Project.pID)
    	)
    INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    	AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    WHERE Project.PID IN (
    		SELECT MyQ.pid AS MQPID
    		FROM (
    			SELECT Project.pID
    				,Project.Tracking_num
    				,QueryJED1.sTracking_num
    				,QueryJED1.MaxOfDue
    				,Submission.sID
    				,Submission.ReviewNum
    				,Submission.Reviewer
    				,Submission.Completed
    				,Submission.Due
    			FROM (
    				QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
    					AND (QueryJED1.pID = Project.pID)
    					AND (QueryJED1.pID = Project.pID)
    				)
    			INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    				AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    			ORDER BY Project.pID
    			) AS myQ
    		GROUP BY myQ.pid
    		HAVING count(reviewnum) = 1
    		)
    
    UNION
    
    SELECT Project.pID
    	,Project.Tracking_num
    	,QueryJED1.sTracking_num
    	,QueryJED1.MaxOfDue
    	,Submission.sID
    	,Submission.ReviewNum
    	,Submission.Reviewer
    	,Submission.Completed
    	,Submission.Due
    FROM (
    	QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
    		AND (Project.pID = QueryJED1.pID)
    		AND (QueryJED1.pID = Project.pID)
    	)
    INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    	AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    WHERE submission.sid IN (
    		SELECT max(sid)
    		FROM (
    			SELECT Project.pID
    				,Project.Tracking_num
    				,QueryJED1.sTracking_num
    				,QueryJED1.MaxOfDue
    				,Submission.sID
    				,Submission.ReviewNum
    				,Submission.Reviewer
    				,Submission.Completed
    				,Submission.Due
    			FROM (
    				QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
    					AND (QueryJED1.pID = Project.pID)
    					AND (QueryJED1.pID = Project.pID)
    				)
    			INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    				AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    			ORDER BY Project.pID
    			) AS Num2
    		GROUP BY num2.pid
    		HAVING count(reviewnum) > 1
    		)
    An interesting "battle" with the SQL as it has been a while. I hope the query is responsive enough for your needs.
    Good luck.

  4. #19
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Looks like an exact match and about 4 times faster than my original query. Now I just need to figure out how you did it so I can apply the strategy in the future if needed.

    What are the AND statements below doing here?
    Code:
    FROM (    QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
            AND (Project.pID = QueryJED1.pID)
            AND (QueryJED1.pID = Project.pID)
        )

    Does this part only get the records with 1 due date?
    Code:
    HAVING count(reviewnum) = 1
    Thank you greatly for the help.

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The code above the UNION line is where there is only 1 ReviewNum for the Max(Due) value for Project Sumissions.
    The code below the UNION line gets the MAX(SId) for those records where there is more than 1 ReviewNum and there is more than 1 record with the same Max(Due) value.

    The sql UNION construct appends unique records to the result set.

    I have attached the database containing these additional queries.

    You can copy/adjust and run the parts to see what is returned.

    Good luck with your project.
    Attached Files Attached Files

  6. #21
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Thanks orange,

    I have used Union queries before but I am not familiar with an inner join with multiple statements as you have:

    Code:
    FROM (    QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)        
            AND (Project.pID = QueryJED1.pID)
            AND (QueryJED1.pID = Project.pID)
        )
    so you INNER JOIN on Project.pID = QueryJED1.pID and then you have an AND statement and then the same exact thing and then and AND statement with basically the same thing but reversed, QueryJED1.pID = Project.pID. I dont understand why the multiples, does this do anything different then the follow code:

    Code:
    FROM (    QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)   )
    Thanks for any clarification on this.

  7. #22
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My approach was to try to determine what the data represented. That is, what was causing the duplication - I saw Project, SId, reviewnum, Due etc and saw that tracking_num/stracking_num were involved.

    I used (A) a basic selection of records, then refined a subset that addressed the records with a single reviewnum with the Max(Due) which excluded those records where there were multiple reviewnums with the max(Due) and then,
    (B)took the same basic selection of records and refined it by addressing only those records where the Max(Due) was associated with multiple reviewnums and selecting the Max(Sid) in the group.
    The first selection (A) identified 428 records, and (B) found 4 records for a total of 432.

    Now, it may be possible to remove some of the inner joins and/or revise the sql. I basically took the sql generated by my query design and copied it as needed-tested it, then copied/inserted it into the next query to build up the sets that included the ANDs. I first looked at the records with multiple reviewnums with Max(Due) and saw there were 4 records involved. And based on earlier posts I realized that 430 was incorrect. Once I manipulated the sql to get the 428 and 4 records subsets I didn't process things further. So, it's possible there may be some duplication within joins.

    I have not tried to remove some of the intermediate steps/joins to see if there is some unnecessary duplication. It would be an interesting exercise. Since there is a query that does work, you could make a copy and experiment. I agree that there appears to be an extra join(s) and don't have any specific info. Testing and further review is needed to get some rationale for it/them or to remove same if needed.

    If I get some time I'll take a look, but in the meantime, since you are familiar with the data and the requirement, perhaps you should do some experimenting (with a copy) and let readers know what you find.

    Good luck.

    Update: 8:54
    I did some reviewing/cutting and testing. I removed some of the joins and was able to get the 432 records with this SQL. I named it QJRevisedJoinsFinal432 in my copy of the database.

    Code:
     SELECT Project.pID
    	,Project.Tracking_num
    	,QueryJED1.sTracking_num
    	,QueryJED1.MaxOfDue
    	,Submission.sID
    	,Submission.ReviewNum
    	,Submission.Reviewer
    	,Submission.Completed
    	,Submission.Due
    FROM (
    	QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
    	)
    INNER JOIN Submission ON (QueryJED1.sTracking_num = Submission.sTracking_num)
    	AND (QueryJED1.MaxOfDue = Submission.Due)
    WHERE Project.PID IN (
    		SELECT MyQ.pid AS MQPID
    		FROM (
    			SELECT Project.pID
    				,Project.Tracking_num
    				,QueryJED1.sTracking_num
    				,QueryJED1.MaxOfDue
    				,Submission.sID
    				,Submission.ReviewNum
    				,Submission.Reviewer
    				,Submission.Completed
    				,Submission.Due
    			FROM (
    				QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
    				)
    			INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    				AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    			ORDER BY Project.pID
    			) AS myQ
    		GROUP BY myQ.pid
    		HAVING count(reviewnum) = 1
    		)
    
    UNION
    
    SELECT Project.pID
    	,Project.Tracking_num
    	,QueryJED1.sTracking_num
    	,QueryJED1.MaxOfDue
    	,Submission.sID
    	,Submission.ReviewNum
    	,Submission.Reviewer
    	,Submission.Completed
    	,Submission.Due
    FROM (
    	QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
    	)
    INNER JOIN Submission ON (QueryJED1.sTracking_num = Submission.sTracking_num)
    	AND (QueryJED1.MaxOfDue = Submission.Due)
    WHERE submission.sid IN (
    		SELECT max(sid)
    		FROM (
    			SELECT Project.pID
    				,Project.Tracking_num
    				,QueryJED1.sTracking_num
    				,QueryJED1.MaxOfDue
    				,Submission.sID
    				,Submission.ReviewNum
    				,Submission.Reviewer
    				,Submission.Completed
    				,Submission.Due
    			FROM (
    				QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
    				)
    			INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
    				AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    			ORDER BY Project.pID
    			) AS Num2
    		GROUP BY num2.pid
    		HAVING count(reviewnum) > 1
    		);
    Last edited by orange; 06-26-2020 at 10:13 AM. Reason: reformatted the SQL based on PoorSQL

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. TempVars Slowing Down Query?
    By jnoonan22 in forum Queries
    Replies: 4
    Last Post: 12-31-2015, 12:13 PM
  2. Function call in query slowing it down
    By sneuberg in forum Queries
    Replies: 2
    Last Post: 10-17-2014, 10:32 AM
  3. Replies: 3
    Last Post: 02-27-2014, 10: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