Advanced SubQuery for totals in graph (1 Viewer)

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
I have a report containing a series of line graphs showing the number of service calls (for maintenance on equipment) performed each month. The service calls are categorized based on the reason for the service call. Im attempting to give a further breakdown of the progression of each category all on the same line graph. To do this i'm attempting to use a totals query with sub-queries(Using the IN() statement) to make this work. I can get the sub-query to return the data I need when run independently but it fails when combined into the main query. here's the further details...

The data needs to show a total number of service calls per month for the last 2 years, for each category & specific to each facility.

so I have set up the sub query with two tables Service Calls & Employees linked by an employee ID (employees table determines facility, in this case 'HTD'). I wrote an IN statement to look at the service calls ID (PCL_ID) and select only the ID's WHERE the JCDept field (facility) and PCL-Category match the criteria.

Code:
SELECT PressCallLog.PCL_Date, PressCallLog.PCL_ID
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
WHERE (((PressCallLog.PCL_ID) In (SELECT PCL_ID FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID WHERE (PCL_Category Like 'Material Variation') AND (JCDept = 'HTD'))));

This runs just fine and gives me the data i need, so i then copied this SQL, removed the PCL_Date field, and pasted it as another IN() statement into my main query.

The main totals query will have a formatted column for each category to give me a total count by month for the past two years. Here is the resulting SQL for limited to just the first category.

Code:
SELECT Format([PCL_Date],"yyyy-mm") AS [Year-Month], Count(PressCallLog.PCL_ID) AS [Material Variation]
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
GROUP BY Format([PCL_Date],"yyyy-mm")
HAVING (((Format([PCL_Date],"yyyy-mm"))>(Year(Now())-2)) AND ((Count(PressCallLog.PCL_ID)) In (SELECT PressCallLog.PCL_ID
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
WHERE (((PressCallLog.PCL_ID) In (SELECT PCL_ID FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID WHERE (PCL_Category Like 'Material Variation') AND (JCDept = 'HTD')))))))
ORDER BY Format([PCL_Date],"yyyy-mm");

The issue comes in when i run this, because it doesn't list the results by month, it instead counts the records and returns a yearly total which doesnt even match my calculated total(when done manually).

this is the first subquery im attempting so is there something im missing here?
 

Guus2005

