Link to home
Start Free TrialLog in
Avatar of Glen Gibb
Glen GibbFlag for Canada

asked on

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

Avatar of Glen Gibb

ASKER

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

I haven't thought about using one.  Hoped to just throw the results into a grid that could be exported to Excel.
>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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh, and will want to order by ID as well...
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.)