Tough (for me) Query question

I've inherited a database that had a single flat table holding client
and policy information. The policy information included individual
fields for estimated commissions (i.e. "Comm 99"; "Comm 00"; "Comm
01"...) where the field was null if the policy was not active during
that year.

I've split this into client/policy tables, and I've separated the est.
commissions into a new table:
ID
POLICY_ID
COMM_YEAR
COMMISSION

Where POLICY_ID is a foriegn key to the Policy table.

The department manager does not want to change the way the reports
look, so now I have to try and recreate a flat record spanning the
client, policy, and these estimated commissions for 4 years across.
I've made three attempts with the reports: Two use Dlookup (I know, I
know...) - one with the dlookup in the query, and one with the dlookup
on the report - and run into the obvious performance hit; the third
uses 4 single-field subreports, and 4 function calls to populate the
sum of each year, but this also is quite slow.

Is there a way to build a query that will get me:
[POLICY_ID] [COMM_YEAR]=2006 [COMMISSION] [COMM_YEAR]=2005
[COMMISSION], etc.
and will allow for null years (in other words, if the policy did not
have an estimated commission for 2006, it would appear as a null on the
record)? Does this involve subqueries or using multiple instances of
the commission table?

Maybe a change in the way the Est. Commission table is designed?

Thanks for your help.
Kevin

Kevin,

Two thoughts:
1) And I'm serious here, just push harder on the notion of changing the
look of the reports. Most people will resist change at first, but often
you can make them comfortable with the change just by being nice to
them, and explaining that it's going to cost more money (or take more
time, if you're on staff) to make everything look just the way it did.

2) Look into crosstab queries. With a relatively known set of column
headings, it often makes sense to use a CT and just put in the column
headings as part of the field being split out, which will take care of
the null values.

Jeremy

Porkapalooza wrote:

Quote:

I've inherited a database that had a single flat table holding client
and policy information. The policy information included individual
fields for estimated commissions (i.e. "Comm 99"; "Comm 00"; "Comm
01"...) where the field was null if the policy was not active during
that year.
>
I've split this into client/policy tables, and I've separated the est.
commissions into a new table:
ID
POLICY_ID
COMM_YEAR
COMMISSION
>
Where POLICY_ID is a foriegn key to the Policy table.
>
The department manager does not want to change the way the reports
look, so now I have to try and recreate a flat record spanning the
client, policy, and these estimated commissions for 4 years across.
I've made three attempts with the reports: Two use Dlookup (I know, I
know...) - one with the dlookup in the query, and one with the dlookup
on the report - and run into the obvious performance hit; the third
uses 4 single-field subreports, and 4 function calls to populate the
sum of each year, but this also is quite slow.
>
Is there a way to build a query that will get me:
[POLICY_ID] [COMM_YEAR]=2006 [COMMISSION] [COMM_YEAR]=2005
[COMMISSION], etc.
and will allow for null years (in other words, if the policy did not
have an estimated commission for 2006, it would appear as a null on the
record)? Does this involve subqueries or using multiple instances of
the commission table?
>
Maybe a change in the way the Est. Commission table is designed?
>
Thanks for your help.
Kevin

Kevin,
With all due respect to Jeremy, doing the impossible for inscrutible
bosses is what we get paid for. Yes, change is sometimes necessary.
But it has to be done in a way that doesn't adversely affect revenue.
Sales people are *very* interested in accurate commissions reports.
Messing with what works without some rather large cojones is a bad
career move. So . . . I'd look at cross-tabs or the TransactSQL Case
Statement syntax for doing cross-tabs so the reports remain the same.
I'd also think about leaving the flat table as it is while you learn it
and how it is used in reports. If the flat table is a fact table then
normalizing it may actually make things worse.
Jeremy Wallace wrote:

Quote:

Kevin,
>
Two thoughts:
1) And I'm serious here, just push harder on the notion of changing the
look of the reports. Most people will resist change at first, but often
you can make them comfortable with the change just by being nice to
them, and explaining that it's going to cost more money (or take more
time, if you're on staff) to make everything look just the way it did.
>
2) Look into crosstab queries. With a relatively known set of column
headings, it often makes sense to use a CT and just put in the column
headings as part of the field being split out, which will take care of
the null values.
>
Jeremy
>
>
Porkapalooza wrote:

