Query to consolidate other query results 1

[tricky29]

Hi,

I have spent the last day trying to resolve this one without success.

I have 4 separate queries that report on a range of consultants on their individual daily activity. What I want to do is combine all sets of results into one master query for export.

i.e.

Query 1

Consultant ID Clients

1 3
2 4
4 2
8 4

Query 2

Consultant ID Sales

1 3
2 2
3 2
5 1

Query 3

Consultant ID Sent

4 3
5 2
7 2
8 1

Query 4

Consultant ID Received

1 4
3 3
4 2
6 2
8 1

Desired Query

Consultant ID Clients Sales Sent Received
1 3 3 0 4
2 4 2 0 2

etc.

Once consolidated I can then do a simple export for reporting purposes.

Please can someone help, I have already wasted a day on this and ageing by the minute.

 

[r937]

SELECT consultant_id
     , SUM(clients)  AS clients
     , SUM(sales)    AS sales
     , SUM(sent)     AS sent
     , SUM(received) AS received
  FROM ( 
       SELECT consultant_id
            , clients
            , NULL AS sales
            , NULL AS sent
            , NULL AS received
         FROM query1
       UNION ALL
       SELECT consultant_id
            , NULL AS clients
            , sales
            , NULL AS sent
            , NULL AS received
         FROM query2
       UNION ALL
       SELECT consultant_id
            , NULL AS clients
            , NULL AS sales
            , sent
            , NULL AS received
         FROM query3
       UNION ALL
       SELECT consultant_id
            , NULL AS clients
            , NULL AS sales
            , NULL AS sent
            , received
         FROM query4
       ) AS u
GROUP
    BY consultant_id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

Thank you so much I will give it a try and let you know

 

[tricky29]

I am sorry, I cant get it to work. I now get a circular reference. I have converted your text into the actual variable names and query names. I will therefore post all 4 queries below and amended code. I would really appreciate it if you could spot the error, as I am sure it is my inexperience in this.

Queries 1 to 4 are named PS_One, PS_Two etc. I have named your query Summary Query. You will also notice that whilst the names are stored as a number it is linked to another table to display the actual name.

Queries 1 - 4 below.

SELECT Tm.Name AS Name, Count(Illustration.[Illustration ID]) AS CLIENTS
FROM Illustration INNER JOIN Tm ON Illustration.[Fpg - ConsultantName]=Tm.Id
WHERE (((Illustration.[Call Stage])>1) And ((Illustration.[Fpg - Date]) Between Forms!Export2!Date1 And Forms!Export2!Date2))
GROUP BY Tm.Name;

SELECT Tm.Name AS Name, Count(Loans.ID) AS PACKSOUT
FROM (Illustration INNER JOIN Loans ON Illustration.[Illustration ID]=Loans.Illustration_ID) INNER JOIN Tm ON Illustration.[Fpg - ConsultantName]=Tm.Id
WHERE (((Illustration.[Call Stage])>1) AND ((Illustration.[Fpg - Date]) Between Forms!Export2!Date1 And Forms!Export2!Date2))
GROUP BY Tm.Name;

SELECT Tm.Name AS Name, Count(Illustration.[Fpg - Type of Visit]) AS COLLECTION
FROM Illustration INNER JOIN Tm ON Illustration.[Fpg - ConsultantName]=Tm.Id
WHERE (((Illustration.[Fpg - Date]) Between Forms!Export2!Date1 And Forms!Export2!Date2) AND ((Illustration.[Call Stage])>1) AND ((Illustration.[Fpg - Type of Visit])=3))
GROUP BY Tm.Name;

SELECT Tm.Name AS Name, Count(Illustration.[Fpg - Type of Visit]) AS SALES
FROM Illustration INNER JOIN Tm ON Illustration.[Fpg - ConsultantName]=Tm.Id
WHERE (((Illustration.[Fpg - Date]) Between Forms!Export2!Date1 And Forms!Export2!Date2) AND ((Illustration.[Call Stage])>1) AND ((Illustration.[Fpg - Type of Visit])=4))
GROUP BY Tm.Name;

And now your amended query.

SELECT NAME, SUM(CLIENTS) AS CLIENTS, SUM(PACKSOUT) AS PACKSOUT, SUM(COLLECTION) AS COLLECTION, SUM(SALES) AS SALES
FROM (SELECT NAME, CLIENTS, NULL AS PACKSOUT, NULL AS COLLECTION, NULL AS SALES
FROM PS_One
UNION ALL
SELECT NAME, NULL AS CLIENTS , PACKSOUT, NULL AS COLLECTION, NULL AS SALES
FROM PS_Two
UNION ALL
SELECT NAME, NULL AS CLIENTS , NULL AS PACKSOUT, COLLECTION, NULL AS SALES
FROM PS_Three
UNION ALL
SELECT NAME, NULL AS CLIENTS , NULL AS PACKSOUT, NULL AS COLLECTION, SALES
FROM PS_Four
) AS u
GROUP BY NAME;


