Problem with Continious Form

[Adam]

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

 

[Ron2006]

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

 

[Adam]

Hi Ron,

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

 

[Adam]

Hi Ron,

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

 

[Ron2006]

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.

 

[Ron2006]

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.

 

[Adam]

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

 

[Adam]

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

 

[Ron2006]

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.



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

Ron

 

[Adam]

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.

 

[Adam]

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.

 

[Ron2006]

Did you send it?

 

[Adam]

No, I'll send it to you shortly !

 

[Ron2006]

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

Ron

 

[Adam]

Hi Ron,

I've emailed you the database now.

Many thanks for your help!

Adam

 

[Ron2006]

Returned with Notes and hopefully some ideas.

Ron

 

[Adam]

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...

 

[Adam]

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

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]

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

 

[Ron2006]

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?