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

John's hit it right on the head.

First, you tell us what the relationship is between Students and Activities.

Then we can help with the table structure ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JethroUK©" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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" wrote in message
...
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" wrote in message
...
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?