Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 06:26 AM   #1
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Advanced SubQuery for totals in graph

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?

JJSHEP89 is offline   Reply With Quote
Old 07-18-2019, 07:07 AM   #2
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,494
Thanks: 45
Thanked 80 Times in 75 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Advanced SubQuery for totals in graph

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...
Guus2005 is offline   Reply With Quote
Old 07-18-2019, 07:20 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

Quote:
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.

plog is offline   Reply With Quote
Old 07-18-2019, 07:48 AM   #4
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Advanced SubQuery for totals in graph

Quote:
Originally Posted by plog View Post
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...
JJSHEP89 is offline   Reply With Quote
Old 07-18-2019, 10:17 AM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

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/...dvanced/nz.php) call to convert all the nulls to 0s in the final query.
plog is offline   Reply With Quote
Old 07-18-2019, 12:13 PM   #6
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Advanced SubQuery for totals in graph

Quote:
Originally Posted by plog View Post

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.


Quote:
Originally Posted by plog View Post
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.
JJSHEP89 is offline   Reply With Quote
Old 07-18-2019, 12:54 PM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

Quote:
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.

Quote:
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).

plog is offline   Reply With Quote
Old 07-18-2019, 02:02 PM   #8
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Advanced SubQuery for totals in graph

Quote:
Originally Posted by plog View Post
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.

Quote:
Originally Posted by plog View Post
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");
JJSHEP89 is offline   Reply With Quote
Old 07-18-2019, 02:51 PM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

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
plog is offline   Reply With Quote
Old 07-18-2019, 03:03 PM   #10
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,494
Thanks: 45
Thanked 80 Times in 75 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Advanced SubQuery for totals in graph

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

Last edited by Guus2005; 07-18-2019 at 03:26 PM.
Guus2005 is offline   Reply With Quote
Old 07-19-2019, 07:57 AM   #11
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Advanced SubQuery for totals in graph

Quote:
Originally Posted by Guus2005 View Post
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...


Quote:
Originally Posted by Guus2005 View Post
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
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.
Attached Files
File Type: accdb IC_Stripped(2).accdb (1.37 MB, 8 views)
JJSHEP89 is offline   Reply With Quote
Old 07-19-2019, 10:09 AM   #12
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

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.
plog is offline   Reply With Quote
Old 07-19-2019, 11:01 AM   #13
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Advanced SubQuery for totals in graph

Quote:
Originally Posted by plog View Post
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
Attached Files
File Type: xlsx IC_Stripped_Data.xlsx (10.0 KB, 8 views)
JJSHEP89 is offline   Reply With Quote
Old 07-19-2019, 01:47 PM   #14
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,306
Thanks: 10
Thanked 2,257 Times in 2,209 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Advanced SubQuery for totals in graph

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.
plog is offline   Reply With Quote
The Following 2 Users Say Thank You to plog For This Useful Post:
Guus2005 (07-20-2019), JJSHEP89 (07-22-2019)
Old 07-20-2019, 03:00 AM   #15
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,494
Thanks: 45
Thanked 80 Times in 75 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Advanced SubQuery for totals in graph

Thanks PLOG for taking over.
Couldn't have said it better.

Kudos!

Guus2005 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a graph from a Report that has sub totals Lateral Reports 4 12-22-2015 11:01 AM
Referencing SubQuery Fields in a SubQuery lovelornloser Queries 6 12-04-2013 07:24 AM
Graph totals from two queries with different data maggioant Queries 1 10-02-2009 11:04 AM
Graph based on running totals maggioant Queries 0 10-01-2009 06:49 AM
Query/SubQuery that sorts and totals Marbinski Queries 1 08-30-2004 03:58 PM




All times are GMT -8. The time now is 06:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World