Create a report with unrelated columns

Posted on 2008-06-14
Medium Priority
Last Modified: 2010-04-21

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  --

but these simply will not run in MS Jet.

Can anyone lend me a hand?

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

Open in new window

Question by:Glen Gibb
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
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...


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.
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.
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


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?

Author Comment

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

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?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787586
what is the reporting tool?
creating subreports depends on that alot...
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...

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.
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.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1400 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...

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!
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 600 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...

LVL 51

Expert Comment

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

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

800 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