Results 1 to 3 of 3

Bringing multiple tables into one query help.

  1. #1
    Dakker221 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2019
    Posts
    2

    Bringing multiple tables into one query help.

    Hi Everyone,

    I would really appreciate help with this one I can't seem to work it out.

    I have these example tables - for arguments sake I can't do anything about the structure of the tables as these come from the client and the real tables have a lot more data in.

    tbl_fruit_sales
    id_fruit_sale
    sale_date
    staff_id
    staff_name
    amount_of_sales


    tbl_new_customer_memberships
    id_memberships


    report_date
    staff_id
    customer_name

    tbl_complaints
    id_complaint
    report_date
    staff_id
    complaint

    tbl_staff
    staff_id
    staff_name
    staff_status


    Ideal output:



    Date staff_id staff_name Sum of Sales Count of new memberships Count of complaints
    17/05/2019 46445 David Smith 1 2 0
    18/05/2019 46445 David Smith 4 2 7



    What I can't seem to understand is how to link the tables to get this output. For example on the 18/05/2019 I need to know what David did but if I tried to join the rest of the tables to say sales if he had no sales on the 18th I wouldnt be able to bring back memberships and complaints, its the same issue what ever table I use.

    Would appreciate any help.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    Well, this will get complicated.

    Need a dataset that has all possible date and staff combinations. Getting all possible dates can be done with a UNION query to pull dates from the 3 tables. Then build a query using the UNION query and tbl_staff without a join clause - this is a Cartesian relationship and will result in all possible date/staff pairs.

    Now build 3 aggregate queries summarizing by date and staff.

    Build another query that does a compound join of the 3 aggregate queries to the Cartesian query.

    I count 6 queries to accomplish that output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dakker221 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2019
    Posts
    2
    Hi June,

    oh wow thank you for coming back so quick, I really appreciate this. I will try this now and see where I get to.

    Thanks

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

Similar Threads

  1. Replies: 6
    Last Post: 02-14-2018, 04:25 PM
  2. Replies: 4
    Last Post: 09-13-2017, 05:19 AM
  3. Replies: 4
    Last Post: 08-09-2017, 11:06 AM
  4. Replies: 4
    Last Post: 08-24-2016, 05:48 AM
  5. Replies: 1
    Last Post: 04-12-2013, 02:03 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
  •  
Tech Forums: Microsoft Office Forums