Expression to return the name of the term from a registration date

[Hilarys]

I am setting up an Access database for 6th form student data.
I have a "Registration" table for their daily registration with :
RegistrationDateID
Date
Pupil ID
Present/Absent
Term ID

And a 'Term' table with the dates for the 6 terms (Autumn Yr 12, Spring
Yr12.......Summer Yr 13) already entered with:
Term ID
Term
Beginning date
Ending date

The tables are joined with a many to one join ( many dates can have only one
term).

I want an expression to return the name of the term which the date in the
registration table falls in.
Help!

 

[Ken Sheridan]

You should be able do this with the DLookup function, as the ControlSource
for a text box on a form bound to the Registration table for example:

=DLookup("Term", "Term", "[Term ID] = " & [Term ID])

Or you can join the tables in a query with something like:

SELECT [Pupil ID], [Date], [Present/Absent], Term
FROM Registration INNER JOIN Term
ON Registration.[Term ID] = Term.[Term ID];

The latter query would not be updatable, however.

BTW I'd advise against the use of Date as a column name. It could be
confused with built in date function in some circumstances and give false
results. Better to use something like RegistrationDate.

Strictly speaking you don't actually need the foreign key Term ID in
Registration; the tables can in fact be joined on the registration date
falling within the range defined by the Beginning Date and Ending date
columns in Term. This redundancy does leave the door open to update
anomalies in that a registration record could reference a term record where
the registration date falls outside the term. However the use of a Term ID
foreign key does make the join somewhat easier to do, so I'd be inclined to
stick with the current design even though it is technically flawed.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Ignore my comment about updatability of the query in my last post. That
actually referred to another query which I'd written before I noticed you had
the redundant Term ID column in Registration. The query joined the tables on
the dates by means of a join criterion in the WHERE clause; queries of this
type are not updatable:

SELECT [Pupil ID], [Date], [Present/Absent], Term
FROM Registration, Term
WHERE Registration.[Date]
BETWEEN Term.[Beginning date] AND Term.[Ending date];

Ken Sheridan
Stafford, England