Quote:

I've inherited a database that had a single flat table holding client
and policy information. The policy information included individual
fields for estimated commissions (i.e. "Comm 99"; "Comm 00"; "Comm
01"...) where the field was null if the policy was not active during
that year.

I've split this into client/policy tables, and I've separated the est.
commissions into a new table:
ID
POLICY_ID
COMM_YEAR
COMMISSION

Where POLICY_ID is a foriegn key to the Policy table.

The department manager does not want to change the way the reports
look, so now I have to try and recreate a flat record spanning the
client, policy, and these estimated commissions for 4 years across.
I've made three attempts with the reports: Two use Dlookup (I know, I
know...) - one with the dlookup in the query, and one with the dlookup
on the report - and run into the obvious performance hit; the third
uses 4 single-field subreports, and 4 function calls to populate the
sum of each year, but this also is quite slow.

Is there a way to build a query that will get me:
[POLICY_ID] [COMM_YEAR]=2006 [COMMISSION] [COMM_YEAR]=2005
[COMMISSION], etc.
and will allow for null years (in other words, if the policy did not
have an estimated commission for 2006, it would appear as a null on the
record)? Does this involve subqueries or using multiple instances of
the commission table?

Maybe a change in the way the Est. Commission table is designed?

Thanks for your help.
Kevin

knogeek wrote:

Quote:

Kevin,
With all due respect to Jeremy, doing the impossible for inscrutible
bosses is what we get paid for. Yes, change is sometimes necessary.
But it has to be done in a way that doesn't adversely affect revenue.
Sales people are *very* interested in accurate commissions reports.
Messing with what works without some rather large cojones is a bad
career move. So . . . I'd look at cross-tabs or the TransactSQL Case
Statement syntax for doing cross-tabs so the reports remain the same.
I'd also think about leaving the flat table as it is while you learn it
and how it is used in reports. If the flat table is a fact table then
normalizing it may actually make things worse.
Jeremy Wallace wrote:

Quote:

Kevin,

Two thoughts:
1) And I'm serious here, just push harder on the notion of changing the
look of the reports. Most people will resist change at first, but often
you can make them comfortable with the change just by being nice to
them, and explaining that it's going to cost more money (or take more
time, if you're on staff) to make everything look just the way it did.

2) Look into crosstab queries. With a relatively known set of column
headings, it often makes sense to use a CT and just put in the column
headings as part of the field being split out, which will take care of
the null values.

Jeremy

Porkapalooza wrote:

Quote:

I've inherited a database that had a single flat table holding client
and policy information. The policy information included individual
fields for estimated commissions (i.e. "Comm 99"; "Comm 00"; "Comm
01"...) where the field was null if the policy was not active during
that year.
>
I've split this into client/policy tables, and I've separated the est.
commissions into a new table:
ID
POLICY_ID
COMM_YEAR
COMMISSION
>
Where POLICY_ID is a foriegn key to the Policy table.
>
The department manager does not want to change the way the reports
look, so now I have to try and recreate a flat record spanning the
client, policy, and these estimated commissions for 4 years across.
I've made three attempts with the reports: Two use Dlookup (I know, I
know...) - one with the dlookup in the query, and one with the dlookup
on the report - and run into the obvious performance hit; the third
uses 4 single-field subreports, and 4 function calls to populate the
sum of each year, but this also is quite slow.
>
Is there a way to build a query that will get me:
[POLICY_ID] [COMM_YEAR]=2006 [COMMISSION] [COMM_YEAR]=2005
[COMMISSION], etc.
and will allow for null years (in other words, if the policy did not
have an estimated commission for 2006, it would appear as a null on the
record)? Does this involve subqueries or using multiple instances of
the commission table?
>
Maybe a change in the way the Est. Commission table is designed?
>
Thanks for your help.
Kevin

The crosstab worked perfect, guys, thanks! I never really understood
those things until now!

Tough (for me) Query question

re: Tough (for me) Query question

re: Tough (for me) Query question

re: Tough (for me) Query question

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about