by Hello everyone.
To simplify things for my users, I created a view on a SQL Server 7
table where I summarize two columns which have datatype decimal(13,2).
Nulls are NOT allowed for these 2 columns. Nor are nulls allowed on
any of the fields in the GROUP BY clause of the create view statement.
Everything apears to be working correctly on SQL Server 7. The columns
are summed correctly.
However, when my users link the view into MS Access 97 using ODBC, the
two 'new' columns in the view that were created by the SUM function,
are seen by MS Access as having a dataype of TEXT with length 255?!?
*** Does anyone know why MS Access views these columns as TEXT?
The work around I have to use to fix the problem is to use the CAST
function to explicitly set the datatypes of the two new columns to be
decimal(14,2). However, this adds a bit of overhead, and I would just
as soon not use it.
Thank you.
Glen
Co-operators Life Insurance
Below is the CREATE VIEW statement. The columns giving me the problem
are [premium_amount] and[claim_amount]
CREATE VIEW dbo.v_prem_claim_sum_oj AS
SELECT PC.group_number,
PC.account_number,
PC.product_id,
PC.source,
PC.year,
PC.month,
[premium_amount] = CAST(SUM(PC.premium_amount) AS DECIMAL(14,2)),
[claim_amount] = CAST(SUM(PC.claim_amount) AS DECIMAL(14,2)),
[aso_ind] = MAX(PC.aso_ind),
[product_type] = MAX(PC.product_type),
policy_type = MAX(ISNULL(GC.policy_type, '~')),
company_code = MAX(ISNULL(GC.company_code, '~~~~~~~~~~')),
tpa_number = MAX(ISNULL(AM.tpa_number, '~~~~~~~~~~')),
group_sales_code = MAX(ISNULL(AM.group_sales_code, '~~~~~~~')),
agent_number = MAX(ISNULL(PA.agent_number, '~~~~~~~~~~'))
FROM prem_claim AS PC
LEFT OUTER JOIN grpdmart..account_master as AM
ON PC.group_number = AM.group_number AND
PC.account_number = AM.account_number
LEFT OUTER JOIN grpdmart..group_control as GC
ON PC.group_number = GC.group_number
LEFT OUTER JOIN grpdmart..primary_agent as PA
ON PC.group_number = PA.group_number AND
PC.account_number = PA.account_number
group by PC.group_number, PC.account_number, PC.product_id, PC.source,
PC.year, PC.month