Results 1 to 7 of 7
  1. #1
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159

    Auto status a record

    Is there a way to set up a query that analyzes a table and based on the input of data in this table, the query can provide a "status."

    For example. Lets say I have a table that has the following:



    Number Step 1
    Step 2
    Step 3
    1234 C NC NC
    4567 C C C

    And then another table that will be used to status the above "Numbers"

    Step 1
    Step 2
    Step 3
    Status
    C NC NC Workout finished
    C C C Phase 1 complete
    C NC C Phase 1 complete

    So once the query runs it will "status" each "number" so it would look as so:
    Number Status
    1234 Workout finished
    4567 Phase 1 complete

    Keep in mind I have a huge list of "numbers" and will have up to 13 "steps" to analyze to get the status. Anyway to create a query or code to run my table through an analysis test based on my "status table" to add a status to each "number?"

  2. #2
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    I tried writing a very long IIf, but it gets way too complex after layering a third status into the equation.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would first normalize the tables. You have the tables set up like a spreadsheet. I would have the table designed like this:
    Code:
    NumID    ANumber    TheStep   StepValue
    1    1234           1            C
    2    1234           2            NC
    3    1234           3            NC
    4    4567           1            C
    5    4567           2            C
    6    4567           3            C
    (NumID is an autonumber field I added.
    "Number" and "Value" are reserved words in Access)

    Keep in mind I have a huge list of "numbers" and will have up to 13 "steps" to analyze to get the status.
    Walk me through "how" you would analyze say, number 7412 that has 10 steps.
    Do you compare Step1 from the Number table to Step 1 of the Status table??
    How do you know when to stop comparing steps? Number 1234 has C, NC, NC. you have the STATUS as "Workout finished". Will the other 10 steps have NC?

    Also, does C & NC have meaning?

  4. #4
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Quote Originally Posted by ssanfu View Post
    I would first normalize the tables. You have the tables set up like a spreadsheet. I would have the table designed like this:
    Code:
    NumID    ANumber    TheStep   StepValue
    1    1234           1            C
    2    1234           2            NC
    3    1234           3            NC
    4    4567           1            C
    5    4567           2            C
    6    4567           3            C
    (NumID is an autonumber field I added.
    "Number" and "Value" are reserved words in Access)


    Walk me through "how" you would analyze say, number 7412 that has 10 steps.
    Do you compare Step1 from the Number table to Step 1 of the Status table??
    How do you know when to stop comparing steps? Number 1234 has C, NC, NC. you have the STATUS as "Workout finished". Will the other 10 steps have NC?

    Also, does C & NC have meaning?
    First off, thanks for the prompt reply.

    The "Number" will actually be the primary key itself. No two numbers will be alike.

    C= Complete, NC= Not Complete

    Essentially, my "status" table will have to have a lay out of every possible step definition possible. For example, in my first post the status "Phase 1 complete" has different step definitions that arrive to that status. So as you may imagine, with 10-12 steps total, there will be plenty of different combinations of step definitions to cover.

    But I'm trying to find out if it is even possible to use a table as a guide to status a record based on given criteria for each step.

  5. #5
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Whats funny is the layout you did with the table is how my data usually gets exported to Excel in. The number will be listed multiple times in rows for each step that is defined. However, I believe I can find my way around this issue by creating a query to break up each "step" into its own column and then combining all queries for each step to columnize each step compared to having them listed in rows.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    However, I believe I can find my way around this issue by creating a query to break up each "step" into its own column and then combining all queries for each step to columnize each step compared to having them listed in rows.
    You would be taking a normalized table and de-normalizing it...??

    Code:
    NumID    ANumber    TheStep    StepValue
    1        1234         S1        C
    2        1234         S2        NC
    3        1234         S3        NC
    4        4567         S1        C
    5        4567         S2        C
    6        4567         S3        C
    7        1234         S4        NC
    Given the above normalized table, you could use a crosstab query
    Code:
    TRANSFORM First(StepTable.StepValue) AS FirstOfStepValue
    SELECT StepTable.ANumber
    FROM StepTable
    GROUP BY StepTable.ANumber
    PIVOT StepTable.TheStep;
    to produce a result like:
    Code:
    ANumber    S1    S2    S3    S4
    1234       C     NC    NC    NC
    4567       C     C     C
    (See attached mdb)

    Essentially, my "status" table will have to have a lay out of every possible step definition possible. For example, in my first post the status "Phase 1 complete" has different step definitions that arrive to that status. So as you may imagine, with 10-12 steps total, there will be plenty of different combinations of step definitions to cover.

    But I'm trying to find out if it is even possible to use a table as a guide to status a record based on given criteria for each step.
    Yes, with enough code, it is possible (and not that much code is required).

    BUT..., without examples, I'm kinda stuck. Need to see your "Number" table and the "Status" result table. Care to attach the project? (Preferably in A2000-2003 format)

  7. #7
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Don't want to leave you hanging. I appreciate your help. I am currently on track to getting this down. From what I have seen in your post, I think you understand exactly what I am needing.

    I am using make table queries and append queries with a mix of IIfs and piecing my "status' for each 'number' that way. If I deem this way to be ineffective or bogging down the database due to run time, I will be back here.

    Regardless, I will look into sharing my way if it works out. If not, I will be checking yours out.

    Again, thanks for your help and time.

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

Similar Threads

  1. Replies: 8
    Last Post: 07-06-2013, 05:13 PM
  2. Auto Delete record after 3 years?
    By maxx102 in forum Access
    Replies: 3
    Last Post: 06-01-2012, 08:55 PM
  3. Auto Record create across tables
    By b123 in forum Database Design
    Replies: 15
    Last Post: 10-02-2010, 01:36 AM
  4. Auto Generate Record
    By mjhopler in forum Forms
    Replies: 2
    Last Post: 02-10-2010, 03:40 PM
  5. Turn Off Auto Save Record
    By Syntinal in forum Forms
    Replies: 2
    Last Post: 03-01-2009, 11:30 AM

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