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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 210
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
 
strive4peace
post Aug 7 2019, 08:02 PM
Post#2


strive4peace
Posts: 20,464
Joined: 10-January 04



hi Everett,

>" 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"

Maybe (did you use Byte, Memo/Long Text, Attachment, or other known problem type?)
... so, maybe not ... I see bad names.

Some of your fieldnames use bad characters like / and space. Better not to do that, and use something else instead -- maybe underscore (_), or use ProperCase to identify different words/abbreviations ? When there are no suspect characters (stick to alphabet letters A-Z, a-z, followed perhaps with numbers 0-9, and/or include underscore. ALWAYS Start with letter), there is no need to surround names with brackets to be properly understood.

> "why SQL put brackets [] around some fields"

typically, user-defined object names are/can be surrounded with brackets. If your names don't have spaces or special characters that could be misinterpreted (/ means division, + means add, - means subtract, etc), it is obvious that what you typed is the whole name ... but names are allowed to contain characters that could cause issues, so to absolutely identify where a name begins and ends, using brackets makes it clear.


This post has been edited by strive4peace: Aug 7 2019, 08:18 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
strive4peace
post Aug 7 2019, 09:49 PM
Post#3


strive4peace
Posts: 20,464
Joined: 10-January 04



ps, Everett,

since you mentioned calculations, to specify data type, an expression could be wrapped in a conversion function such as to currency - 14 or 15 places before decimal point and 4 after, to integer <=32K, to long integer (bigger whole number), to double precision which is float, or scientific notation, and not accurate for keys and exact comparison, but has decimal places and good to represent extremely large and small numbers when all you need is a relative value, etc, so there is no doubt about data type for the result of a calculation. Other functions like round, INT and FIX, can help you get the sig figs (significant figures) you want.



This post has been edited by strive4peace: Aug 7 2019, 09:59 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
GroverParkGeorge
post Aug 8 2019, 08:54 AM
Post#4


UA Admin
Posts: 36,171
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#5



Posts: 210
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:03 PM
Post#6


UA Admin
Posts: 36,171
Joined: 20-June 02
From: Newcastle, WA


Underscores are generally not a problem. In fact, some naming conventions prefer them.

I don't know that anything has actually changed over the years. Access is very tolerant of such things, some might say too tolerant. It's also the case that many of Microsoft's own sample dbs and templates do not use the most optimal naming conventions, so a LOT of people have been given poor examples.

It's not that you CAN'T use these special characters. They just come at a higher cost.

--------------------
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
 
GroverParkGeorge
post Aug 8 2019, 12:06 PM
Post#7


UA Admin
Posts: 36,171
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
 
strive4peace
post Aug 15 2019, 08:50 AM
Post#8


strive4peace
Posts: 20,464
Joined: 10-January 04



thanks, George
> "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"
Everett, you're welcome. I normally make heavy use of underscore in these cases! b_wgt and c_wgt. If you're concerned that you might not remember it is per, you can name them like this:
b_per_wgt and c_per_wgt

--------------------
have an awesome day,
crystal
Go to the top of the page
 
Everettc4
post Aug 27 2019, 04:58 PM
Post#9



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


Thanks again all, I have everything moved and working. I'm in the processes of cleaning up the bad field names. I am going with big Indian little indian for filed name her on out. JobTicket ect. Also working on stored procedures to speed up some of the slower data pulls. working on a new post now looking for help.

Cheers
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 05:56 PM