AWF VIP
Local time
Today, 18:07
Joined
Jun 26, 2007
Messages
2,645
Code:
HAVING (((Format([PCL_Date],"yyyy-mm"))>(Year(Now())-2)) AND ((Count(PressCallLog.PCL_ID)) In (SELECT PressCallLog.PCL_ID
This seems a bit strange. This construction in the HAVING clause. Where you count the number of PCL_ID and use the IN keyword for the subquery...
 

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
it doesn't list the results by month,

Yes it does. Look at your SELECT clause the two things in there are a Year-Month field and a Count. By definition its listing the count by Year/month, so yes it does that.

Code:
...((Count(PressCallLog.PCL_ID)) In (SELECT PressCallLog.PCL_ID...

The above is a more focused snippet of code to demonstrate what Guus is pointing out. For some reason your comparing counts of ID numbers to ID numbers. Why? What does that do.

Suppose you had a table with just 2 records, both with the ID=1. When you do a count you get 2 (because two records), then if you bump that up against the actual ID number (1) you will get no results because the Count (2) doesn't equal the actual value (1). That line of code above has to be wrong.

Perhaps you can demonstrate what your issue is with actual data.
 

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
Yes it does. Look at your SELECT clause the two things in there are a Year-Month field and a Count. By definition its listing the count by Year/month, so yes it does that.

True, you got me there, it just doesn't display the results the way i expected.. it returns the following...

Year-Month Count of Material variation
2017-11 200
2018-08 221
2019-05 181

where i should be getting months 01 to 12 for each year...
 

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
You can't create data in a query that doesn't exist in an underlying table. If you have no data for 2017-10, then you will not get any records in your query. So, you need 2017-10 in an underlying table.

What you are going to need is a table that lists the months you want to report on something like so:

ReportingPeriods
ReportingMonth
2010-01
2010-02
...
2010-11
2010-12
...


Once you have that table you can LEFT JOIN the query you are working on to it, showing all from that ReportingTable and using a NZ(https://www.techonthenet.com/access/functions/advanced/nz.php) call to convert all the nulls to 0s in the final query.
 

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
Code:
...((Count(PressCallLog.PCL_ID)) In (SELECT PressCallLog.PCL_ID...

The above is a more focused snippet of code to demonstrate what Guus is pointing out. For some reason your comparing counts of ID numbers to ID numbers. Why? What does that do.

Suppose you had a table with just 2 records, both with the ID=1. When you do a count you get 2 (because two records), then if you bump that up against the actual ID number (1) you will get no results because the Count (2) doesn't equal the actual value (1). That line of code above has to be wrong.

Perhaps you can demonstrate what your issue is with actual data.

I'm not quite following here, I thought the In() Clause essentially provided a dataset to the main query to further refine. So i thought it would require a common key but I guess i'm not understanding its function entirely.

Unfortunately i'll have to create a mock up to send out which may take more time than its worth... I'll see what i can throw together.


You can't create data in a query that doesn't exist in an underlying table. If you have no data for 2017-10, then you will not get any records in your query. So, you need 2017-10 in an underlying table.

The data is all there in the underlying tables. We typically have about 50-60 records, all time stamped, for each month for each plant so it definitely exists. I've been able to get the following SQL to return all the ID's and their corresponding "Year-Month" of occurrence.

Code:
SELECT PressCallLog.PCL_ID, Format([PCL_Date],"yyyy-mm") AS [Year-Month]
FROM PressCallLog
WHERE (((PressCallLog.PCL_ID) In (SELECT PCL_ID FROM PressCallLog 
INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID 
WHERE (PCL_Category Like 'Material Variation') AND (JCDept = 'HTD'))) 
AND ((Format([PCL_Date],"yyyy-mm"))>(Year(Now())-2)))
ORDER BY Format([PCL_Date],"yyyy-mm");

but i need to get a count of this data for each month for each category so that the output of my query gives me a table where the year-month is in the first column and the counts for each category are listed in subsequent columns. this will then be used for the graph.
 

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
I thought the In() Clause essentially provided a dataset to the main query to further refine
FROM declares the source your data comes from.

IN is used in the WHERE clause and acts as criteria, every value in IN() can pass through to the query.

INNER JOIN does both--it adds a datasource to the query as well as acts as a filter, limiting the data that goes into the query to just whatever matches between it and the data source to which it is joined.

The data is all there in the underlying tables.

Possibly, but if it is, its not meeting the criteria of your criteria (INNER JOIN, WHERE and/or HAVING clauses).

If the last query you posted returns all the granular records you need, the next step is simple. Make an aggregate query using it as the source:

Code:
SELECT COUNT(PCL_ID) AS RecordCount, [Year-Month]
FROM ThatQueryNameHere
GROUP BY [Year-Month]

Bonus--life will be easier if you only use alphanumeric characters for field names---no spaces, no dashes (e.g. YearMonth).
 

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
If the last query you posted returns all the granular records you need, the next step is simple. Make an aggregate query using it as the source:

Code:
SELECT COUNT(PCL_ID) AS RecordCount, [Year-Month]
FROM ThatQueryNameHere
GROUP BY [Year-Month]

Except that this would only get me the count for one category, while i need counts for 9 different categories, all on the same datasheet that can be used for a graph.

Bonus--life will be easier if you only use alphanumeric characters for field names---no spaces, no dashes (e.g. YearMonth).

Year-Month is just a reformatting of the PCL_Date field which is a time stamp. Its just a column heading.

here's all the details...
there are two tables PressCallLog (Records the service calls done) & dbo_ICEp_vwEmpBasic (view of employees table connected through ODBC to enterprise system)

PressCallLog table contains
PCL_ID
PCL-Date (Timestamp)
PCL_Category (9 different categories)
PCL_ Employee (ID# field)
among others that are irrelevant here

dbo_ICEp_vwEmpBasic
EmpID
JCDept (job costing department, which is a three letter code describing facility and department)

the query i am trying to create should return the year and month values going two years back in the first column, the second through the 10th column should return the total count for each of the 9 categories for each recorded month, All of the records should be from a specific Job costing department. in this case is "HTD"

this is what i have thus far, which is closer than where i was this morning.

Code:
SELECT Format([PCL_Date],"yyyy-mm") AS [Year-Month], Count(PressCallLog.PCL_ID) AS [Material Variation], Count(PressCallLog.PCL_ID) AS Wear, Count(PressCallLog.PCL_ID) AS Other
FROM PressCallLog LEFT JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
WHERE (((dbo_ICEp_vwEmpBasic.JCDept)="HTD"))
GROUP BY Format([PCL_Date],"yyyy-mm")
HAVING (((Format([PCL_Date],"yyyy-mm"))>(Year(Now())-2)) AND ((Count(PressCallLog.PCL_ID)) In (SELECT PCL_ID FROM PressCallLog 
WHERE (PCL_Category Like 'Material Variation'))) AND ((Count(PressCallLog.PCL_ID)) In (SELECT PCL_ID FROM PressCallLog 
WHERE (PCL_Category Like 'Wear'))) AND ((Count(PressCallLog.PCL_ID))="dbo_ICEp_vwEmpBasic"))
ORDER BY Format([PCL_Date],"yyyy-mm");
 

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
To help you further I am going to need data example. Please provide 2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data your ultimate query should return when you feed it the data from A.

Again, 2 sets of data--starting and ending. You can use a spreadsheet and upload that or use thsi format for posting in a post:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Dave, 1/1/2018, 44
Sally, 5/12/2017, 31
Larry, 3/9/2018, 12
 

Guus2005

AWF VIP
Local time
Today, 18:07
Joined
Jun 26, 2007
Messages
2,645
You are still comparing a count to a table field PCL_ID. Three times now. That doesn't seem right.
Code:
...((Count(PressCallLog.PCL_ID))="dbo_ICEp_vwEmpBasic")...
The count (int or long) is compared to a string. This can not produce any records. If it does, it is a miracle.

With this kind of queries it is better that you split the query in logical parts. Each with its own name.

You have 9 categories of which you want to count the number of records.
Seems to me that you need a CROSSTAB query.
Turn the table 90 degrees and make the categories column names.

Like PLOG said, it is better that you provide a sample database with a few tables and records describing the problem.

HTH:D
 
Last edited:

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
You are still comparing a count to a table field PCL_ID. Three times now. That doesn't seem right.
Code:
...((Count(PressCallLog.PCL_ID))="dbo_ICEp_vwEmpBasic")...
The count (int or long) is compared to a string. This can not produce any records. If it does, it is a miracle.

I'm not sure where this is coming from exactly, it only appears in the SQL and doesn't make sense when i look at it in MS Access design view. I removed it and tried to run again but no luck...


With this kind of queries it is better that you split the query in logical parts. Each with its own name.

You have 9 categories of which you want to count the number of records.
Seems to me that you need a CROSSTAB query.
Turn the table 90 degrees and make the categories column names.

Like PLOG said, it is better that you provide a sample database with a few tables and records describing the problem.

HTH:D

A crosstab query may be better here, i just haven't worked with them much so i will need a bit of a refresher.

Here's the stripped database, inside are the two tables with some sample records for each, the two queries I've been playing with (query8 is the most recent) & the report with the graph i've been referencing.
 

Attachments

  • IC_Stripped(2).accdb
    1.4 MB · Views: 101

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
Tat's only part of what I asked for. That's the A part, now please provide the B part: the expected results of the data in A. Show me what data you expect to end up with from your query based on the data you provided.

No explanations, no non-working SQL, data. Provide me with what data the query should produce based on the data you just provided.
 

JJSHEP89

Registered User.
Local time
Today, 12:07
Joined
Aug 18, 2016
Messages
121
Tat's only part of what I asked for. That's the A part, now please provide the B part: the expected results of the data in A. Show me what data you expect to end up with from your query based on the data you provided.

No explanations, no non-working SQL, data. Provide me with what data the query should produce based on the data you just provided.

attached here
 

Attachments

  • IC_Stripped_Data.xlsx
    10 KB · Views: 111

plog

Banishment Pending
Local time
Today, 12:07
Joined
May 11, 2011
Messages
11,611
Perfect and Guus was right--you need a cross-tab. I did this in 2 queries, the first SQL is this:

Code:
SELECT Year([PCL_Date]) AS CallYear, Month([PCL_Date]) AS CallMonth, PressCallLog.PCL_Category, Count(PressCallLog.PCL_Date) AS Calls
FROM PressCallLog
GROUP BY Year([PCL_Date]), Month([PCL_Date]), PressCallLog.PCL_Category;

Paste the above code into a Query object and name it "sub1". It aggregates your data and gets it ready for the cross tab. Below is the SQL for the final query:

Code:
TRANSFORM Sum(sub1.Calls) AS SumOfCalls
SELECT [CallYear] & "-" & Format([CallMonth],"00") AS YearMonth
FROM sub1
GROUP BY [CallYear] & "-" & Format([CallMonth],"00")
PIVOT sub1.PCL_Category;

That gets you 90% of the way there which is as good as you will get in Access without a lot of hacking things together. It does not put your columns in the order you dictated, nor does it convert nulls to 0.

If you need it exaclty as you provided in your Excel file, I suggest using Excel. What you would do is run sub1 and paste the data into Excel. Then you would use it in a Pivot Table. That will give you the layout of a cross-tab, but allow you more configurability. You will be able to easily convert nulls to 0s and put the columns in the exact order you desire.
 

Guus2005

AWF VIP
Local time
Today, 18:07
Joined
Jun 26, 2007
Messages
2,645
Thanks PLOG for taking over.
Couldn't have said it better.

Kudos!
 

Users who are viewing this thread

Top Bottom