Using correct datatypes in SQL and creating child tables

Question

Super noob here at my wits end with Access 2010. I have some questions:

How do I create a child table in Access? I figured it'd be just a matter of defining the PK in the child table then referencing that PK as a FK to the parent table. Here's my sql code just in case I'm not making a bit of sense:

This is the end of my parent table which is Employee-

    CONSTRAINT  PKEmployee  PRIMARY  KEY  (EmpNo) ,
    CONSTRAINT  FKPosNo FOREIGN KEY  (PosNo)  REFERENCES  Position,  
    CONSTRAINT FKDeptNo FOREIGN KEY (DeptNo) REFERENCES Department )

Here's what I have for my child table, which is Salary. It's an Employee type-

    CREATE TABLE Salary
    (     EmpNo                 CHAR (6) ,
          OfficeNo              CHAR (4) ,
          SalaryAmount            DOUBLE ,
    CONSTRAINT PKEmpNo PRIMARY KEY (EmpNo),
    CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee )

When I run this code is gives me the "There is already a relationship named 'FKEmpNo' in the database." message. I'm not sure if this is on my end or Access'.

I'm also getting a syntax error when I try to declare 'SalaryAmount' as a DECIMAL. My code for it is:

  (EmpNo                 CHAR (6) ,
  OfficeNo              CHAR (4) ,
  SalaryAmount      DECIMAL (7, 2) ,
  CONSTRAINT PKEmpNo PRIMARY KEY (EmpNo),
  CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee )

The error highlights the first parenthesis. Can I even use DECIMAL type in access 2010? If not, what is the best way to represent a yearly income in Access 2010?

Please provide examples and explain to me like I'm a five year old. I'm trying wrap my mind around this database stuff without losing it.

You must have a constraint named FKEmpNo in some other table. — Barmar
– Barmar, Commented Nov 8, 2013 at 17:56
Just name the constraint something different. I usually use a combination of the constraint type (FK, UC...), the table name and the field name(s) — gloomy.penguin
– gloomy.penguin, Commented Nov 8, 2013 at 18:00
Use names like FKSalarayEmpNo to distinguish the foreign key constraints from different tables. — Barmar
– Barmar, Commented Nov 8, 2013 at 18:01
Most people find that the UI is easier to use. Is there any reason that you are trying to do this via DDL/SQL instead of the designer? — tgolisch
– tgolisch, Commented Nov 8, 2013 at 18:14
@BenignBaboon A CREATE TABLE with DECIMAL must be executed from ADO. It will not work when you attempt to execute the statement from DAO. If you're doing this from the Access query designer, be aware the query designer uses DAO "under the hood". All that aside, seems to me that CURRENCY would be the natural choice for a SalaryAmount field --- it's money, right? — HansUp
– HansUp, Commented Nov 8, 2013 at 18:26

HansUp · Accepted Answer · 2013-11-08 19:33:18Z

The code sample below builds and executes this statement:

CREATE TABLE Salary
    (
        uid COUNTER PRIMARY KEY,
        EmpNo CHAR (6),
        OfficeNo CHAR (4),
        SalaryAmount DECIMAL (7, 2),
        CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee(EmpNo)
    );

It successfully created the table with SalaryAmount DECIMAL (7, 2) because the statement was executed from CurrentProject.Connection.Execute, which is an ADO method. You can only use DECIMAL in a CREATE TABLE when it's executed from ADO. It will not work when executed with DAO. The query designer uses DAO to execute queries.

If Access complains about a name clash with an existing FKEmpNo constraint, use a different name for the foreign key constraint --- one which doesn't match any other existing constraint ...

CONSTRAINT BobIsYourUncle FOREIGN KEY ...

Note I changed the primary key for Salary. I guessed you didn't really want EmpNo as both primary and foreign key, so I used an autonumber field, uid, as primary key. You'll have to fix that if I guessed wrong.

Dim strDdl As String
strDdl = "CREATE TABLE Salary" & vbCrLf & _
    "(" & vbCrLf & _
    "uid COUNTER PRIMARY KEY," & vbCrLf & _
    "EmpNo CHAR (6)," & vbCrLf & _
    "OfficeNo CHAR (4)," & vbCrLf & _
    "SalaryAmount DECIMAL (7, 2)," & vbCrLf & _
    "CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee(EmpNo)" & vbCrLf & _
    ");"
Debug.Print strDdl
CurrentProject.Connection.Execute strDdl

And finally, this answer was to show you how you can use DECIMAL in a CREATE TABLE if you really want it. However, it seems to me that CURRENCY would be the natural choice for a SalaryAmount field.

Collectives™ on Stack Overflow

Using correct datatypes in SQL and creating child tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related