Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2020
    Posts
    1

    Query help


    trying to make a query in ms access and i keep getting error messages. Not sure what i am doing wrong.

    CREATE TABLE Customer
    (
    Cust_Name VARCHAR(15) NOT NULL,
    Cust_Address NUMERIC(15) NOT NULL,
    Cust_Phone NUMERIC(10) NOT NULL,
    EmailAddress VARCHAR(20) NOT NULL,
    Cust_Number NUMERIC(7) NOT NULL,
    PRIMARY KEY (Cust_Number)
    );

    This is the Query.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Cust_Address and Cust_Phone and Cust_Number could be text type because these are not quantitative data.

    Sometimes an address could be like 101A.

    Do you want to store phone number with hyphen? Is area code in another field?

    Don't include length value for Numeric type. Numeric will default to Double.

    Why do you need this? Why not just use table designer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @Jordan_spencer2001
    You can also use CHAR instead of VARCHAR but both work.
    If you want to specify number types e.g. INTEGER, BIT etc, list them as such in your data definition query
    For example:
    Code:
      CurrentDb.Execute "CREATE TABLE ImportedCourses " & _ 
                "(CourseID CHAR(10), CourseDetails MEMO, Weighting INTEGER," & _
                " ShowOnReport BIT, AssTarget1 CHAR(50), AssTarget2 CHAR(50), AssTarget3 CHAR(50)," & _
                " AssTarget4 CHAR(50), AssTarget5 CHAR(50), Year CHAR(5), SubjectID CHAR(5));"
                
            CurrentDb.Execute "CREATE INDEX CourseID ON ImportedCourses(CourseID) WITH PRIMARY;"
    @June7
    As you probably already know, using a data definition query or DAO to create tables gives more control over the field sizes and also allows you to create indexes / PK fields
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums