Solved

Create a report with unrelated columns

Posted on 2008-06-14
15
202 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 142

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 142

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
 

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787586
what is the reporting tool?
creating subreports depends on that alot...
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 142

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 142

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 142

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now