Results 1 to 11 of 11
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    Doubtful about using Group by with Multiple table join.

    Group by and Join doesn’t go hand in hand. Group by (also Aggregate functions) on Access query does not seem to work properly when it is used with multiple tables joins. However, I think it works with only two table join. But I can’t reckon on that. Especially with outer joins. Some say subquery is a way around, but it’s not very clear to me. Can any body enlighten me on that?
    Last edited by bubai; 11-25-2019 at 02:45 AM.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've been using aggregate queries successfully for many years often with multiple table joins.
    Here's one example chosen at random:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	27.6 KB 
ID:	40286

    Here's another example with 3 tables and a query (itself built from 3 tables)

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	20 
Size:	27.2 KB 
ID:	40287

    Subqueries have their uses but can be slow.
    I see no reason to use them for this type of query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    I've been using aggregate queries successfully for many years often with multiple table joins.
    Here's one example chosen at random:
    .
    You have used one to one join. But I find trouble when there is outer joins with more than two tables.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Perhaps you should have stated that at the beginning of the thread.
    Rather than me waste further time searching for existing multi table outer join aggregate queries, please define exactly what the problem is that you are having
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    Perhaps you should have stated that at the beginning of the thread.
    Sorry if I was ambiguous. I have made corrections. The problem was miscalculation in sum functions and with group by when I used more than 2 table with outer joins. It also showed duplication.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problem. If you've fixed the issue could you mark the thread as solved.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    No problem. If you've fixed the issue could you mark the thread as solved.
    No I have not solved the issue. What I meant was - I have made corrections to the problem description.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Help us to help you - suggest show your query SQL, some example data, the output you are currently getting and the output required. Otherwise we are just guessing what the issue is so cannot offer a solution

  9. #9
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by Ajax View Post
    Help us to help you - suggest show your query SQL, some example data, the output you are currently getting and the output required. Otherwise we are just guessing what the issue is so cannot offer a solution
    I don't have the query on me any longer. Unable to work it out, i have changed complete structure of the query. Had to break it down to couple of separate queries. But it's cumbersome now. However, if you can provide a concrete guideline of using Group by (with aggregate) with Outer Joins I will be much obliged.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you can provide a concrete guideline of using Group by (with aggregate) with Outer Joins I will be much obliged
    you just use them. Without understanding what you are trying to do and with what, not possible to advise further. Your question is akin to you asking your mechanic how to make your car go faster. Without knowing details of the car they cannot advise you.

    If you can't provide the query, at least provide example data and the required outcome

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I agree. There is no special tricks to use multiple tables with outer joins other than ensuring you avoid ambiguous joins
    Here's another one aggregate query of mine. This time with 4 tables all with outer joins

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	27.7 KB 
ID:	40303

    Code:
    SELECT DISTINCT Subjects.FacultyIDTimetable.RoomIDCount(Timetable.RoomID) AS Lessons
    FROM 
    ((Classes LEFT JOIN Subjects ON Classes.SubjectID Subjects.SubjectID
    RIGHT JOIN Timetable ON Classes.ClassID Timetable.ClassID
    LEFT JOIN Faculties ON Subjects.FacultyID Faculties.FacultyID
    WHERE 
    (((Classes.Offsite)=False))
    GROUP BY Subjects.FacultyIDTimetable.RoomID
    HAVING 
    (((Subjects.FacultyID)<>'') AND ((Timetable.RoomID)<>'' And (Timetable.RoomID)<>'-'))
    ORDER BY Subjects.FacultyIDTimetable.RoomID
    If you're finding this process difficult I suggest you try creating it first as a standard select query and modify as necessary to ensure it gives the correct records.
    Then change it to an aggregate query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combine multiple Inner Join same table
    By kaylachris in forum Queries
    Replies: 11
    Last Post: 03-12-2019, 05:37 AM
  2. Replies: 6
    Last Post: 02-26-2019, 11:53 PM
  3. Multiple Table Join Issue
    By tehbaker in forum Database Design
    Replies: 4
    Last Post: 10-07-2010, 01:30 PM
  4. SQL query with SUM, INNER JOIN and GROUP BY
    By BayerMeister in forum Queries
    Replies: 2
    Last Post: 08-14-2010, 12:55 AM
  5. Error on Multiple Table Join & IIF
    By cmartin in forum Queries
    Replies: 1
    Last Post: 05-21-2010, 08:58 PM

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