Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    4

    Unanswered: Yes/No fields in a table

    I've created a table of classes with yes/no fields for each job title we have. I then check the box for each position that needs to take that course.

    I've no idea how to query each class for the positions that need it. I think I need a criteria? An IIF seems wrong, but just not sure. Am I even doing this right?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What you need are two separate tables: one of classes and one of jobs. Each one should have a PK. Now you need a separate combined table. This table will contain the pk from jobs and one course pk from the classes. Each required job/class combo must be a separate record for normalization reasons. All you need are the two fields in each table.

    You don't need Booleans at all.

    Sam

  3. #3
    Join Date
    Oct 2012
    Posts
    4
    Let me see if I got this right. 3 tables
    Job positions = PK, Title
    Classes = PK, Class name
    Combination table = PK, TitlePK1, ClassPK1 (3 columns?)
    PK, TitlePK2, ClassPK1
    PK, Title PK3, ClassPK1
    PK, Title PK1, ClassPK2
    PK, Title PK2, ClassPK2
    PK, Title PK3, ClassPK2 .....etc
    Or is it: PK, TitlePK1+ClassPK1 (2 columns)

    Like this? Sorry, just having a hard time wrapping my head around these. Not sure I'm thinking in the right direction.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I would say that the combo table doesn't need a pk of its own.

    Each pk in the combo table is a separate field, such as ClassFk (that's foreign key. In its local table it's called primary key; in a 'foreign' table it's called foreign key.) and JobPosFk.

    By the way, you can also adjust your employee table, which already includes a field called 'job position,' to reflect the job position table's pk instead of a long-hand text description which is subject to data-entry anomalies such as added spaces or punctuation, numeralization (such as '1' instead of 'one'), spelling errors, etc.

  5. #5
    Join Date
    Oct 2012
    Posts
    4
    Thank you Sam.
    Whoop! Worked like a champ. I used my employee table for job titles, as it was already built and included. Did however build the combination table with a PK - before I read this last post of yours. I assume it's still okay, just unnecessary?

    Thanks again.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It'll work; just not necessary.

    Glad I was able to help.

    Sam

Posting Permissions

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