Solved

Create a report with unrelated columns

Posted on 2008-06-14
15
205 Views
Last Modified: 2010-04-21
Experts,

I need to create a report with three sets of columns.  They would look something like this:

Caller Type       Total                     Insurance Type     Total                       Activity Type     Total
----------------------------------------------------------------------------------------------------------------
Broker             07                           Whole Life               14                         Form Request       12
Policy Owner  14                         Interest Sensitive      09                         Illustration Req      08

The numbers of rows would not be equal.

It's easy enough to garner the information for one column, as in the attached code.

Is there any possible way of gathering the other columns into the same report?

I've been told that you can add multiple SELECTS  --
SELECT * FROM TBL1
SELECT * FROM TBL2

but these simply will not run in MS Jet.

Can anyone lend me a hand?

Capt
SELECT     tblInsuranceType.InsuranceTypeDesc AS [Insurance Type], COUNT(Calls.InsuranceFK) AS Total
FROM (Calls INNER JOIN
    tblInsuranceType ON Calls.InsuranceFK =     tblInsuranceType.InsuranceTypePK)
WHERE     (Calls.CallDate = #6/14/2008#)
GROUP BY tblInsuranceType.InsuranceTypeDesc

Open in new window

0
Comment
Question by:Glen Gibb
  • 7
  • 6
  • 2
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787544
>The numbers of rows would not be equal.
that's the main problem, as SQL is not really meant to do so.

I think there are 2 ways to solve this:
* each "set" of columns shall be a subreport
* with some tricky processing, you can get a join via the row numbers (which is the join), but that would be inefficient the more rows there are...

0
 

Author Comment

by:Glen Gibb
ID: 21787556
You're right.  Joins don't give the results I was hoping for because there are no corresponding values for each row.  Is there a way to get blanks for the unrelated values?

Dumb question, but I'd like to know.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787562
what database/version are you using?
what you will need is a outer join (ie a full outer join), based on the row number.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Glen Gibb
ID: 21787573
I think I get the idea about subreports.   Each category would be handled separately, is that it?

Can you elaborate?  Are there best practices or good suggestions to follow here?
0
 

Author Comment

by:Glen Gibb
ID: 21787575
Using MS Access 2000.  I intend to move this into SQL Server later on.
0
 

Author Comment

by:Glen Gibb
ID: 21787583
Just another observation.  There will be 2000 or so rows / week that would be added.  Is that stretching Access to the breaking point?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787586
what is the reporting tool?
creating subreports depends on that alot...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787590
>  I intend to move this into SQL Server later on.
how much "later" ?
as there will be quite some differences to solve, is this "migration" worth? you might want to go straight with mssql server as back-end...
0
 

Author Comment

by:Glen Gibb
ID: 21787591
Reporting tool?  

I haven't thought about using one.  Hoped to just throw the results into a grid that could be exported to Excel.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787592
>There will be 2000 or so rows / week
makes 100K+ rows/year ...
how many years of data are there planned to be stored?
depending on the row size, this could put access to it's limits indeed.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 350 total points
ID: 21787597
ms access and excel are also a reporting tools :)
so, you right now planned to get the 3 "tables" side by side in ms excel sheet.
I think I would make that via some excel macro, reading the 3 tables from the database and filling the sheet as needed.
should be only a few rows of code...
0
 

Author Comment

by:Glen Gibb
ID: 21787600
Now I'm getting worried.  But I can move my dataobjects to work with SQL Server w/out too much trouble, I think.

I was hoping to get a working prototype first.

The whole project seems to be evolving more rapidly than I envisioned!
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 150 total points
ID: 21788756
The challenge / difficulty is only with the three discrete columns.

Either the report write is able to properly support columnar style reporting, or, you have three otherwise unrelated queries sitting side by side. Trying to return them side by side is the problem in a single query.

So, can export the three individual queries to excel (or use ms query inside excel) and format them on a seperate worksheet, or, use a query...

Now, access 2000 does not have a facility for row-number (in a straight query), so, if using As a once off prototype, it might be worth creating three new tables - basically same structure ID autonumber increment by 1, Description, and Total. Populate each one with the straight forward query as in your example......

then, (in sql view of a new query)

SELECT c.id, max(caller_type) as [Caller Type] ,max(ct_total) as [Total1] , max(insurance_type) as [Insurance Type] ,max(it_total) as [Total2] , max(activity_type) as [Activity Type] ,max(at_total) as [Total3] from

(
 select ID, description as caller_type, total as ct_total,'' as insurance_type, 0 as it_total, '' as activity_type, 0 as at_total from table_1
union all
 select ID,'',0, description, total,'',0 from table_2
union all
 select ID,'',0,'',0, description, total from table_3
) c
group by ID


Later in SQL server, can get the row_number as part of a query, so then do not have to populate "temp" tables to get the ID - but it is a prototype...

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21788761
Oh, and will want to order by ID as well...
0
 

Author Closing Comment

by:Glen Gibb
ID: 31467298
Thank you for your input, Experts.  I appreciate your timely and knowledgeable responses.  I will concentrate on the Excel report, but will also re-attempt the three-table combine idea.  (It proved a bit tricky.)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question