strange strange strange

[willem]

I created two tables, and a relation between them. The first table contains student name
and the second table contains the attendancy data from these students. I created a query upon these two tables but it stays empty as long as there is only one line (one record) in the attendancy table. Can i force the computer to go to the next line? When i create a field and fill it with a string, via the default value the table contains only one line. Help me please.

 

[Rich@ITTC]

strange strange strange
This posting seems to appear twice ... double strange, strange, strange!

How have you linked the two tables? What is your Primary Key in your first table - have you used a similar data type in your second table for the Foreign Key?

What sort of relationship have you set up? One to One, One to Many, Many to Many?

Rich Gorvin

 

[willem]

It is a one to one relationship.
primary key is studentid foreign key is leerlingid (dutch for student) actually it is the same. Both are long integers. I was thinking to enter subdatasheet via vba and then move to the next record. can you help me? Is it possible

 

[Rich]

Shouldn't it be a one to many? assuming students attend more than once.

 

[willem]

Studenttable

studentid(primary key)
name
surname
adress
...


Monthtable

studentid(foreignkey)
day1
Day2
Day3
...


There is a relation between studentid and studentid. Everyday one can put in a symbol
A for attended
S for sick
...

I think it is best to use one to one relation, i tried a one to many relation but it had the same result as discussed in the strange strange strange mail.

Willem

 

[joeyreyma]

right, it's one-to-one relationship! but i'm more concern on the structure of the Attendance table because its not normalized for a relational database. i mean, if you continue using this structure and enter data, their will come a time in the near future that manipulating fields or maintaining the entry would be very hard to follow.

i suggest that you go with one-to-many and re-structure your Attendance table.

eg.

ATTENDANCE

StudentId (Foreign)
SchoolDate (Date)
Attend (Yes/No)

and if you want to have a day1, day2, day3 later on, you can do it using the crosstab query.

 

[Rich]

I think you have a structure problem on several points.
You seem to have a different table for each month with each day as a field name if so then scrap it.
Table1
studentid(primary key)
name
surname
adress
Table 2
attendanceID (primary key)
studentid(Foreign key)
attDate Date/Time
and maybe a Yes/No field for attendance
the relationship between 1 and 2 is one to many.
Use a form and subform to enter data for students.
HTH
Both on line together eh.


[This message has been edited by Rich (edited 07-12-2001).]

 

[willem]

thanks for all your help

It took me a long time but i found the problem. In my query i had to use left joins and not innerjoins. Your comments have made me think,think and think my problem over and over and over.

I'll be back.

Willem

 

[R. Hicks]

For you to have a "One to One" relational join you must join Primary Keys of both tables. If you join a Primary Key of one table to a Foreign Key of another table you will have a "One to Many" relational join.

HTH
RDH

[This message has been edited by R. Hicks (edited 07-15-2001).]