Sum of Multiple Queries 1

[sstump]

OK...I hope I explain this clearly.

I've built 4 queries to calculate different criteria. I need a 5th query to sum each up each of the sums on the prior 4 queries. The problem I'm having is if 1 of the 4 queries doesn't return a total (because there was no data) and the others do then the 5th query doesn't calculate any of the totals. I've tried different joins to get it to calculate but can't seem to get it to work. Is there a simple fix to this?

I'm not using SQL for this (cause I'm not that comfortable with it). Just building as a Query in Access.

 

[jebry]

Hi!

Since you are building it in design view what I would recommend is to wrap each field in your sums with the following:

IIf(IsNull(YourField) = True, 0, YourField)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[sstump]

Where would I put this. The original 4 queries are creating new tables for the 5th query to query from. So would I put this in the orignal queries? If so where?

I neglected to mention that I'm joining these on Date and Name fields. So it give totals per person-per week ending. Therefore I presume that I would add this to the original queries so that the dates would populate with a 0 and would be the same on each of the query, so I can create the joins then.

 

[jebry]

Hi!

I would wrap the fields used in the final query. That way you are only changing the one.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[lespaul]

First of all, I wouldn't bother having the first four queries creating new tables. You can just use the query name:

qrySomething = SELECT FIELD1, SUM(FIELD2) FROM TABLENAME


qrySomethingElse = SELECT * FROM qrySomething

Secondly, could you post the SQL from the first four queries?

Leslie

 

[sstump]

OK...done but the numbers come back the same. If it helps here's the SQL for the final Query.

SELECT DISTINCT TTAllClosedby.Supervisor, TTAllClosedby.Tech, TTAllClosedby.[Week Ending], IIf(IsNull([ttallclosedby]![CountOfTROUBLE_TICKET_ID])=True,0,[ttallclosedby]![CountOfTROUBLE_TICKET_ID]) AS [Total of All Closed TT], IIf(IsNull([TTAllOpenby]![CountOfTROUBLE_TICKET_ID])=True,0,[TTAllOpenby]![CountOfTROUBLE_TICKET_ID]) AS [Total of All Opened TT], IIf(IsNull([VRFAllClosedby]![CountOfREQUEST_ID])=True,0,[VRFAllClosedby]![CountOfREQUEST_ID]) AS [Total of All Closed VRF], IIf(IsNull([VRFAllOPENby]![CountOfREQUEST_ID])=True,0,[VRFAllOPENby]![CountOfREQUEST_ID]) AS [Total of All Open VRF], [TTAllClosedby]![CountOfTROUBLE_TICKET_ID]+[TTAllOpenby]![CountOfTROUBLE_TICKET_ID]+[VRFAllClosedby]![CountOfREQUEST_ID]+[VRFAllOpenby]![CountOfREQUEST_ID] AS Total, *
FROM ((TTAllClosedby INNER JOIN TTAllOpenby ON (TTAllClosedby.[Week Ending] = TTAllOpenby.[Week Ending]) AND (TTAllClosedby.Tech = TTAllOpenby.Tech)) INNER JOIN VRFAllClosedby ON (TTAllOpenby.[Week Ending] = VRFAllClosedby.[Week Ending]) AND (TTAllOpenby.Tech = VRFAllClosedby.Tech)) INNER JOIN VRFAllOpenby ON (VRFAllClosedby.[Week Ending] = VRFAllOpenby.[Week Ending]) AND (VRFAllClosedby.Tech = VRFAllOpenby.Tech);

 

[sstump]

The only reason I do that is cause the queries are slow and take some time and I might need to review some of the data further, so it serves as a quick reference.

Here is the SQL you requested...the final query was pasted above.

