February 14, 2005 at 4:17 pm
Hi All,
I've recently upsized an Access 2000 database to an MSDE 1 SQL server database. I'm having problems with the following query:-
SELECT
tblProjectQuotes.IDProject,
tblProjectQuoteTemplateItems.Name AS QuoteTemplateType,
tblProjectQuoteItems.Value AS CostPriceTotal,
tblProjectQuoteItems.SellPrice AS SellPriceTotal,
tblProjectQuoteTemplateItems.IsPrivate
FROM
tblProjectQuoteTemplateItems INNER JOIN
(tblProjectQuotes INNER JOIN tblProjectQuoteItems
ON tblProjectQuotes.ID = tblProjectQuoteItems.IDProjectQuote) ON
tblProjectQuoteTemplateItems.ID = tblProjectQuoteItems.IDProjectTemplateItem
WHERE
tblProjectQuoteTemplateItems.IDTemplateItemType=2
ORDER BY
tblProjectQuoteTemplateItems.IDTemplateItemType;
Although this query works within SQL Server, what I actually want to do is basically return a list of the sum of the [CostPriceTotal] and [SellPriceTotal] fields, grouping by [IDProject] and [QuoteTemplateType] consecutively.
[CostPriceTotal] is a nvarchar data type, which can contain text as well as numeric data. But as far as this query is concerned, the data retrieved will either be numeric or null.
[SellPriceTotal] is a money data type, which can be numeric or null.
[IsPrivate] is a bit field.
I understand that you can force convert [CostPriceTotal] and [SellPriceTotal] to avoid nulls by using ISNULL([field],0). How do I go about converting this into an aggregate SUM, GROUP by query?
The original Access query is:-
SELECT
tblProjectQuotes.IDProject,
tblProjectQuoteTemplateItems.Name AS QuoteTemplateType,
Sum(tblProjectQuoteItems.Value) AS CostPriceTotal,
Sum(tblProjectQuoteItems.SellPrice) AS SellPriceTotal, tblProjectQuoteTemplateItems.IsPrivate
FROM
tblProjectQuoteTemplateItems INNER JOIN
(tblProjectQuotes INNER JOIN tblProjectQuoteItems
ON tblProjectQuotes.ID = tblProjectQuoteItems.IDProjectQuote) ON
tblProjectQuoteTemplateItems.ID = tblProjectQuoteItems.IDProjectTemplateItem
GROUP BY
tblProjectQuotes.IDProject,
tblProjectQuoteTemplateItems.Name,
tblProjectQuoteTemplateItems.IsPrivate,
tblProjectQuoteTemplateItems.IDTemplateItemType
HAVING
(tblProjectQuoteTemplateItems.IDTemplateItemType=2)
ORDER BY
tblProjectQuoteTemplateItems.IDTemplateItemType;
Please help.
February 14, 2005 at 9:17 pm
select sum(cast( isnull([CostPriceTotal] ,0) as float)) will do
My Blog:
February 14, 2005 at 11:52 pm
Thanks Dinesh. I (finally) came to the same conclusion but converted it to money (as this is what data value would remain within this field after the WHERE filter is applied).
After doing some further checking I realised that when I upsized from Access 2000, the Wizard changed the Access Variant data type to nvarchar within this field.
This itself is not a problem, but the Wizard seemed to literally convert currency (money) values to a string (char) (e.g) "£XX.XX" (instead of "XX.XX"). The additional pound symbol would confuse the query and so any attempts of summing this field were rejected.
My advice to other people upsizing is to check the output of the final SQL Server tables for consistency.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply