Writing to multiple tables

[Chip]

Hey Everyone, its me again..

My question today involves writing to multiple tables. My project is
an Access program that will be used to oversee practical
examinations. Each student (tblStudents) must pass each of 4
stations. There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail. In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.

Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window. One of the
requirements of my project is to look at the in multiple ways. Not
only do I want to record when a student passes or fails, but I want to
know how many times the student had to take that station. I also want
to measure class peformance as a whole. I want to measure how
individual evaluators do. There are limitless ways in which I can
forsee me chopping this data up.

So, the exam coordinator (me and my coworkers) come to a Practical
exam. We have a list of students as a subform in the Class. The
class itself has multiple things attached to it, the practical exam is
only a small part of it. When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.

Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())

These data points are then written to a table, tblPR. That table is
then used to populate other fields. In this example, each student has
a record. I record demographic data, paperwork items, etc.. One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR.
This is a subform, that is only displayed on the student record.

Up to this point, everything works fine. However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record. For example, if you take a station
today, 3/4/2009 and you fail. I want your current status for that
station to read Fail and the date of status to read 3/4/2009. Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date. That
would be on each studen trecord.

Then again, on the class record, I want the class record to be updated
as well. So my question is, is my structure appropriate for the
task? I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table. If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well. We also
adminstered Written exams in the same way.

Your thoughts?

chip

 

[aaron.kempf]

SQL Server Stored Procedures can write to multiple tables.

Access queries cannot.

-Aaron

 

[ken]

Chip:

As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data. The information is inherent in the tblPR table and can be
returned with a query like this:

SELECT Student, [Date], Station, Outcome
FROM tblPR AS PR1
WHERE [Date] =
(SELECT MAX([Date])
FROM tblPR AS PR2
WHERE PR2.Student = PR1.Student
AND PR2.Station = PR1.Station);

BTW don't use Date as a column name as it’s the name of a built in
function. Use something like ExamDate. If you do use Date then be
sure to wrap it in square brackets when referencing the column.

Ken Sheridan
Stafford, England

 

[CraigH]

I am sorry to everyone about this rant but I don't like it when it happens to
me:

Aaron - why in the world would you answer a post with a negative when there
is a positive answer. I don't believe you even read the whole post because
nowhere is there a mention of queires. It can even cause some people who may
have an answer skip over the post - because they think it is answered.

Chip - First I will say that copying fields into two or more tables is
probably a bad idea. I don't know enough about your table designs and
without getting a better overview I can't be sure. I will try one example I
think you have described to show an alternate way and then tell you how to
copy the data if you want to continue in that fashion.

You mentioned something like this: I want to see the lates pass/fail attemp
for a student. (You put that information into the student table from the
history of testing table) This could be just as easily be seen by showing it
through a query that returns the latest attempt (sorted by date and TOP 1)
and show it on the student Form. Or wherever.

Now if you want to write to multiple tables one option is to do it in code.
It will involve deciding when to do it - Possibly in an after update event.
You would open a recordset for the tables you want to work with, and
determine if you are Add(ing) or Edit(ing) the information, then put the
information in the tables.

I know this is a short answer and if you don't have much coding experience
this may be a little criptic - but again without seeing the total situation
it would be hard to give better instructions.

But hopefully you are pointed into one option that you can try to do.

Craig

 

[aaron.kempf]

Date isn't the name of a built in function.. GetDate is the name of a
built in function

Chip:

As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data.  The information is inherent in the tblPR table and can be
returned with a query like this:

SELECT Student, [Date], Station, Outcome
FROM tblPR  AS PR1
WHERE [Date] =
    (SELECT MAX([Date])
      FROM tblPR  AS PR2
      WHERE PR2.Student = PR1.Student
      AND PR2.Station = PR1.Station);

BTW don't use Date as a column name as it’s the name of a built in
function.  Use something like ExamDate.  If you do use Date then be
sure to wrap it in square brackets when referencing the column.

Ken Sheridan
Stafford, England

Hey Everyone, its me again..

My question today involves writing to multiple tables.  My project is
an Access program that will be used to oversee practical
examinations.  Each student (tblStudents) must pass each of 4
stations.  There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail.  In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.

Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window.  One of the
requirements of my project is to look at the in multiple ways.  Not
only do I want to record when a student passes or fails, but I want to
know how many times the student had to take that station.  I also want
to measure class peformance as  a whole.  I want to measure how
individual evaluators do.  There are limitless ways in which I can
forsee me chopping this data up.

So, the exam coordinator (me and my coworkers) come to a Practical
exam.  We have a list of students as a subform in the Class.  The
class itself has multiple things attached to it, the practical exam is
only a small part of it.  When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.

Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())

These data points are then written to a table, tblPR.  That table is
then used to populate other fields.  In this example, each student has
a record.  I record demographic data, paperwork items, etc..  One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR.
This is a subform, that is only displayed on the student record.

Up to this point, everything works fine.  However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record.  For example, if you take a station
today, 3/4/2009 and you fail.  I want your current status for that
station to read Fail and the date of status to read 3/4/2009.  Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date.  That
would be on each studen trecord.

Then again, on the class record, I want the class record to be updated
as well.  So my question is, is my structure appropriate for the
task?  I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table.  If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well.  We also
adminstered Written exams in the same way.

Your thoughts?

chip

 

[Michel Walsh]

You are technically wrong.

With Jet, not only you can update through a query based on many tables (as
long as the query is updateable), but you can update (append) in more than
one table at the time through that query. It is NOT recommended, though.

While you can update through a view, in MS SQL Server, by default, you can
only update from one of the table at a time. To update more than one table
of the view, at a time, you need to add Instead of Trigger (from MS SQL
Server 2000 or later), as explained in Delaney, as example.

As far as Stored Procedures are involved in MS SQL Server, to perform the
same functionality, that implies Transact SQL, role which is played by VBA
in Jet+Access. And VBA is much more generic than Transact SQL. You shoot
twice yourself in your foot with that comparison, I am afraid.



Vanderghast, Access MVP


SQL Server Stored Procedures can write to multiple tables.

Access queries cannot.

-Aaron

 

[L'Équipe Microsoft Windows Mail]

message de

Hey Everyone, its me again..

My question today involves writing to multiple tables. My project is
an Access program that will be used to oversee practical
examinations. Each student (tblStudents) must pass each of 4
stations. There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail. In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.

Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window. One of the
requirements of my project is to look at the in multiple ways. Not
only do I want to record when a student passes or fails, but I want to
know how many times the student had to take that station. I also want
to measure class peformance as a whole. I want to measure how
individual evaluators do. There are limitless ways in which I can
forsee me chopping this data up.

So, the exam coordinator (me and my coworkers) come to a Practical
exam. We have a list of students as a subform in the Class. The
class itself has multiple things attached to it, the practical exam is
only a small part of it. When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.

Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())

These data points are then written to a table, tblPR. That table is
then used to populate other fields. In this example, each student has
a record. I record demographic data, paperwork items, etc.. One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR.
This is a subform, that is only displayed on the student record.

Up to this point, everything works fine. However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record. For example, if you take a station
today, 3/4/2009 and you fail. I want your current status for that
station to read Fail and the date of status to read 3/4/2009. Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date. That
would be on each studen trecord.

Then again, on the class record, I want the class record to be updated
as well. So my question is, is my structure appropriate for the
task? I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table. If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well. We also
adminstered Written exams in the same way.

Your thoughts?

chip

 

[CraigH]

Well...

Date isn't the name of a built in function.. GetDate is the name of a
built in function

From :Access Developer Home > Visual Basic for Applications Language
Reference > Visual Basic Language Reference > Functions

From the Visual Basic Help

Date Function

Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by the
Calendar property setting. If the calendar is Hijri, Date$ returns a
10-character string of the form mm-dd-yyyy, where mm (01-12), dd (01-30) and
yyyy (1400-1523) are the Hijri month, day and year. The equivalent Gregorian
range is Jan 1, 1980 through Dec 31, 2099.

Example
This example uses the Date function to return the current system date.

Dim MyDate
MyDate = Date ' MyDate contains the current system date.


From the Access Help

Date Function
Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

If you use the Date function with a Gregorian calendar, the behavior of
Date$ is unchanged by the Calendar property setting. If the calendar is
Hijri, Date$ returns a 10-character string of the form mm-dd-yyyy, where mm
(01-12), dd (01-30) and yyyy (1400-1523) are the Hijri month, day and year.
The equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.
.......


Now I could be wrong that it is a "Built In" Function.

Craig H

Chip:

As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data. The information is inherent in the tblPR table and can be
returned with a query like this:

SELECT Student, [Date], Station, Outcome
FROM tblPR AS PR1
WHERE [Date] =
(SELECT MAX([Date])
FROM tblPR AS PR2
WHERE PR2.Student = PR1.Student
AND PR2.Station = PR1.Station);

BTW don't use Date as a column name as it’s the name of a built in
function. Use something like ExamDate. If you do use Date then be
sure to wrap it in square brackets when referencing the column.

Ken Sheridan
Stafford, England

Hey Everyone, its me again..

My question today involves writing to multiple tables. My project is
an Access program that will be used to oversee practical
examinations. Each student (tblStudents) must pass each of 4
stations. There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail. In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.

Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window. One of the
requirements of my project is to look at the in multiple ways. Not
only do I want to record when a student passes or fails, but I want to
know how many times the student had to take that station. I also want
to measure class peformance as a whole. I want to measure how
individual evaluators do. There are limitless ways in which I can
forsee me chopping this data up.

So, the exam coordinator (me and my coworkers) come to a Practical
exam. We have a list of students as a subform in the Class. The
class itself has multiple things attached to it, the practical exam is
only a small part of it. When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.

Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())

These data points are then written to a table, tblPR. That table is
then used to populate other fields. In this example, each student has
a record. I record demographic data, paperwork items, etc.. One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR.
This is a subform, that is only displayed on the student record.

Up to this point, everything works fine. However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record. For example, if you take a station
today, 3/4/2009 and you fail. I want your current status for that
station to read Fail and the date of status to read 3/4/2009. Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date. That
would be on each studen trecord.

Then again, on the class record, I want the class record to be updated
as well. So my question is, is my structure appropriate for the
task? I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table. If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well. We also
adminstered Written exams in the same way.

Your thoughts?

chip

 

[aaron.kempf]

they're talking about updating multiple tables.

you cannot do multiple things in any Access query-- only though moving
to stored procedures can you do distinct tasks-- like this--

Transact SQL = Transact SQL
Transact SQL + VBA = Much More Powerful than Jet!!!!!

-Aaron

 

[aaron.kempf]

and for the record, doing _ANYTHING_ with Access queries-- it might
work _ONCE_OR_TWICE_ but when you're stacking queries on top of
queries-- nothing is predictable.

Jet doesn't even have any sort of ability to troubleshoot broken
queries--
Jet just throws up msgbox "the parameter is incorrect" or "the
expression is too complex"

What a stupid ****ing database.
Jet has been obsolete for a decade-- in addition to anyone that uses
it.

The only answer is to fire anyone using jet-- and then spit on them.

**** Jet-- these days-- even Excel makes a better database than Jet
does
because Excel doesn't crap out when it hits 10,000 records!

maybe you should learn to spell 'Access Data Projects' pushing SQL
Server 2005 (which can fire .NET Stored Procedures) before you even
start trying to tell me I'm wrong

-Aaron



-Aaron

 

[Michel Walsh]

"YOU" were talking about the impossibility to update (append) in many
tables, at a time, using Jet, and I pointed you were simply wrong about that
statement. And you didn't fight my argument, so I assume you acknowledge you
were wrong.

By the way, using VBA, you can also update multiple tables, and much more
easily than with a stored proc.


Vanderghast, Access MVP



they're talking about updating multiple tables.

you cannot do multiple things in any Access query-- only though moving
to stored procedures can you do distinct tasks-- like this--

Transact SQL = Transact SQL
Transact SQL + VBA = Much More Powerful than Jet!!!!!

-Aaron

 

[ken]

You never cease to amaze us with your ignorance Aaron. It is in VBA
and is thus usable in Jet. Here's what Access's help system has to
say:

Date Function


Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by
the Calendar property setting. If the calendar is Hijri, Date$ returns
a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd
(01-30) and yyyy (1400-1523) are the Hijri month, day and year. The
equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Ken Sheridan
Stafford, England

Date isn't the name of a built in function.. GetDate is the name of a
built in function

Chip:

As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data. The information is inherent in the tblPR table and can be
returned with a query like this:

SELECT Student, [Date], Station, Outcome
FROM tblPR AS PR1
WHERE [Date] =
(SELECT MAX([Date])
FROM tblPR AS PR2
WHERE PR2.Student = PR1.Student
AND PR2.Station = PR1.Station);

BTW don't use Date as a column name as it’s the name of a built in
function. Use something like ExamDate. If you do use Date then be
sure to wrap it in square brackets when referencing the column.

Ken Sheridan
Stafford, England

 

[CraigH]

Hi,
I already pounced on him for that - benefit of being in close time zone.
I even gave him the two versions VBA actually what your sis, also shame on
you for not putting in the example
{
Example
This example uses the Date function to return the current system date.

Dim MyDate
MyDate = Date ' MyDate contains the current system date.

}

and part of the Access Help (it was way to long)

Enjoy,

Craig

You never cease to amaze us with your ignorance Aaron. It is in VBA
and is thus usable in Jet. Here's what Access's help system has to
say:

Date Function


Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by
the Calendar property setting. If the calendar is Hijri, Date$ returns
a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd
(01-30) and yyyy (1400-1523) are the Hijri month, day and year. The
equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Ken Sheridan
Stafford, England

Date isn't the name of a built in function.. GetDate is the name of a
built in function

Chip:

As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data. The information is inherent in the tblPR table and can be
returned with a query like this:

SELECT Student, [Date], Station, Outcome
FROM tblPR AS PR1
WHERE [Date] =
(SELECT MAX([Date])
FROM tblPR AS PR2
WHERE PR2.Student = PR1.Student
AND PR2.Station = PR1.Station);

BTW don't use Date as a column name as it’s the name of a built in
function. Use something like ExamDate. If you do use Date then be
sure to wrap it in square brackets when referencing the column.

Ken Sheridan
Stafford, England

On Mar 4, 3:22 pm, Chip <[email protected]> wrote:

Hey Everyone, its me again..

My question today involves writing to multiple tables. My project is
an Access program that will be used to oversee practical
examinations. Each student (tblStudents) must pass each of 4
stations. There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail. In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.

Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window. One of the
requirements of my project is to look at the in multiple ways. Not
only do I want to record when a student passes or fails, but I want to
know how many times the student had to take that station. I also want
to measure class peformance as a whole. I want to measure how
individual evaluators do. There are limitless ways in which I can
forsee me chopping this data up.

So, the exam coordinator (me and my coworkers) come to a Practical
exam. We have a list of students as a subform in the Class. The
class itself has multiple things attached to it, the practical exam is
only a small part of it. When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.

Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())

These data points are then written to a table, tblPR. That table is
then used to populate other fields. In this example, each student has
a record. I record demographic data, paperwork items, etc.. One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR.
This is a subform, that is only displayed on the student record.

Up to this point, everything works fine. However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record. For example, if you take a station
today, 3/4/2009 and you fail. I want your current status for that
station to read Fail and the date of status to read 3/4/2009. Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date. That
would be on each studen trecord.

Then again, on the class record, I want the class record to be updated
as well. So my question is, is my structure appropriate for the
task? I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table. If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well. We also
adminstered Written exams in the same way.

Your thoughts?

chip

 

[aaron.kempf]

uh... he might have been using ADP, you never know.
Why don't you go and learn how to property use getdate, the real mans
function for the date!

-Aaron

You never cease to amaze us with your ignorance Aaron.  It is in VBA
and is thus usable in Jet.  Here's what Access's help system has to
say:

Date Function

Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by
the Calendar property setting. If the calendar is Hijri, Date$ returns
a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd
(01-30) and yyyy (1400-1523) are the Hijri month, day and year. The
equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Ken Sheridan
Stafford, England

Date isn't the name of a built in function.. GetDate is the name of a
built in function

On Mar 4, 9:41 am, (e-mail address removed) wrote:

Chip:
As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data.  The information is inherent in the tblPR table and can be
returned with a query like this:
SELECT Student, [Date], Station, Outcome
FROM tblPR  AS PR1
WHERE [Date] =
    (SELECT MAX([Date])
      FROM tblPR  AS PR2
      WHERE PR2.Student = PR1.Student
      AND PR2.Station = PR1.Station);
BTW don't use Date as a column name as it’s the name of a built in
function.  Use something like ExamDate.  If you do use Date then be
sure to wrap it in square brackets when referencing the column.
Ken Sheridan
Stafford, England
Hey Everyone, its me again..
My question today involves writing to multiple tables.  My project is
an Access program that will be used to oversee practical
examinations.  Each student (tblStudents) must pass each of 4
stations.  There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail.  In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.
Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window.  One of the
requirements of my project is to look at the in multiple ways.  Not
only do I want to record when a student passes or fails, but I wantto
know how many times the student had to take that station.  I alsowant
to measure class peformance as  a whole.  I want to measure how
individual evaluators do.  There are limitless ways in which I can
forsee me chopping this data up.
So, the exam coordinator (me and my coworkers) come to a Practical
exam.  We have a list of students as a subform in the Class.  The
class itself has multiple things attached to it, the practical examis
only a small part of it.  When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.
Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())
These data points are then written to a table, tblPR.  That tableis
then used to populate other fields.  In this example, each student has
a record.  I record demographic data, paperwork items, etc..  One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR..
This is a subform, that is only displayed on the student record.
Up to this point, everything works fine.  However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record.  For example, if you take a station
today, 3/4/2009 and you fail.  I want your current status for that
station to read Fail and the date of status to read 3/4/2009.  Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date.  That
would be on each studen trecord.
Then again, on the class record, I want the class record to be updated
as well.  So my question is, is my structure appropriate for the
task?  I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table.  If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well.  We also
adminstered Written exams in the same way.
Your thoughts?
chip

 

[aaron.kempf]

dude.. you're a ****ing idiot if you think that you can compare Jet
+Vba to Tsql+Vba.

With Access Data Projects, I can do anything you can do in Vba.. and
then about 10 billion more things in TSQL.
With Access queries, you can't run multiple statements in one
package-- you have to write a whole ton of (unmaintainable) vba.

Let me compare:

TSQL:
1) fire a sproc.. that.. uh.. does multiple things

VBA:
1) write a bunch of queries... test those queries, oh snap-- one of
them randomly gives me 'the parameter is incorrect'
2) write a bunch of VBA.. oh crap, my fingers are getting sore because
it takes me about 20 lines of VBA to run two statements
3) push out a new version of the frontend (every time you write a new
query) because Jet is ****ing worthless
4) deal with susie, who has database locking problems, she has a
hidden instance of MS Access because you forgot to de-instantiate DAO
variables (ROFL- what a joke, Jet / DAO sucks a donkey-dick)

 

[aaron.kempf]

Ken;

Do you really think that I don't know that?

I'm just saying..

