Results 1 to 5 of 5

Thread: SQL Query Help

  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: SQL Query Help

    Hello,

    I was wondering if someone could help me, I'm trying to create a SQL query that creates a table I've got the basics nailed I'm just struggling on this data type.

    It's got to be a number but how can I restrict it so it's only a number between 1-5 I tried:

    Code:
    CREATE TABLE Review
    (
    reviewdate DateTime,
    rating NUMBER(5),
    comment CHAR(50)
    )
    But it just chucks up a error.

    Any suggestions on this is greatly appreciated as I'm tearing my hair out trying to find a suggestion to this.

    Thanks in advance

    (Using Access 2007 by the way)

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    Error is on the declaration of the data type number

    CREATE TABLE Review
    (
    reviewdate DateTime,
    rating NUMBER,
    comment CHAR(50)
    )

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    and one can consider, rather than an abstract Create Table - - - one could instead use a MakeTable query and use the query functionality to be sure that only numbers between 1-5 are allowed.
    www CahabaData com

  4. #4
    Join Date
    Nov 2010
    Posts
    8
    Quote Originally Posted by eremija View Post
    Error is on the declaration of the data type number

    CREATE TABLE Review
    (
    reviewdate DateTime,
    rating NUMBER,
    comment CHAR(50)
    )
    Yes I know, But I was a wondering how would I go about making it so only a number between 1-5 would be entered

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are two methods to achieve this.
    First is a check constraint.
    Advantage: easy to apply, requires no further tables.
    Disadvantage: if ever you need to change the range you need to execute DDL statements to alter the table. Also, you can't execute the DDL using the Access interface.
    Intermediate Microsoft Jet SQL for Access 2000
    Second is a relationship to a table containing the permissible values using a foreign key.
    Advantages: you can show the permissible values in the GUI by reading from the related table. You can also store additional information about the rating (since the values are drawn from a table it is now an entity and can have further attributes). Ratings can be changed using DML
    Disadvantage: Requires more objects to be created.

    If you simply want to only allow numbers between 1 and 5 and you do not need to store any other information at all about the ratings, and things will never change then use the constraint. Otherwise use the related table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •