Programming with dBASE - How do I tackle this?

Author

How do I tackle this?

Pat Curran

2006-12-07, 5:20 am

Not sure I know what to call this, so I will try to describe what I need to
do.

I currently have 200 students. Each student will have their own dbf file.
The file will consist of their name and student number. They will have a
record added to their file for each subject that they have been trained in.
The record will have the subject, and the date that the training took place.
Some student's file may have only 10 records, while another student's file
may have 50. It will look like the following:

Michael Smith

Safety in the Work Area 12/05/2006

Hazardous Mtrl Storage 11/03/2006

Bradley Johnson

Emergency First Aid 10/05/06

Safety in the Work Area 12/05/06

This part is easy. The part I cant figure out is I have to create a
temporary table, and populate the name field of the temp table with the name
for each student. Then I have to have the program open each student file(dbf
table), search for a given subject, and return whatever date they were
taught on the given subject, and populate a field of the temp table with the
date that the subject was taught. I may have 200 or more students. If the
student has not been trained on the given subject, the program should return
their name, and a blank value for the date taught. I know dbase can do this,
but I don't understand the best way to tackle it. Any ideas would be helpful

Thanks

Pat

Roland Wingerter

2006-12-07, 7:12 pm

Pat Curran wrote

> Not sure I know what to call this, so I will try to describe what I need
> to do.
>
> I currently have 200 students. Each student will have their own dbf file.
------
I suggest to reconsider this. This is not a good design, and causes the sort
of problems you describe. With this design, even creating a list of
student's names is almost impossible.

Normally you would have something like this:

Student(StudentId, Last_name, first_name, ...)
Subject(SubjectID, Title, ...)
Student_Subject(Stud
entID, SubjectID, Date)

Roland

Robert Bravery

2006-12-07, 7:12 pm

HI,

Bad design, youre just going to run into more problems
Thisnk of a ctudents table, which has student relative infor, such as name
address.
Then had a subjects file, with relavant information
Then have a bridging/mapping table between students and subject, with
student FK, and subject FK and perhaps date, end any other common
information.
This gives you a many:many relationship
in the mapping file you will include student PK and Subject PK
for each student for each subject. Because many students can take many
subjects, and many sucjects are taken by many students.
This way you can list subjects per student, or students per subject

Robert

"Pat Curran" <curranpg@hotmail.com> wrote in message
news:ZqxydleGHHA.1332@news-server...
> Not sure I know what to call this, so I will try to describe what I need
to
> do.
>
> I currently have 200 students. Each student will have their own dbf file.
> The file will consist of their name and student number. They will have a
> record added to their file for each subject that they have been trained
in.
> The record will have the subject, and the date that the training took
place.
> Some student's file may have only 10 records, while another student's file
> may have 50. It will look like the following:
>
>
>
> Michael Smith
>
> Safety in the Work Area 12/05/2006
>
> Hazardous Mtrl Storage 11/03/2006
>
>
>
> Bradley Johnson
>
> Emergency First Aid 10/05/06
>
> Safety in the Work Area 12/05/06
>
>
>
> This part is easy. The part I cant figure out is I have to create a
> temporary table, and populate the name field of the temp table with the
name
> for each student. Then I have to have the program open each student
file(dbf
> table), search for a given subject, and return whatever date they were
> taught on the given subject, and populate a field of the temp table with
the
> date that the subject was taught. I may have 200 or more students. If the
> student has not been trained on the given subject, the program should
return
> their name, and a blank value for the date taught. I know dbase can do
this,

> but I don't understand the best way to tackle it. Any ideas would be
helpful
>
>
>
> Thanks
>
> Pat
>
>
>

Dulux

2006-12-07, 7:12 pm

Hi Pat,

This might be a silly question, but why are you using a separate dbf file
for each student?

I think if would be easier if you had a Student Table (file) a Courses
Table, and a Linking Table.

The Student table would look like this (for example):
Student_ID Int or Autoinc.
Last_Name Char(32)
First_Name Char(32)
etc..

The Courses table would look like this (eg)
Course_ID Int or Autoinc
Course_name Char(32)
etc..

And finally, the important linking table would look like this:
Student_ID Int
Course_ID Int
Date_Completed Date

This is known as normalisation, and this is "A Good Thing".

This design eliminates your problem, which has to a good thing <grin>

Feel free to ask for further clarification if required.

Cheers

matt
(a trained database designer - if that makes a difference)
--
Matthew James BLACK
M.Inf.Tech.(Data Comms), MBA, B.Sc
Grad.Dip.Inf.Tech
Grad.Cert.Inf.Tech.(Security)
Grad.Cert.Inf.Tech.(Management)
ITIL Certified
Cert II Stock Market Trading & Investment Strategies
MCSE+I, MCSE, MCDBA, CCNA, MCP+I, MCP
Practicing Computer Professional
Senior Member - Australian Computer Society

Email: dulux@wilddsl.net.au
Phone: 04 0411 0089

"Pat Curran" <curranpg@hotmail.com> wrote in message
news:ZqxydleGHHA.1332@news-server...
> Not sure I know what to call this, so I will try to describe what I need
> to do.
>
> I currently have 200 students. Each student will have their own dbf file.
> The file will consist of their name and student number. They will have a
> record added to their file for each subject that they have been trained
> in. The record will have the subject, and the date that the training took
> place. Some student's file may have only 10 records, while another
> student's file may have 50. It will look like the following:
>
>
>
> Michael Smith
>
> Safety in the Work Area 12/05/2006
>
> Hazardous Mtrl Storage 11/03/2006
>
>
>
> Bradley Johnson
>
> Emergency First Aid 10/05/06
>
> Safety in the Work Area 12/05/06
>
>
>
> This part is easy. The part I cant figure out is I have to create a
> temporary table, and populate the name field of the temp table with the
> name for each student. Then I have to have the program open each student
> file(dbf table), search for a given subject, and return whatever date
> they were taught on the given subject, and populate a field of the temp
> table with the date that the subject was taught. I may have 200 or more
> students. If the student has not been trained on the given subject, the
> program should return their name, and a blank value for the date taught. I
> know dbase can do this, but I don't understand the best way to tackle it.
> Any ideas would be helpful
>
>
>
> Thanks
>
> Pat
>
>
>

Pat Curran

2006-12-07, 7:12 pm

Thanks...It hit me like a brick this morning when I woke up and said "Thats
a dumb idea!" I will have one table for the subjects, and one for the
students.

Pat

Robert Bravery

2006-12-07, 7:12 pm

Hi,

You would still need a linking/mapping/bridging table.
Especiall if a student can take many subject, and those same sucjects can be
linked to many students.
You would'nt need a mapping table if one student can have many subjects, but
no other student can take the a subject that has already been allocated to a
student

Robert

"Pat Curran" <curranpg@hotmail.com> wrote in message
news:vmBQxbjGHHA.1524@news-server...
> Thanks...It hit me like a brick this morning when I woke up and said
" Thats

> a dumb idea!" I will have one table for the subjects, and one for the
> students.
>
> Pat
>
>
>

Sponsored Links