Multiple Dates Tracking Database

[Ldappa]

Hi,
I have read through the posts but am unclear on how I would create the above
database.

I am creating a database with ID,Name,Date,Time.

A need to track each student signing into the room. I have set up a form
where a student will log in each time they attend and I would like the "each
date" to be recored into the database.

Do I create a separate database with fields date1,date2,date3 ect.......?

 

[Allen Browne]

Use 3 tables:

Student table (one record for each student), with fields:
- StudentID AutoNumber primary key
- Surame Text
- FirstName Text
etc

Room table (one record for each room), with a RoomID primary key

Entry table (one record for each time someone enters a room), with fields:
- EntryID AutoNumber primary key
- StudentID Number relates to the StudentID in Student
table
- RoomID Number relates to the RoomID in the Room
table
- EntryDateTime Date/Time when the student entered the room

It's really important to get this data structure right.

It's also really important not to use fields named Name, Date and Time, as
these will cause you grief in Access.

 

[Ldappa]

Hi,

Thanks for the quick response. The field names I used were general I will
use different ones when I create a the acutual table

I read your answer and I am confused at how this will work. I think I only
need two tables, one with student information and the second being the
dateattended table.

I have a form that a student logs in(they are already in the database at
this point) and I want to automatically put the date they log in "today's
date" into the dateattended table. How do I have the each date accumulate in
the date table.

Example:
0002 Sally Smith 10/01/08
10/02/08

 

[Ldappa]

Sorry, I'm still a little confused.

I will just need two databased as one will have student info and the other
dates attended.

On the dateattended, how will the database look?
datefield1,datefield2,datefield3?

When a student logs in I would like the form to automatically add the
current date to the dateattended database. How will I get it to put the date
in the correct field.

Would it be if datefield is blank then datefield2 and so on???

 

[Steve]

You need three tables if you have more than 1 room or two tables if you only
have one room.

As for automatically recording the date, go to the attendance table in
design view, select the date field, go to the Default Value box at the
bottom and enter Date(). Today's date will be automatically entered every
time you create a new record.

Steve

 

[John W. Vinson]

Hi,
I have read through the posts but am unclear on how I would create the above
database.

I am creating a database with ID,Name,Date,Time.

Change those fieldnames. Name, Date and Time are all reserved words.

A need to track each student signing into the room. I have set up a form
where a student will log in each time they attend and I would like the "each
date" to be recored into the database.

Do I create a separate database with fields date1,date2,date3 ect.......?

NO.

You're using a relational database. Use it relationally! You will certainly
need at least two or three tables: a table of Students with StudentID,
LastName, FirstName, etc. (and this should be the only table containing
names); probably a table of Rooms unless there is only one room that you will
*EVER* be reserving; and a log table with fields like LogID (autonumber
primary key), StudentID, AttendanceDateTime, and perhaps more. Access
Date/Time values contain both the date and the time so it's rarely necessary
to have separate date and time fields. Each instance of attending would be
stored as a new record in the log table.

Take a look at some of the tutorials at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Ldappa]

I guess my question is regarding the form. I have made a form which has a
entryfield for a student to enter their ID, when they do this I need the
second database(ie the log) to document the date/time. This needs to happen
each time they log in.
Can I use a query to connect the two databases(do I need an update query for
the student id's to get into the Date Log?

I've tried a couple of things but it does not seem to be connecting.

I use mostly linked tables that I cannot use the relations function so I'm a
little lost, nor do I use primary keys or autonum

 

[John W. Vinson]

I guess my question is regarding the form. I have made a form which has a
entryfield for a student to enter their ID, when they do this I need the
second database(ie the log) to document the date/time. This needs to happen
each time they log in.
Can I use a query to connect the two databases(do I need an update query for
the student id's to get into the Date Log?

I've tried a couple of things but it does not seem to be connecting.

I use mostly linked tables that I cannot use the relations function so I'm a
little lost, nor do I use primary keys or autonum

First off... jargon alert. In Access a "Database" is the .mdb or .mde file, a
container for multiple Tables, Forms, Reports and other objects. I think
you're referring to Tables here. I have no idea what you mean by "the
relations function" - what and where are your tables?

You could have just a form with a combo box to select the student, and a
textbox with a DefaultValue property of =Now(). When the student selects her
name, the date will automatically fill in.

 

[Ldappa]

Sorry I'm an old Dbase User so I tend to use the old jargon. You are correct,
I do mean table.
The students will put in their ID(cannot use combobox as I don't want them
to get a list). Each time they put in their ID I need the second table to
log the date and some other information.

Sally Smith comes in and puts in her ID via a form which looks up her
previous information from the first table which is queried with the second
table and "today's date" automatically puts in the date. When she comes in
the next day and puts in her ID the second table adds that date.

When you query the first table with the second table you will see
Sally Smith,00002,10/10/08
10/20/08

Does that make more sense.

 

[Allen Browne]

In your example, what's the 0002?
Is that just a unique record identifier?
Or is it the room number?

Do you need to record which room the student entered? If you don't want to
record which room it was, then you don't need the Room table I suggested.

To automatically record the date and time, set the Default Value of the
EntryDate field to:
=Now()
If you wish to record just the date (without the time), use:
=Date()

As in the answer from John Vinson, it is crucially important that you do not
use repeating fields such as Date1, Date2, ... Use a related table with many
*records*, not one wide table with many fields.