Glen Gibb
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
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
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.
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.
what you will need is a outer join (ie a full outer join), based on the row number.
ASKER
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?
Can you elaborate? Are there best practices or good suggestions to follow here?
ASKER
Using MS Access 2000. I intend to move this into SQL Server later on.
ASKER
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...
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...
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...
ASKER
Reporting tool?
I haven't thought about using one. Hoped to just throw the results into a grid that could be exported to Excel.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
I was hoping to get a working prototype first.
The whole project seems to be evolving more rapidly than I envisioned!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, and will want to order by ID as well...
ASKER
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.)
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...