Btw, it's immensly more practical to use the GetDate() function in a
query.. than Date() in a query.. because one is on the server side,
and one is on the clientside.. and if susie's clock is wrong????
Presto-Chango- you've got data quality issues because Jet ****ing
sucks dick.

-Aaron

You never cease to amaze us with your ignorance Aaron.  It is in VBA
and is thus usable in Jet.  Here's what Access's help system has to
say:

Date Function

Returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by
the Calendar property setting. If the calendar is Hijri, Date$ returns
a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd
(01-30) and yyyy (1400-1523) are the Hijri month, day and year. The
equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Ken Sheridan
Stafford, England

Date isn't the name of a built in function.. GetDate is the name of a
built in function

On Mar 4, 9:41 am, (e-mail address removed) wrote:

Chip:
As far as I can see the answer is that you don't need to update
another table with this data; nor should you do so as it introduces
redundancy and leaves the database at risk of holding inconsistent
data.  The information is inherent in the tblPR table and can be
returned with a query like this:
SELECT Student, [Date], Station, Outcome
FROM tblPR  AS PR1
WHERE [Date] =
    (SELECT MAX([Date])
      FROM tblPR  AS PR2
      WHERE PR2.Student = PR1.Student
      AND PR2.Station = PR1.Station);
BTW don't use Date as a column name as it’s the name of a built in
function.  Use something like ExamDate.  If you do use Date then be
sure to wrap it in square brackets when referencing the column.
Ken Sheridan
Stafford, England
Hey Everyone, its me again..
My question today involves writing to multiple tables.  My project is
an Access program that will be used to oversee practical
examinations.  Each student (tblStudents) must pass each of 4
stations.  There are 3 states for each station; Not Tested Yet (the
default), Pass, Fail.  In the tblStudents I have fields that
correspond to each of the four stations, and a combo box with each of
the three states listed.
Each practical exam attempt, has associated with it, a date, a skills
examiner, the station, the class and the state window.  One of the
requirements of my project is to look at the in multiple ways.  Not
only do I want to record when a student passes or fails, but I wantto
know how many times the student had to take that station.  I alsowant
to measure class peformance as  a whole.  I want to measure how
individual evaluators do.  There are limitless ways in which I can
forsee me chopping this data up.
So, the exam coordinator (me and my coworkers) come to a Practical
exam.  We have a list of students as a subform in the Class.  The
class itself has multiple things attached to it, the practical examis
only a small part of it.  When we start getting results of the
Practical exam back, we dbl click on the student name, and a form
opens that has the following items in it.
Student (populated, based on which student I dbl click)
Evaluator (combo box)
Class Number (populated based on which student I dbl click)
Exam Level (combo box)
Station (combo box)
Outcome (combo box, Not Tested Yet, Pass Fail)
Date (populated with Now())
These data points are then written to a table, tblPR.  That tableis
then used to populate other fields.  In this example, each student has
a record.  I record demographic data, paperwork items, etc..  One of
the things in the Student record is a history of the Practical Exam
attempts, complete with date, evaluator etc. all based on the tblPR..
This is a subform, that is only displayed on the student record.
Up to this point, everything works fine.  However, when a student
passes or fails, I want my database to record the date and teh outcome
and do so on each student record.  For example, if you take a station
today, 3/4/2009 and you fail.  I want your current status for that
station to read Fail and the date of status to read 3/4/2009.  Then if
you return next week and take the station and Pass, I want the Fail
status to be overwritten to Pass with teh corresponding date.  That
would be on each studen trecord.
Then again, on the class record, I want the class record to be updated
as well.  So my question is, is my structure appropriate for the
task?  I think it is, since each Practical Exam attempt is its own
entity and can be measured, I was always told that if it can be
measured, it deserves its own table.  If I can overcome this hurdle, I
believe I can replicate that logic on other fronts as well.  We also
adminstered Written exams in the same way.
Your thoughts?
chip