Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with Continious Form

Discussion in 'Information Technology' started by Adam@nospam.com, Jul 28, 2006.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hello all

    I'm trying to make a well designed database, which is simplistic as
    I've learnt that this is very important when the database gets used
    heavily.


    I have a form which has its Record Source set to a query.

    The query contains two tables.

    AuditQuestions and AuditResults.

    AuditQuestions has [TeamName] and [QuestionID] as primary keys
    AuditResults has [AdvisorName] (dups ok) and [QuestionID] (dups ok) as
    primary keys.

    The relationship is a One-Many relationship (1 auditquestions to many
    auditresults).

    I have all the fields from AuditQuestions and [Advisor name],[MaxScore]
    from AuditResults.

    Now I have setup a continious form, which AdvisorName in the Form
    Header, and the Questions and MaxScore in the detail section.

    Now when I type in AdvisorName, then select the 2nd row on the
    Questions in the detail section of the form, the AdvisorName goes
    blank.

    I presume that this is because its seeing it as another record.

    Its there a way where I can keep the AdvisorName in the FormHeader,
    when I go through the questions in the detail when using Continious
    Form format?

    I hope that someone can help!

    Many Thanks
    Adam
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I have a question about the tables first.

    Are you saying that for team and questionID
    there can be multiple result records and that the Advisor Name can
    be different for each of the results?
    That is what you table struction is saying.

    It would seem that Advisor name should be with the team and not for
    each answer for each question.

    Also, On what event are you loading the advisor name?

    A clearer explanation of the table structures would help significantly.

    Ron
     
  4. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hi Ron,

    I want the one advisor name to be recorded against each of the
    questions...


    Ron2006 wrote:

    > I have a question about the tables first.
    >
    > Are you saying that for team and questionID
    > there can be multiple result records and that the Advisor Name can
    > be different for each of the results?
    > That is what you table struction is saying.
    >
    > It would seem that Advisor name should be with the team and not for
    > each answer for each question.
    >
    > Also, On what event are you loading the advisor name?
    >
    > A clearer explanation of the table structures would help significantly.
    >
    > Ron
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I am taking your response to mean that there really is only one advisor
    and that advisor is really related to the team not to each question.
    That is the key, since that is true, you can always view and display
    the advisor's name but you do not need to save it on each individual
    question result record. But if the advisor is related to the question
    see my notes next to the advisor name

    Given that your tables look more like this:

    Ideally, (If I understand your tables and processes)

    tblTeam
    TeamID
    TeamName
    Advisor -- perhaps the best place to put this
    other team info

    tblAuditQuestions
    QuestionID (PK)
    TeamID (FK)
    Question
    Advisor -- the next best place to put this
    other question info

    tblResults
    ResultsID (PK)
    QuestionID (FK)
    Score
    other result information



    And load the Advisor name to the header on the OnCurrent event and not
    the Onopen or onload event. This state ment is true even if you keep a
    advisor name in the tblAuditQuestion table.
     
  6. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Thank you, I will look at this at home and post back!


    Ron2006 wrote:

    > I am taking your response to mean that there really is only one advisor
    > and that advisor is really related to the team not to each question.
    > That is the key, since that is true, you can always view and display
    > the advisor's name but you do not need to save it on each individual
    > question result record. But if the advisor is related to the question
    > see my notes next to the advisor name
    >
    > Given that your tables look more like this:
    >
    > Ideally, (If I understand your tables and processes)
    >
    > tblTeam
    > TeamID
    > TeamName
    > Advisor -- perhaps the best place to put this
    > other team info
    >
    > tblAuditQuestions
    > QuestionID (PK)
    > TeamID (FK)
    > Question
    > Advisor -- the next best place to put this
    > other question info
    >
    > tblResults
    > ResultsID (PK)
    > QuestionID (FK)
    > Score
    > other result information
    >
    >
    >
    > And load the Advisor name to the header on the OnCurrent event and not
    > the Onopen or onload event. This state ment is true even if you keep a
    > advisor name in the tblAuditQuestion table.
     
  7. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Can I send you a copy of this database Ron? I'm having difficulty with
    Normalization because I cant see how to apply it.


    Adam@nospam.com wrote:

    > Thank you, I will look at this at home and post back!
    >
    >
    > Ron2006 wrote:
    >
    > > I am taking your response to mean that there really is only one advisor
    > > and that advisor is really related to the team not to each question.
    > > That is the key, since that is true, you can always view and display
    > > the advisor's name but you do not need to save it on each individual
    > > question result record. But if the advisor is related to the question
    > > see my notes next to the advisor name
    > >
    > > Given that your tables look more like this:
    > >
    > > Ideally, (If I understand your tables and processes)
    > >
    > > tblTeam
    > > TeamID
    > > TeamName
    > > Advisor -- perhaps the best place to put this
    > > other team info
    > >
    > > tblAuditQuestions
    > > QuestionID (PK)
    > > TeamID (FK)
    > > Question
    > > Advisor -- the next best place to put this
    > > other question info
    > >
    > > tblResults
    > > ResultsID (PK)
    > > QuestionID (FK)
    > > Score
    > > other result information
    > >
    > >
    > >
    > > And load the Advisor name to the header on the OnCurrent event and not
    > > the Onopen or onload event. This state ment is true even if you keep a
    > > advisor name in the tblAuditQuestion table.
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    One of the questions that needs to be answered is:

    Is the advisor "assigned", for lack of a better word, to each question
    or is the advisor assigned to a team.

    Here is some advise given by Allen Brown, a frequent MVP poster to this
    forum:
    ========================================
    Microsoft's article:
    ACC2000: Database Normalization Basics
    at:
    http://support.microsoft.com/?id=209534

    Don't worry greatly about trying to figure out the difference between
    the
    1st, 2nd, and 3rd normal forms. A very practical way to learn this is
    to
    open the Northwind sample database, open the Relationships window
    (Tools
    menu), and what tables and relations are needed for customers,
    products, and
    orders.
    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html


    =======================================

    Ron
     
  9. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    There are a team of advisors.

    Each advisor is then audited on a number of questions, so when I audit
    an advisor there will be a number of audit questions to be recorded for
    that one advisor.




    Ron2006 wrote:

    > One of the questions that needs to be answered is:
    >
    > Is the advisor "assigned", for lack of a better word, to each question
    > or is the advisor assigned to a team.
    >
    > Here is some advise given by Allen Brown, a frequent MVP poster to this
    > forum:
    > ========================================
    > Microsoft's article:
    > ACC2000: Database Normalization Basics
    > at:
    > http://support.microsoft.com/?id=209534
    >
    > Don't worry greatly about trying to figure out the difference between
    > the
    > 1st, 2nd, and 3rd normal forms. A very practical way to learn this is
    > to
    > open the Northwind sample database, open the Relationships window
    > (Tools
    > menu), and what tables and relations are needed for customers,
    > products, and
    > orders.
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    >
    >
    > =======================================
    >
    > Ron
     
  10. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    I want to then look at a consolidated team score, and an individual
    advisors score. This will be over a range of dates etc, as an advisor
    could be audited more than once.




    Adam@nospam.com wrote:

    > There are a team of advisors.
    >
    > Each advisor is then audited on a number of questions, so when I audit
    > an advisor there will be a number of audit questions to be recorded for
    > that one advisor.
    >
    >
    >
    >
    > Ron2006 wrote:
    >
    > > One of the questions that needs to be answered is:
    > >
    > > Is the advisor "assigned", for lack of a better word, to each question
    > > or is the advisor assigned to a team.
    > >
    > > Here is some advise given by Allen Brown, a frequent MVP poster to this
    > > forum:
    > > ========================================
    > > Microsoft's article:
    > > ACC2000: Database Normalization Basics
    > > at:
    > > http://support.microsoft.com/?id=209534
    > >
    > > Don't worry greatly about trying to figure out the difference between
    > > the
    > > 1st, 2nd, and 3rd normal forms. A very practical way to learn this is
    > > to
    > > open the Northwind sample database, open the Relationships window
    > > (Tools
    > > menu), and what tables and relations are needed for customers,
    > > products, and
    > > orders.
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > >
    > >
    > > =======================================
    > >
    > > Ron
     
  11. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Did you send it?
     
  12. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    No, I'll send it to you shortly !

    Ron2006 wrote:

    > Did you send it?
     
  13. Ron2006

    Ron2006
    Expand Collapse
    Guest

    No Problem. Just wanted to make sure that I had not deleted it or
    something by mistake.

    Ron
     
  14. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hi Ron,

    I've emailed you the database now.

    Many thanks for your help!

    Adam

    Ron2006 wrote:
    > No Problem. Just wanted to make sure that I had not deleted it or
    > something by mistake.
    >
    > Ron
     
  15. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Returned with Notes and hopefully some ideas.

    Ron
     
  16. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Thanks Ron,

    Looks like a lot of changes! I'm goner need to sit and dicest all of
    this.

    How would I have an advisor at the top, with the questions below, all
    recording against that one advisor...


    Ron2006 wrote:
    > Returned with Notes and hopefully some ideas.
    >
    > Ron
     
  17. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Like how I've setup the form "New Audit frm".


    Adam@nospam.com wrote:
    > Thanks Ron,
    >
    > Looks like a lot of changes! I'm goner need to sit and dicest all of
    > this.
    >
    > How would I have an advisor at the top, with the questions below, all
    > recording against that one advisor...
    >
    >
    > Ron2006 wrote:
    > > Returned with Notes and hopefully some ideas.
    > >
    > > Ron
     
  18. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I'll switch it and send a copy.....
     
  19. Ron2006

    Ron2006
    Expand Collapse
    Guest

    That is why everything is going blank. Advisor is at the bottom of the
    hierarchy, (it is part of the information on the most detail piece of
    information) so when you go to add those records, it loses track of
    who/what records to hang under.

    In your form you have the information from the two tables intermingled
    so that in effect when you go to add a new "sub-form" record you are
    really adding the masterform record. Using that form to display does
    not cause a problem, it is when you try to use it to enter new
    information that it runs into a problem.

    What is the purpose of the form?

    To add result records?

    Are those the only questions that are going to be asked?

    Will different "teams" have different set of questions?

    You would do best figuring out how to ADD information given your
    structure and then figure out how you want to view information for
    reports and/or analysis. Mixing the two causes the type of problem that
    you are showing.

    How is the input organized? Meaning is it one sheet/booklet for a
    particular date and advisor and you have a response and score for each
    of the questions?


    And a related question?
    Are the notes that are on the top really for every question or rather
    are they really relative to ALL the questions that are being asked for
    this advisor for this date?
    In fact, is that sort of how it is organized? All the information in
    the header is relative to a single test for a particular date for a
    particular "team" for a particular advisor?
     
  20. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hi Ron,

    The purpose of the form is to audit the advisor, as per the questions.

    It is like a questionnaire, you enter your name at the top and answer
    each of the questions. So your results will all be recorded against the
    one Name.

    The form is to add records.

    There will be different questions for each "Team".

    There is a response and score for each question.

    And the notes field is relative to ALL of the questions, not each of
    the questions.

    Think I've answered each item?

    Many Thanks for your assistance!

    Adam


    Ron2006 wrote:
    > That is why everything is going blank. Advisor is at the bottom of the
    > hierarchy, (it is part of the information on the most detail piece of
    > information) so when you go to add those records, it loses track of
    > who/what records to hang under.
    >
    > In your form you have the information from the two tables intermingled
    > so that in effect when you go to add a new "sub-form" record you are
    > really adding the masterform record. Using that form to display does
    > not cause a problem, it is when you try to use it to enter new
    > information that it runs into a problem.
    >
    > What is the purpose of the form?
    >
    > To add result records?
    >
    > Are those the only questions that are going to be asked?
    >
    > Will different "teams" have different set of questions?
    >
    > You would do best figuring out how to ADD information given your
    > structure and then figure out how you want to view information for
    > reports and/or analysis. Mixing the two causes the type of problem that
    > you are showing.
    >
    > How is the input organized? Meaning is it one sheet/booklet for a
    > particular date and advisor and you have a response and score for each
    > of the questions?
    >
    >
    > And a related question?
    > Are the notes that are on the top really for every question or rather
    > are they really relative to ALL the questions that are being asked for
    > this advisor for this date?
    > In fact, is that sort of how it is organized? All the information in
    > the header is relative to a single test for a particular date for a
    > particular "team" for a particular advisor?
     
  21. Ron2006

    Ron2006
    Expand Collapse
    Guest

    What about the Doc Reference - Date - Time, The Customer Name? Are all
    of these relative to the test or are they relative to the question?

    Ron
     

Share This Page