UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Best Field Types For Calculations, SQL Server 2012    
 
   
Everettc4
post Aug 7 2019, 06:57 PM
Post#1



Posts: 212
Joined: 12-June 06
From: Oregon


Hi all hey I am in the rapids with a SQL migration been learning a ton but.... now I have come to a query in access that calculates so reports can summarize. works great in access but linking to the exported SQL tables the query crashes. I'm pretty sure it's due to the field type SQL set when I exported. The question is what type of field is best for calculations when pulling into access? Also I'm not sure why SQL put brackets [] around some fields could that be causing problems??

Thanks for the help!

Attached File(s)
Attached File  SQL_FeildTypes.JPG ( 50.39K )Number of downloads: 8
 
Go to the top of the page
 
 
Start new topic
Replies
GroverParkGeorge
post Aug 8 2019, 08:54 AM
Post#2


UA Admin
Posts: 37,507
Joined: 20-June 02
From: Newcastle, WA


I agree with crystal’s analysis of the problem.

One thing to look for is whether SSMA protected your table by wrapping invalid field names in square brackets and saved them as part of the actual field names. In other words, any field name can be rendered with square brackets in SQL, but here you probably have them literally part of the name because SSMA is smart enough to recognize non valid naming as Crystal explained.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Everettc4
post Aug 8 2019, 11:45 AM
Post#3



Posts: 212
Joined: 12-June 06
From: Oregon


Wow, thanks for all the good input. I am going to try renaming to SQL friendly names for sure, seems underscores are not preferred either so no space ProperCase is the way to go. I started building this db back in 97 things have certainly changed.

I will report back how it goes.

Thanks

Any recommendations on the best field type to use for numbers that will be calculated?? like b/wgt x c/wgt the numeric values would be 142.5 x 48.60
Go to the top of the page
 
GroverParkGeorge
post Aug 8 2019, 12:06 PM
Post#4


UA Admin
Posts: 37,507
Joined: 20-June 02
From: Newcastle, WA


The precision question is one that doesn't have a hard-and-fast rule, exactly. You need to decide how precise the results of your calculations must be and provide for that.

See Crystal's review of datatypes.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 01:23 PM