Results 1 to 2 of 2
  1. #1
    MSandell13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    How to break up my query then have them link to one report

    I have one rather large query that is either providing duplicate results or potentially not all of my records. I am wondering the best way to break up these queries now that I made one large one, and then how best to link them to get one report or table. Also, when I click on relationships, nothing populates, should something be there? Anything helps!



    SELECT dbo_customer.customer_name, dbo_customer.customer_token, [Proposal Status Codes].description1, dbo_underwriter.last_name, dbo_salesperson.lastname, dbo_proposal.effec_date, dbo_customer.state, dbo_producer_company.producer_company_name, dbo_producer_company.state, dbo_producer.firstname, dbo_producer.lastname, dbo_experience_contacts_view.contact_type, dbo_experience_contacts_view.contact_name, dbo_producer_parent.Parent_company_name, Sum(dbo_worksheet.enroll_total) AS SumOfenroll_total, dbo_mgu_representative.first_name, dbo_mgu_representative.last_name, dbo_proposal.renewal INTO Renewals
    FROM (((((((dbo_mgu_representative INNER JOIN ((dbo_producer INNER JOIN dbo_producer_company ON dbo_producer.producer_company_token = dbo_producer_company.producer_company_token) INNER JOIN (dbo_customer INNER JOIN dbo_proposal ON dbo_customer.customer_token = dbo_proposal.customer_token) ON dbo_producer.producer_token = dbo_proposal.producer_token) ON dbo_mgu_representative.mgu_rep_token = dbo_proposal.mgu_rep_token) INNER JOIN dbo_worksheet ON dbo_proposal.proposal_token = dbo_worksheet.proposal_token) INNER JOIN dbo_underwriter ON dbo_proposal.under_token = dbo_underwriter.under_token) INNER JOIN dbo_salesperson ON dbo_proposal.sales_rep_token = dbo_salesperson.salesperson_token) INNER JOIN [Proposal Status Codes] ON dbo_proposal.status = [Proposal Status Codes].value) INNER JOIN dbo_experience_period ON dbo_customer.customer_token = dbo_experience_period.customer_token) INNER JOIN dbo_experience_contacts_view ON dbo_experience_period.exp_period_token = dbo_experience_contacts_view.exp_period_token) INNER JOIN dbo_producer_parent ON dbo_producer_company.parent_company_token = dbo_producer_parent.parent_company_token
    GROUP BY dbo_customer.customer_name, dbo_customer.customer_token, [Proposal Status Codes].description1, dbo_underwriter.last_name, dbo_salesperson.lastname, dbo_proposal.effec_date, dbo_customer.state, dbo_producer_company.producer_company_name, dbo_producer_company.state, dbo_producer.firstname, dbo_producer.lastname, dbo_experience_contacts_view.contact_type, dbo_experience_contacts_view.contact_name, dbo_producer_parent.Parent_company_name, dbo_mgu_representative.first_name, dbo_mgu_representative.last_name, dbo_proposal.renewal
    HAVING (((dbo_proposal.effec_date)=#2/1/2020#) AND ((dbo_experience_contacts_view.contact_type)="07") AND ((dbo_proposal.renewal)="Y"));

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build a query that does aggregation on one table then JOIN that query to other table.

    If this query involves multiple many-to-many relationships then possibly need subreports.

    You already asked this question and received same answer https://www.accessforums.net/showthread.php?t=79448

    You click on relationships where?
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-22-2015, 03:23 AM
  2. break the link to a backend table
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 06-28-2013, 10:49 PM
  3. Replies: 9
    Last Post: 05-07-2013, 08:37 PM
  4. Page Break in Report
    By MarshallSutton in forum Reports
    Replies: 2
    Last Post: 09-23-2011, 05:54 PM
  5. Alphabet break in report
    By amccook in forum Reports
    Replies: 8
    Last Post: 08-27-2010, 03:13 PM

Tags for this Thread

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