Can a field store a date and/or any other data type?

I can see the benefit of having a separate table for student activities

tblStudentActivity
StuActID - Autonumber PK
StudentID - Long Integer - Foreign Key to tblStudent
ActivityID - Long Integer - Foreign Key to tblActivity
ActivityStartDate - Date
ActivityCompleteDate - Date

but i'm struggling to see the benefit of a separate table for activity
description (assuming it's as unique as the activity)

tblActivity
ActivityID - Autonumber PK
ActivityDescription - Text

wouldn't it be easier to put both in same table:

tblActivity
StuActID - Autonumber PK
StudentID - Long Integer - Foreign Key to tblStudent
ActivityDescription - Text
ActivityStartDate - Date
ActivityCompleteDate - Date






"Klatuu" <Klatuu DeleteThis @discussions.microsoft.com> wrote in message
news:92D049F3-04E7-4BD4-91BC-913029FFEA6E@microsoft.com...
> Jethro, everyone here is trying to help you. The problem is you are
> trying
> to shoehorn a spreadsheet solution into a relational database. If you
> continue on your current path, it will only get harder. You probably
> would
> be better off staying in Excel.
>
> To be specific, You really don't need the 30 fields for one student. What
> you need is a table that identifies the students, one to identify the
> activities, and another to show the current status of an activity for a
> student. So to keep it as simple as possible (you may need more info in
> your
> tables)
>
> tblStudent
> StudentID - Autonumber PK
> StudentFirstName - Text
> StudentLastName - Text
>
> tblActivity
> ActivityID - Autonumber PK
> ActivityDescription - Text
>
> tblStudentActivity
> StuActID - Autonumber PK
> StudentID - Long Integer - Foreign Key to tblStudent
> ActivityID - Long Integer - Foreign Key to tblActivity
> ActivityStartDate - Date
> ActivityCompleteDate - Date
>
> Now, you use a record in the tblStudentActivity table for each Activity
> for
> Each Student. This is a very basic relational design.
>
> If you have any questions on this, please post back.
>
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "JethroUK©" wrote:
>
>> my problem is only small (as described), but even with your vast
>> knowledge
>> you don't seem to see a simple solution
>>
>> so I don't think reading a book would help me at all
>>
>> i think i'll just have to use two fields to describe start & end every
>> single activity
>>
>>
>>
>> "Jerry Whittle" <JerryWhittle DeleteThis @discussions.microsoft.com> wrote in message
>> news:BB0E51DE-A616-4B7F-91E2-EB21542F7DFB@microsoft.com...
>> > Step away from the keyboard! You are trying to "commit spreadsheet".
>> > Having
>> > 12 fields with similar data with the possibility of going to 30 fields
>> > is
>> > a
>> > very bad idea. Your table has serious normalization problems and just
>> > will
>> > NOT work correctly in a relational database. Period.
>> >
>> > I highly recommend getting some relational database training or reading
>> > "Database Design for Mere Mortals" by Hernandez before proceeding any
>> > further
>> > on this database.
>> > --
>> > Jerry Whittle, Microsoft Access MVP
>> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>> >
>> >
>> > "JethroUK©" wrote:
>> >
>> >> Then is it possible to assign a date type field as 'not null'? - which
>> >> could
>> >> in turn be described on a form/report as 'started' (but not yet
>> >> completed)
>> >>
>> >> I am currently describing over 12 activities with 12 fields/columns
>> >> and
>> >> it
>> >> is likely (once i have database set up i will go on to describe/track
>> >> 20-30
>> >> activities) so i don't really want several fields per activity to
>> >> maintain,
>> >> if one date field will describe it
>> >>
>> >>
>> >> "Allen Browne" <AllenBrowne DeleteThis @SeeSig.Invalid> wrote in message
>> >> news:#EeIqXN7IHA.4192@TK2MSFTNGP06.phx.gbl...
>> >> > No. Unlike columns in a spreadsheet, fields in a database are
>> >> > properly
>> >> > typed, so you cannot store a non-numeric value in a Number field, or
>> >> > a
>> >> > non-date value in a date/time field.
>> >> >
>> >> > --
>> >> > Allen Browne - Microsoft MVP. Perth, Western Australia
>> >> > Tips for Access users - http://allenbrowne.com/tips.html
>> >> > Reply to group, rather than allenbrowne at mvps dot org.
>> >> >
>> >> > "JethroUK" <reply DeleteThis @the.board> wrote in message
>> >> > news:edf6sTN7IHA.1204@TK2MSFTNGP04.phx.gbl...
>> >> >> I'm using Excel to keep data on students
>> >> >>
>> >> >> I'm currently describing the state of any particular field as
>> >> >> either
>> >> >> '*'
>> >> >> = student has started this part of their programme - or a date
>> >> >> "15/7/08"
>> >> >> to describe their end/completion date for that same part of the
>> >> >> programme
>> >> >>
>> >> >> I want to import all this data into Access but can a field store a
>> >> >> date
>> >> >> and/or any other data type as per Excel?
>> >> >
>> >>
>>

"JethroUK©" <reply.DeleteThis@the.board> wrote in message
news:ed5%234Q08IHA.4532@TK2MSFTNGP05.phx.gbl...
>
> but i'm struggling to see the benefit of a separate table for activity
> description (assuming it's as unique as the activity)

It depends how unique the activities are. "Go to study hall" may be a common
activity, but "read page 997 of War and Peace" may be a rare activity. If
you do not have a finite number of unique activites, then the combined table
makes sense. The other approach to look at it is if the two tables end up
being an almost one to one relationship then a single table makes sense. If
there is a many to one relationship on activity then the seperate tables
make sense.

John... Visio MVP