none
Transferring Data Through Relationships RRS feed

  • Question

  • Hey everyone, 

    So this problem may be difficult to explain through writing, but I am going to do my best.  

    I have a data structure where data is entered into company sites.  There are hundreds of sites which report up to a main company.   The data base has about 10 separate companies. 

    Some sites report the data directly to their company, and others report the data to a regional site and that site reports to the company.  

    I have a "Sites" table which has  "RegionalID" and  "CompanyID"  included as  attributes.  

    For each site which reports data to a regional site, I do not include a value for "CompanyID" because in the regions table, there is a "CompanyID" attribute; so the site and the company become linked through the regions table. 

    The problem I have is  I want to run a query to show the total sum of data reported to each company, however I can either show the total sum of data reported to each company from regional sites, or the total sum of data reported from sites directly to the companies, I cant show a total aggregate of both in one query.   I tried using a union query, however companies which have sites that report data directly to them and also have regional sites which report to them, show up twice in the union.   

    Does anyone have an idea how I can run a query to show each company with an aggregate of data reported to them through sites and regions? 

    I am sorry if this doesnt make much sense, its very hard to explain over text.  I really appreciate any help! Thank you!

    Sunday, January 10, 2016 3:28 AM

Answers

  • For example,  when I do a union query ill get something like this 

    Company A 1200 

    Company A 800 

    The first result is the data reported to company A from regional sites, and second is data reported to company A directly from sites.  I am not sure now how to automatically sum company A into one row (Company A 2000) Since they are coming from separate queries.  

    You need a JOIN, not a UNION operation; e.g. if we assume the original queries are named Q1 and Q2:

    SELECT Q1.Company, Q1.Total + Q2.Total AS GrandTotal
    FROM Q1 INNER JOIN Q1 on Q1.Company = Q2.Company;

    However, this assumes that every company is represented in the result  sets of both original queries.  If not you'd have to use a combination of OUTER JOINs and a UNION operation, e.g.

    SELECT Q1.Company, Q1.Total + NZ(Q2.Total,0) AS GrandTotal
    FROM Q1 LEFT JOIN Q2 on Q1.Company = Q2.Company
    UNION
    SELECT Q1.Company, NZ(Q1.Total,0) + Q2.Total
    FROM Q1 RIGHT JOIN Q2 on Q1.Company = Q2.Company;

    The first part of the UNION operation returns all row from Q1, adding the value of the Total column from Q1 to that of the Total column from Q2 or, by virtue of the NZ function, to zero if there is no matching row in Q2.  The second part of the UNION operation returns all row from Q2, adding the value of the Total column from Q2 to that of the Toal column from Q1 or, by virtue of the NZ function, to zero if there is no matching row in Q1.

    Where there are rows with a match in each query's result both parts of the UNION operation will of course return those rows, but because a UNION operation suppresses duplicate rows only one instance of each will be returned.


    Ken Sheridan, Stafford, England

    Sunday, January 10, 2016 9:53 PM

All replies

  • Hi. You're right. It's still a little unclear just reading it. Can you post some screenshots?
    Sunday, January 10, 2016 6:29 AM
  • As you apparently are able to return the totals successfully by means of separate queries, can you join those queries and then add the values from the two total columns to give you the overall total per company?

    Ken Sheridan, Stafford, England

    Sunday, January 10, 2016 11:48 AM
  • Hi Ken, thank you so much for your reply.  

    I am able to do this, but I do not know how to add the companies together at this stage.  

    For example,  when I do a union query ill get something like this 

    Company A 1200 

    Company A 800 

    The first result is the data reported to company A from regional sites, and second is data reported to company A directly from sites.  I am not sure now how to automatically sum company A into one row (Company A 2000) Since they are coming from separate queries.  

    Sunday, January 10, 2016 5:54 PM
  • Hi Thank you so much for your reply, Im working on screenshots now. Thanks!
    Sunday, January 10, 2016 6:13 PM
  • Does anyone know how I can verify my account?  Apparently I can not post screenshots until I do so. 
    Sunday, January 10, 2016 6:27 PM
  • For example,  when I do a union query ill get something like this 

    Company A 1200 

    Company A 800 

    The first result is the data reported to company A from regional sites, and second is data reported to company A directly from sites.  I am not sure now how to automatically sum company A into one row (Company A 2000) Since they are coming from separate queries.  

    You need a JOIN, not a UNION operation; e.g. if we assume the original queries are named Q1 and Q2:

    SELECT Q1.Company, Q1.Total + Q2.Total AS GrandTotal
    FROM Q1 INNER JOIN Q1 on Q1.Company = Q2.Company;

    However, this assumes that every company is represented in the result  sets of both original queries.  If not you'd have to use a combination of OUTER JOINs and a UNION operation, e.g.

    SELECT Q1.Company, Q1.Total + NZ(Q2.Total,0) AS GrandTotal
    FROM Q1 LEFT JOIN Q2 on Q1.Company = Q2.Company
    UNION
    SELECT Q1.Company, NZ(Q1.Total,0) + Q2.Total
    FROM Q1 RIGHT JOIN Q2 on Q1.Company = Q2.Company;

    The first part of the UNION operation returns all row from Q1, adding the value of the Total column from Q1 to that of the Total column from Q2 or, by virtue of the NZ function, to zero if there is no matching row in Q2.  The second part of the UNION operation returns all row from Q2, adding the value of the Total column from Q2 to that of the Toal column from Q1 or, by virtue of the NZ function, to zero if there is no matching row in Q1.

    Where there are rows with a match in each query's result both parts of the UNION operation will of course return those rows, but because a UNION operation suppresses duplicate rows only one instance of each will be returned.


    Ken Sheridan, Stafford, England

    Sunday, January 10, 2016 9:53 PM