Create a report with unrelated columns


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

Glen GibbOwnerAsked:
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...

Glen GibbOwnerAuthor Commented:
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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database/version are you using?
what you will need is a outer join (ie a full outer join), based on the row number.
Glen GibbOwnerAuthor Commented:
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?
Glen GibbOwnerAuthor Commented:
Using MS Access 2000.  I intend to move this into SQL Server later on.
Glen GibbOwnerAuthor Commented:
Just another observation.  There will be 2000 or so rows / week that would be added.  Is that stretching Access to the breaking point?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the reporting tool?
creating subreports depends on that alot...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>  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...
Glen GibbOwnerAuthor Commented:
Reporting tool?  

I haven't thought about using one.  Hoped to just throw the results into a grid that could be exported to Excel.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
Glen GibbOwnerAuthor Commented:
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!
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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, 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...

Mark WillsTopic AdvisorCommented:
Oh, and will want to order by ID as well...
Glen GibbOwnerAuthor Commented:
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.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.