expression and new field on query

[Marko]

HI there, I currently have a database which records book
Loans.

I am creating a Loan query which currently has the
fields:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

What expression or form of validation could I use to show
only books available and stop anyone taking out a book
which is out on loan.

and

What expression could I use to prevent a book from being
renewed more than twice by the same student.

I do not have a renewed field I would like to add it to
my Loan query with the others I just need an expression
to do so.

if this helps my table relationships are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title



Thanks

 

[JulieD]

Hi Marko

a criteria of IS NULL under Loan Returned
in the query will show those books that are still "out"

or IS NOT NULL under Loan Returned in the query
will show those books that have been returned and are therefore available
for loan. You could then base a form on this query as the only books that
will display on the form are those that are available for loan.

Hope this helps
Cheers
JulieD

 

[Marko]

Hi there what you said has helped but you did not answer
my other questions:

How would I make my database be able to record the
renewal of a book by a student?

What expression could I use to prevent a book from being
renewed more than twice by the same student?

I do not have a renewed field I would like to add it to
my Loan query with the others I just need an expression
to do so. Any ideas?

Many Thanks

Marko

 

[JulieD]

Hi Marko

you're welcome, however, to answer your additional questions i would need to
know more about your table structure ...

if you would like to list your tables with their fields and indicate the PKs
i will see if i can assist you further.

Cheers
JulieD

 

[Guest]

My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table Loan

Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko

 

[JulieD]

Hi

i'm unsure of the purpose of the instance table ... is this due to the fact
that you can have multiple copies of the same book in the book table and
therefore each copy is given an additional "book number"?

anyway with regards to the renewals ... i can see two possible alternatives:
1. add a ID field into the loan table and then add an additional table in
called RENEWALS (or similar) with the following fields
- RenewalID (autonumber) PK
- LoanID
- Renewal Date
- Renewal Date Expiry

this renewal for could either be a subform in your current form or under a
"Renew" button on the form and you could then put code on the BeforeUpdate
event of the renwal form to retrieve the LoanID from existing records and if
the count is 2 then say "sorry can't renew again" or similar.

2. (non normalised option) as you are only allowing 2 renewals you could
(but i'm not sure i'm even really happy suggesting this option) add the
following fields into you LOAN table
- Renewal Date 1
- Renewal Date 1 Expiry
- Renewal Date 2
- Renewal Date 2 Expiry

then you can very easily see if the book has been renewed twice by the same
student. However, as this is not a "normalized" solution you could run into
problems down the track ie if the rules change and people can renew books
say 3 or 4 times.

Hope this has given you something to work with.

Cheers
JulieD

My tables are:

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

Student Name = Primary Key in table student

Student Name and Book Number = Composite Key in table Loan

Book Number = Primary Key in table Instance and ISBN is a
foreign key

ISBN = primary key in table book

I also have a Loan query table which is the record source
of my form it consists of:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

I have only used the Loan query table for my form so far.

So, how would I go about making it so my database can
record a renewal of a book [please state where I should
add the renew field] and What expression could I use to
prevent a book from being renewed more than twice by the
same student?

Many Thanks

Marko

-----Original Message-----
Hi Marko

you're welcome, however, to answer your additional questions i would need to
know more about your table structure ...

if you would like to list your tables with their fields and indicate the PKs
i will see if i can assist you further.

Cheers
JulieD




.