February 1, 2010 at 5:51 pm
I'm trying to update an access table from a sql server view. So I create a link to the view in access, then do the following query:
SELECT DISTINCT P.UPC
(select upc, cost, other_fields, ... FROM products
union all
select upc, cost, other_fields, ... FROM vwproducts
) as P
GROUP BY P.UPC, P.COST, P.other_fields
HAVING count(upc) = 1
The result of this should be a recordset containing the upc's of any rows that are not exactly the same between the two tables. When I run this against two tables, instead of a table and a view, it works perfectly. But when I do it between the table and the view it doesn't group properly.
I reduced my sql statement down until I found that it was the cost field that wasn't grouping properly. When I check the values, they are exactly the same, yet they don't get grouped together. However, I did notice that access sets the cost field to a number instead of a currency. In sql server it is 'money', and in the access table it's 'currency'. There are other 'money' fields that come across as 'currency' in the linked view, but for some reason the cost field comes across as a 'number' data type and I have no idea why.
Having said that, some of the records do group together, in fact most of them do, but about a 1/4th of them don't and there's nothing I can tell that's different about the 1/4th that don't. What's more is that I can do an INNER join between the two tables and set the where clause to only pick records that have equal costs and that works fine.
Anybody got any ideas? My first thought is to just cast the cost field to a string, but I'd really like to know what's going on here if anyone's got any thoughts.
February 1, 2010 at 6:00 pm
OK, just figured out my own problem as I read through my post again 🙂
The sql view is based off a couple of tables and the cost field was previously a 'float' data type in one of the underlying tables. After I changed it to money, I didn't realize that the view wouldn't automatically reflect this change.
Still, I do think I would consider this issue a bug with the Jet Database Engine because, in my opinion, it should either give me a typecasting error when I try to run the query, or else consider 5.42 and $5.42 to be the same if it's going to implicitly cast it for me. Furthermore, most of the dollar amounts and numbers were being grouped together, only some of them weren't and there was no rhyme or reason as to why not.
So, I believe my problem is solved, but I'd still like to hear from anyone if you have any thoughts on why it wasn't working before.
February 19, 2010 at 8:14 pm
Hi brandonmooreis,
I'm moving up to SQL Server from MS Access, so posts like yours really help me in realising the potential pitfalls I might have to face.
I can't offer tips on any personal experience of your problem, but I'd suggest you might want to look at using one of MS Access's datatype conversion functions in your original query to explicitly force the field you're having problems with to be of a certain datatype. If you do, then the one you'll need if you're using money/currency is "CCur". I'm assuming the Cost field was the one giving you grief, so try amending your SQL so it reads "CCur(Cost)" (or the relevant qualified field name) on all the "Cost" fields in your union query.
There's a chance it might work, and there again it might not, but it's worth a try.
The other option - which I really hate to mention - is this: make a query in MS Access based on your SQL view without any grouping, and then make another query (based on the first query) that you then apply the grouping to. It's a horribly ugly solution but sometimes MS Access likes to be spoonfed otherwise it throws its toys out of the pram.
It's a bit late my making suggestions after you've found a solution (albeit one you're not entirely happy with), but I do think the "CCur" datataype conversion option might have stood a chance of working. If you're not used to MS Access's little... ahem..."quirks", then it's useful to know the other related datatype conversion functions, e.g. CByte, if you're faced with similar problems in the future.
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply