How To: Displaying Percents in SQL Table

Posted on 2007-07-22
Last Modified: 2008-01-09
Hi All-

I am very new ot using SQL tables.  I have created a "form" using excel that people fill out.  Some of the cells calculate a percent.  The cells are set to "Percent" for the data type with 2 decimal places.  However, I can not figure out how to get SQL to recognize the percent.  I am using Access to manage/review the data that comes in from the forms people compelte.  Essentially, I nned to get SQL to see the value from an excel as a Percent and then display that value in Access.  

1) what data tpye should I set the SQL table column to be?
2) What should I have the excel data type be?
3) Do I need to do anything in Access?
4) ect...

Thank you for any advice!!
Question by:bclivell
    LVL 42

    Expert Comment

    There is no such thing as a percent data type. Percent is a display format for a numeric datatype.  The number .15 will display as 15%.  The number .1599 will display as 15.99% if the display format is percent with 2 decimal places.

    So, your SQL datatype can be any numeric datatype.   The main consideration is that the (SQL) storage datatype must have 2 more decimal places to the left of the decimal point than the (Excel) percent display format.

    If you want the Excel format percent with 2 decimals then the SQL format should be numeric with 4 decimals.

    Author Comment

    dqmq- Thank you for gettign back.  I am looking in the SQL data type options/properties for numeric data types and i can not find the place to enter the number of desired decimals place.  Can you please tell me where I can adjust this setting?  thank you!
    LVL 42

    Accepted Solution

    In column properties the "Scale" property is the number of digits to the right of the decimal place.  BTW, in-reading my previous answer, I see dislexia crept in--I should have said " the storage datatype must have 2 more decimal places to the right..."

    Author Comment

    Thank you!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now