This is exactly how all 5 are setup, and I receive a circular reference.

I look forward to your response


Kind Regards


Tricky29

 

[r937]

i'm not sure, i think the circular reference is due to trying to assign the same name...

SUM(clients) AS clients

try it like this and see if the error goes away...

SUM(clients) AS clients_x

if that fixes the error, but you'd rather it said clients instead of clients_x in the final query, do you know how to go back and edit it in the UNION subquery?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

That fixes the error however I now get a different error...

The expression is typed incorrectly or is too complicated to evaluate. For example a nummeric expression may contain too many complicated elements. Try simplifing the expression by assigning aprts of the expression to variables

On the side was I supposed to change the line that reads

) AS u
look forward to your thoughts

 

[r937]

which expression?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

I have changed the first line to different variable names as you suggested and that works, however it does not indicate where it is falling over i just get the error box up once it has asked for the dates , which it is picking up from the existing queries.

However if I remove query 3 and query 4 from the summary it runs perfectly for the first two queries and all queries run correctly individually

 

[r937]

try this and tell me how you like it --

SELECT Tm.Name AS Name
     , COUNT(*) AS [red]CLIENTS[/red]
     , SUM(IIF(Illustration.[Fpg - Type of Visit] = 3
              , 1 , 0 )) AS [red]COLLECTION[/red]
     , SUM(IIF(Illustration.[Fpg - Type of Visit] = 4
              , 1 , 0 )) AS [red]SALES[/red]
  FROM Illustration 
INNER 
  JOIN Tm 
    ON Tm.Id = Illustration.[Fpg - ConsultantName]
 WHERE Illustration.[Fpg - Date] 
       BETWEEN Forms!Export2!Date1 
           AND Forms!Export2!Date2 
   AND Illustration.[Call Stage] > 1 
GROUP 
    BY Tm.Name

if that works, then all we have to do is add some more logic for the PACKSOUT and you can throw away all your other queries

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

You are a genius , that works perfectly. If you are able to sort the packsout, then I can start to regrow my thinning hair. lol

Regards

Tricky37

 

[r937]

try this --

SELECT Tm.Name AS Name
     , COUNT(*) AS CLIENTS
     , [blue]( SELECT COUNT(*)
           FROM Loans
          WHERE Illustration_ID 
              = Illustration.[Illustration ID] ) AS PACKSOUT[/blue]
     , SUM(IIF(Illustration.[Fpg - Type of Visit] = 3
              , 1 , 0 )) AS COLLECTION
     , SUM(IIF(Illustration.[Fpg - Type of Visit] = 4
              , 1 , 0 )) AS SALES
  FROM Illustration 
INNER
  JOIN Tm 
    ON Tm.Id = Illustration.[Fpg - ConsultantName]
 WHERE Illustration.[Fpg - Date]
       BETWEEN Forms!Export2!Date1
           AND Forms!Export2!Date2
   AND Illustration.[Call Stage] > 1 
GROUP 
    BY Tm.Name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

Thank you for the response unfortunately I get the following error:

You hve tried to execute a query that does not include the specified expression "Illustration ID" as part of an aggregate function.

 

[r937]

did you run my last query exactly as is, or did you modify it in any way?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

I copied and pasted to a new query and clicked Run.

Based on your last query I experimented with your original union query which actually works with the new code see below.

I called your last query Barry as that is who it is for and kept my original PS_Two

As it now works in the format below, please let me know if you think it would be preferable to combine into one query or the solution below will be robust to use.

SELECT u.NAME, Sum(u.CLIENTS1) AS CLIENTS, Sum(u.PACKSOUT1) AS PACKSOUT, Sum(u.COLLECTION1) AS COLLECTION, Sum(u.SALES1) AS SALES
FROM (SELECT NAME, CLIENTS1, COLLECTION1, SALES1 , NULL AS PACKSOUT1
FROM Barry
UNION ALL
SELECT NAME, NULL AS CLIENTS1 , NULL AS COLLECTION1, NULL AS SALES1, PACKSOUT1
FROM PS_Two

Kind Regards


Tricky29


) AS u
GROUP BY u.NAME;

 

[r937]

if you have something that works, you're good to go

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tricky29]

Thank you so much for all your help, it has been invaluable, and extremely helpful.

Much appreciated

Kind Regards

Tricky29