I have created a report that displays a count for five controls for each month. The controls are yes/no boxes, so each control can have a value of true, false or null. I created two subreports based on crosstab queries for each control - one for true and one for false/null. I would like to create a result like the following in my report:
Year1
Control1T CountJan, CountFeb,...
Control1F CountJan, CountFeb,...
Control1Total Jan, Feb,...
Control2T ...
.
.
.
Year2...
Currently, I have the following (with all results for each subreport kept together):
Year1Control1T CountJan, CountFeb,...
Year2Control1T CountJan,..
Year1Control1F, CountJan,..
Year2Control1F, CountJan,..
Control1Total Jan, Feb,...
I feel like I'm missing something obvious here, or maybe subreports can't be split up like this?
Once again, I appreciate your help.
6 2042 NeoPa 32,556
Expert Mod 16PB
I've read this a couple of times now and still can't follow what you're asking.
This may be because I'm tired and ready for bed.
Alternatively it could be because you start in the middle and never explain your table structure or the data you're working with.
If you want help then I suggest you make it easier for us to understand the question. Here is a link that may help you formulate a more readily understandable question ( POSTING GUIDELINES: Please read carefully before posting to a forum).
I've read this a couple of times now and still can't follow what you're asking.
This may be because I'm tired and ready for bed.
Alternatively it could be because you start in the middle and never explain your table structure or the data you're working with.
If you want help then I suggest you make it easier for us to understand the question. Here is a link that may help you formulate a more readily understandable question (POSTING GUIDELINES: Please read carefully before posting to a forum).
It's probably my bad explanation, so I'll try again.
I have two tables:
Table:Work Order
Fields:
Job# (Primary Key)
Date Task Requested (Date)
KPI Required (Yes/No checkbox)
Table:KPI (Key Performance Indicators)
Fields:
KPI Record # (Primary Key)
Job# (From Work Order Table)
Respond to Customer (Date)
KPI1(Yes/No checkbox)
Return from Trip (Date)
Date Debrief Customer (Date
KPI2 (Yes/No checkbox)
Draft Report (Date)
KPI3 (Yes/No checkbox)
Final Report (Date)
KPI4 (Yes/No checkbox)
Certification (Date)
KPI5 (Yes/No checkbox)
The various dates determine whether or not the KPIs have been met. For example, KPI1 is met if customers are responded to within two days of a job request. KPIs 2-5 are determined by how soon after a team returns from a trip a particular task is accomplished.
So, I needed a report that could give me counts (based upon the KPI Record #)of the KPIs that have/have not been met by month, quarter or year, irrespective of a particular job. I created two crosstab queries for each KPI (one for a true and one for a false/null value), sorted by month to start. I was able to create a report (using the crosstabs as subreports) that showed each KPI in order with counts for each month:
KPI1T TotalRecords JanCount FebCount Etc. (subreport1)
KPI1F...(subreport2)
KPI2T...(subreport3)
However, if more than one year is involved, I get the following:
YearA KPI1T TotalRecords JanCount FebCount Etc. (subreport1)
YearB KPI1T TotalRecords JanCount FebCount Etc. (subreport1)
YearA KPI1F...(subreport2)
YearB KPI1F...(subreoprt2)
My question: Is there a way to achieve the following sorted result using subreports?
YearA KPI1T TotalRecords JanCount FebCount Etc. (subreport1)
YearA KPI1F TotalRecords JanCount FebCount Etc. (subreport1)
YearA KPI2T...(subreport2)
YearA KPI2F...(subreoprt2), etc.
YearB KPI1T TotalRecords JanCount FebCount Etc. (subreport1)
etc.
I'm hoping you might have a suggestion on how to procede (even if it's doing something completely different). Otherwise, I am currently trying to see if it would be easier base my report on a separate table that looks like this (although I'm having trouble passing the correct values from the data entry forms):
Table Name:KPICount
Date
KPI# (1-5)
KPIValue (T,F,Null)
Once again, I appreciate your help, and I hope this explanation is clearer than the last one.
NeoPa 32,556
Expert Mod 16PB
It certainly looks as if the bases are covered at first glance.
Can't devote much time atm so will bookmark and revisit later.
Can you post the SQL of the two crosstab queries for the subreports
and ...
what is the record source of the main report?
how are the subreports being related to the main reports record source?
NeoPa 32,556
Expert Mod 16PB
Having looked a little more closely I'm not sure I can be much help to you.
I'm week on both subreports and cross-tab queries. I'm making some progress in here on subforms but I don't think my limited experience can be of any help. As an interested party I'll still keep an eye on the thread but I think you're in better hands now, for this question certainly.
Here is the SQL code as requested for a False result: - TRANSFORM Count(KPI.[KPI Record #]) AS [The Value]
-
SELECT Format([Return from Trip],"yyyy") AS Expr2, KPI.KPI2,
-
Count(KPI.[KPI Record #]) AS [Total Of KPI Record #]
-
FROM KPI
-
WHERE (((KPI.KPI2)=False) AND ((KPI.[Return from Trip]) Is Not Null))
-
GROUP BY Format([Return from Trip],"yyyy"), KPI.KPI2, KPI.[Return from Trip]
-
PIVOT Format([Return from Trip],"mmm") In
-
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
and for True: - TRANSFORM Count(KPI.[KPI Record #]) AS [The Value]
-
SELECT Format([Return from Trip],"yyyy") AS Expr2, KPI.KPI2,
-
Count(KPI.[KPI Record #]) AS [Total Of KPI Record #]
-
FROM KPI
-
WHERE (((KPI.KPI2)=True) AND ((KPI.[Return from Trip]) Is Not Null))
-
GROUP BY Format([Return from Trip],"yyyy"), KPI.KPI2
-
PIVOT Format([Return from Trip],"mmm") In
-
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
The only difference between the subreports besides KPI#, and whether they are for true or false results, is that KPI1 is based on a different date from a different table. Because of this, the subreports are not linked at this time (perhaps this is my problem). I currently have the crosstab query for a KPI1 True result as the basis for the main report with all other KPI results as subreports. I have tried creating a blank report and adding all KPI results as subreports, but that didn't seem to make much difference.
In case you need it, here is an example of the KPI1 SQL from the KPI1T crosstab query: - TRANSFORM Count(KPI.[KPI Record #]) AS [The Value]
-
SELECT Format([Work Order].[Date Task Requested],"yyyy") AS Expr2,
-
KPI.KPI1, Count(KPI.[KPI Record #]) AS [Total Of KPI Record #]
-
FROM [Work Order] INNER JOIN KPI ON [Work Order].[JIN #] = KPI.[JIN #]
-
WHERE (((KPI.KPI1)=True) AND (([Work Order].[Date Task Requested])
-
Is Not Null))
-
GROUP BY Format([Work Order].[Date Task Requested],"yyyy"), KPI.KPI1
-
PIVOT Format([Work Order].[Date Task Requested],"mmm") In
-
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Anne |
last post by:
I have a report containing multiple subreports (approximately 37
subreports) that are based on individual queries. The query calls to
only display the subreport if there is valid data in the table...
|
by: Don Sealer |
last post by:
I have a report that includes 5 different subreports. I'd like to be
able to open this report using a date function (Start Date and End Date).
I'd like all five subreports to show the data from...
|
by: Don Sealer |
last post by:
I have a report that includes 5 different subreports. I'd like to be
able to open this report using a date function (Start Date and End Date).
I'd like all five subreports to show the data from...
|
by: KartoffelKiffer |
last post by:
Hello,
i have to do a little Crystal Report task, where i need help. I have a
mainreport in which are some subreports. The size of the subreports
can vary so the subreport which could be bigger...
|
by: midlothian |
last post by:
I need to create a large book of work for my company, with many
different reports using different data. It all needs to come together
in one book, to be saved in PDF format. Some of the reports in...
|
by: David |
last post by:
Folks,
I have four subreports in a report in the detail section. I suppose I
could move them to the footer of the report. Anyway, if any of them
have data, I want the main report to force a...
|
by: John P |
last post by:
Hi all
I am trying to create a report which accurately replicates a standard form, so layout is critical.
The required report will be multiple pages, each (Landscape) page must be split...
|
by: Brett Barry: Go Get Geek! |
last post by:
Hello,
I have a main report with a Record Source, a DateToday table, that has
the current Month and Year. I have about 60 queries, each pulling
different data via ODBC, that I am creating...
|
by: glenfernandez |
last post by:
HI there,
Need a little help with the reporting component of my MS Access 2002 project and would appreciate any insight / help from the experts. Please bear with me as I am still learning Access...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |