Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Best Field Types For Calculations

Posted by: Everettc4 Aug 7 2019, 06:57 PM

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!


Posted by: strive4peace Aug 7 2019, 08:02 PM

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.

Posted by: strive4peace Aug 7 2019, 09:49 PM

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.

Posted by: GroverParkGeorge Aug 8 2019, 08:54 AM

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.

Posted by: Everettc4 Aug 8 2019, 11:45 AM

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.


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

Posted by: GroverParkGeorge Aug 8 2019, 12:03 PM

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.

Posted by: GroverParkGeorge Aug 8 2019, 12:06 PM

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.

Posted by: strive4peace Aug 15 2019, 08:50 AM

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

Posted by: Everettc4 Aug 27 2019, 04:58 PM

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.