Query1 SQL:
SELECT Count(ARADMIN_BASE_TROUBLE_TICKET_NOC.TROUBLE_TICKET_ID) AS CountOfTROUBLE_TICKET_ID, [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] INTO TTAllClosedby
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_TROUBLE_TICKET_NOC ON [emp-sup].Tech = ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_USER
WHERE (((ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#));

Query2 SQL:
SELECT Count(ARADMIN_BASE_TROUBLE_TICKET_NOC.TROUBLE_TICKET_ID) AS CountOfTROUBLE_TICKET_ID, [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] INTO TTAllOpenby
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_TROUBLE_TICKET_NOC ON [emp-sup].Tech = ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_USER
WHERE (((ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#));

Query3 SQL:
SELECT Count(ARADMIN_BASE_VENDOR_REFERRAL_NOC.REQUEST_ID) AS CountOfREQUEST_ID, [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] INTO VRFAllClosedby
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_VENDOR_REFERRAL_NOC ON [emp-sup].Tech = ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_CLOSED_USER
WHERE (((ARADMIN_BASE_VENDOR_REFERRAL_NOC.REFERRAL_STOP_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending]
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#));

Query4 SQL:
SELECT Count(ARADMIN_BASE_VENDOR_REFERRAL_NOC.REQUEST_ID) AS CountOfREQUEST_ID, [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] INTO VRFAllOpenby
FROM tblWeekEnding, ARADMIN_BASE_VENDOR_REFERRAL_NOC INNER JOIN [emp-sup] ON ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_OPEN_USER = [emp-sup].Tech
WHERE (((ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_CLOSED_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_OPEN_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#));

 

[sstump]

OK...the one table that seems to be giving me a problem I removed from the 5th query and then built a 6th query. So with the total from the 5th and adding from the problem query with the IIF formula above and that seems to work...but is very messy. Not sure why it won't let me do this in the 5th query. I've gone through and done it every way I could imagine. Added this to the expression to total up all...added this to the individual columns...nothing seems to work.

 

[lespaul]

Here's one solution. Create a single query that determines where each bit of information comes from (I did that by adding "TTAllClosedBy" to each record in the first query, "TTAllOpenedBy" to each record in the second query, etc.):

SELECT Count(ARADMIN_BASE_TROUBLE_TICKET_NOC.TROUBLE_TICKET_ID) AS CountOfTROUBLE_TICKET_ID, "TTAllClosedBy', [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] 
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_TROUBLE_TICKET_NOC ON [emp-sup].Tech = ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_USER
WHERE (((ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#))
UNION
SELECT Count(ARADMIN_BASE_TROUBLE_TICKET_NOC.TROUBLE_TICKET_ID) AS CountOfTROUBLE_TICKET_ID, "TTAllOpenBy", [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending] 
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_TROUBLE_TICKET_NOC ON [emp-sup].Tech = ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_USER
WHERE (((ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_TROUBLE_TICKET_NOC.OPEN_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#))
UNION
SELECT Count(ARADMIN_BASE_VENDOR_REFERRAL_NOC.REQUEST_ID) AS CountOfREQUEST_ID, [emp-sup].Supervisor, "VRFAllClosedBy", [emp-sup].Tech, tblWeekEnding.[Week Ending] 
FROM tblWeekEnding, [emp-sup] INNER JOIN ARADMIN_BASE_VENDOR_REFERRAL_NOC ON [emp-sup].Tech = ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_CLOSED_USER
WHERE (((ARADMIN_BASE_VENDOR_REFERRAL_NOC.REFERRAL_STOP_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending]
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#))
UNION
SELECT Count(ARADMIN_BASE_VENDOR_REFERRAL_NOC.REQUEST_ID) AS CountOfREQUEST_ID, "VRFAllOpenBy", [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending]
FROM tblWeekEnding, ARADMIN_BASE_VENDOR_REFERRAL_NOC INNER JOIN [emp-sup] ON ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_OPEN_USER = [emp-sup].Tech
WHERE (((ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_CLOSED_DATE_TIME) Between [tblWeekEnding]![Start of Week] And [tblWeekEnding]![End of Week]))
GROUP BY [emp-sup].Supervisor, [emp-sup].Tech, tblWeekEnding.[Week Ending], ARADMIN_BASE_VENDOR_REFERRAL_NOC.STATUS_OPEN_USER
HAVING (((tblWeekEnding.[Week Ending])>#4/1/2004#));

Now you have all the information in a single query with identifiers of what kind of information it is. Then I would do the sum on this single table grouping by what type it is.

A second option would be for you to give us the information the tables (table and field names) that these queries are coming from, some sample data and your expected output. There may be a much simpler way of getting the count information you need.





Leslie

 

[sstump]

That didn't come out right. This is how that came out:

CountOfTROUBLE_TICKET_ID Expr1001 Supervisor Tech Week Ending
2 Perez VRFAllClosedBy Cassandra Monson 4/18/2004
2 Perez VRFAllClosedBy Glenn Chritian 5/2/2004
2 Perez VRFAllClosedBy Tyndal Johnson 4/4/2004
2 Stone VRFAllClosedBy Bruce Boydstun 4/25/2004
2 Stone VRFAllClosedBy Chemetra Monroe 4/11/2004
2 Stone VRFAllClosedBy Eric Morel 7/4/2004
2 Stone VRFAllClosedBy Magalis Correa 7/4/2004
2 Stone VRFAllClosedBy Marilyn Johnson 7/4/2004
2 Stone VRFAllClosedBy Tina Ellis-Williams 4/11/2004
2 Stone VRFAllClosedBy Tina Ellis-Williams 5/9/2004
2 TTAllClosedBy Perez Glenn Chritian 5/9/2004
2 TTAllClosedBy Perez Glenn Chritian 6/20/2004
2 TTAllOpenBy Perez Gustavo Ordonez 5/16/2004
2 VRFAllOpenBy Perez Gustavo Ordonez 5/23/2004
2 VRFAllOpenBy Perez Gustavo Ordonez 5/30/2004
2 VRFAllOpenBy Perez Gustavo Ordonez 6/6/2004
2 VRFAllOpenBy Stone Bruce Boydstun 4/4/2004
2 VRFAllOpenBy Stone Denzil Navolt 5/9/2004
2 VRFAllOpenBy Stone Denzil Navolt 6/13/2004
2 VRFAllOpenBy Stone Doug Brown 4/4/2004
2 VRFAllOpenBy Stone Doug Brown 4/18/2004
2 VRFAllOpenBy Stone Doug Brown 6/6/2004
3 Perez VRFAllClosedBy Darlene Anthony 5/30/2004
3 Stone VRFAllClosedBy Denzil Navolt 7/4/2004
3 Stone VRFAllClosedBy Marilyn Johnson 6/27/2004
3 Stone VRFAllClosedBy Te'Reka Wilson 5/16/2004
3 TTAllClosedBy Perez Glenn Chritian 4/25/2004
3 TTAllClosedBy Perez Glenn Chritian 5/30/2004
3 TTAllOpenBy Perez Alfryon McClay 4/4/2004
3 TTAllOpenBy Perez Alfryon McClay 6/6/2004
3 VRFAllOpenBy Stone Doug Brown 5/16/2004

It's transposing the field names and the supervisors names back and forth between the 2nd and 3rd column.

 

[sstump]

Oh and it's not giving me a count of the numbers.

I'll post some examples shortly.

 

[sstump]

OK here's some examples:

This the first make table..."ttallclosedby":table

CountOfTROUBLE_TICKET_ID Supervisor Tech Week Ending
123 Perez Alfryon McClay 4/4/2004
157 Perez Alfryon McClay 4/11/2004
215 Perez Alfryon McClay 4/18/2004
217 Perez Alfryon McClay 4/25/2004
190 Perez Alfryon McClay 5/2/2004
432 Perez Alfryon McClay 5/9/2004

You'll notice it give a count by the individual by week ending and by supervisor.

Essentially I have 4 tables that (should) all look the same. Now what I want to see is this all tables combined and giving me a total of all.

Supervisor Tech Week Ending TTAllClosedby.CountofTROUBLE_TICKET_ID TTAllOpenby.CountofTROUBLE_TICKET_ID VRFAllClosedby.CountOfREQUEST_ID VRFAllOpenby.CountOfREQUEST_ID Total
Perez Alfryon McClay 4/4/2004 123 3 164 6 296
Perez Alfryon McClay 4/18/2004 215 45 218 32 510
Perez Alfryon McClay 4/25/2004 217 34 244 30 525
Perez Alfryon McClay 5/2/2004 190 5 206 14 415
Perez Alfryon McClay 5/9/2004 432 17 535 18 1002

Notice however that there is no data for 4/11/04. Because while this table was populated there was another one that wasn't. Therefore it didn't add anything for that week because I can't get it to assume 0 in the initial table or in the final query...so it just left it out all together.

Does that make sense?

 

[lespaul]

Ok, it looks like the fields may not be lined up correctly. You will need to check the SELECT statements and make sure that they are in the same order.

COUNT, TYPE, Supervisor, Tech, WeekEnding

are the 2s and 3 in the first column not the counts?



Leslie

 

[sstump]

Yea...that gave me exactly what I needed.

Thank you so very much Leslie!!

OH and yes they are the counts...at first I thought they were just numbered by date and such.

Thanks again! Have a star!

 

[sstump]

And I must say that I learned a great deal of information on this trek!

Thank you everyone for your help.

 

[lespaul]

Thanks for the star! Always glad to help!

Leslie