Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Sacramento, CA
    Posts
    2

    Question Unanswered: Building a checklist & linking to a unique record

    I'm using MSAccess 97

    I've been playing around for two days now trying to figure out a way to build a checklist that I wish to link to each unique record in my main table. Iv'e created the 'look' of the checklist and have attached it to the post. Here's the gist of it.

    Lets say I have a list of 10 departments. For each department I want to have two check boxes (yes/no) and two fields for logging dates. I want to link that to each unique record in my main table.

    When the user enters the main form, I want them to be able to click on an icon which would bring up a checklist so that they can do some moderate "project management" tracking.

    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Building a checklist & linking to a unique record

    Interesting.. I don't know how well versed are you with relationships and vba but this how can acieve it.

    Make the check list as a table and connect it to the main table through many to many relationship. ie you'll have to have another table that will link both main and check list table. The idea is to somehow get the record id from the main table and link the check list to that id so that for each unique id there is a unquie set of check list items.

    Then with proper coding and form design you'll be able to do what you are trying to do.


    Originally posted by chipsnsoda
    I'm using MSAccess 97

    I've been playing around for two days now trying to figure out a way to build a checklist that I wish to link to each unique record in my main table. Iv'e created the 'look' of the checklist and have attached it to the post. Here's the gist of it.

    Lets say I have a list of 10 departments. For each department I want to have two check boxes (yes/no) and two fields for logging dates. I want to link that to each unique record in my main table.

    When the user enters the main form, I want them to be able to click on an icon which would bring up a checklist so that they can do some moderate "project management" tracking.

    Thanks,

  3. #3
    Join Date
    Jan 2004
    Location
    Sacramento, CA
    Posts
    2
    I did try one table, but here's the problem I ran across. If I build one table with (for example) three departments; Department A, Department B, and Department C along with the datafields I want to track, how do I get them to repeat for each record I want linked to that table from the Pivot table?

    To conquer that, I created two tables; one as a reference table (We'll call it Table 1) listing Department A, B and C, and the second table (table 2) with the datafields I wanted to track and an ID to link to the Pivot table. I then joined Table 2 to the Pivot table in Relationships (one to many). Then, I created two query's. In query 1 I joined Table 1 and Table 2. In Query 2 I joined that query to the pivot table. but, when I tested it I ran across the error "Can't enter value into blank field on one side of outer join".

    Am I on the right track? Any suggestions on the error?

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Well first of all you have to have a solid foundation to build your app on. Did you consider my previous idea how to set up tables? All the queries and pivot tables and reports come later. First get the right tables i nthe right relationship then see whats the problem.

    Originally posted by chipsnsoda
    I did try one table, but here's the problem I ran across. If I build one table with (for example) three departments; Department A, Department B, and Department C along with the datafields I want to track, how do I get them to repeat for each record I want linked to that table from the Pivot table?

    To conquer that, I created two tables; one as a reference table (We'll call it Table 1) listing Department A, B and C, and the second table (table 2) with the datafields I wanted to track and an ID to link to the Pivot table. I then joined Table 2 to the Pivot table in Relationships (one to many). Then, I created two query's. In query 1 I joined Table 1 and Table 2. In Query 2 I joined that query to the pivot table. but, when I tested it I ran across the error "Can't enter value into blank field on one side of outer join".

    Am I on the right track? Any suggestions on the error?

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Building a checklist & linking to a unique record

    Originally posted by chipsnsoda
    I'm using MSAccess 97

    I've been playing around for two days now trying to figure out a way to build a checklist that I wish to link to each unique record in my main table. Iv'e created the 'look' of the checklist and have attached it to the post. Here's the gist of it.

    Lets say I have a list of 10 departments. For each department I want to have two check boxes (yes/no) and two fields for logging dates. I want to link that to each unique record in my main table.

    When the user enters the main form, I want them to be able to click on an icon which would bring up a checklist so that they can do some moderate "project management" tracking.

    Thanks,
    This looks to be a simple DB design issue ...

    Create a Department Table.
    Create a Cerification table.
    Create a Departmental Certification Table.

    Simple.

Posting Permissions

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