• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Create a report with unrelated columns

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
Glen Gibb
Asked:
Glen Gibb
  • 7
  • 6
  • 2
2 Solutions
 
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...

0
 
Glen GibbAuthor 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.
0
 
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Glen GibbAuthor 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?
0
 
Glen GibbAuthor Commented:
Using MS Access 2000.  I intend to move this into SQL Server later on.
0
 
Glen GibbAuthor Commented:
Just another observation.  There will be 2000 or so rows / week that would be added.  Is that stretching Access to the breaking point?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the reporting tool?
creating subreports depends on that alot...
0
 
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...
0
 
Glen GibbAuthor 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.
0
 
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.
0
 
Guy Hengel [angelIII / a3]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...
0
 
Glen GibbAuthor 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!
0
 
Mark WillsTopic 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 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
 
Mark WillsTopic AdvisorCommented:
Oh, and will want to order by ID as well...
0
 
Glen GibbAuthor 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.)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now