Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: Hi guys! Can u help please?!?!?!

    I am producing this 'booking and returning' database for an art dep (just the normal lending out and returning of art equipment. If the student returns the eqipment item late than the teacher has an option to restrict the student from lending out anything for up to three days. Would i need to do this on form level or on within the student table!?!?!

    Many thanks

    Lucy xxx

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    All "levels" are at table level. Forms are for user input, display and data manipulation prior to storing or after retrieving table data. If you could rephrase your quesion better with some more specific info then methinks me can help.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unhappy

    HI there,

    It would be most helpful if you could attach a copy of your database here to better serve you. So far though sounds like a case for an "IF, THEN" type of situation. IF the return date = something, Then, code set a certain field probably. Anyhow it sounds like that's the way you're going. So as asked by ineuw, a much better clarification of your situation. We need to know what you have in your database, what exactly you wish it to do when a certain condition is met, etc. If it's late do you simply with the program to flag the person that it's late and ask if they wish to lend one out again? So many things to know. But hey, we're here to try to help you, so help us out a bit more.

    have a nice one,
    Bud

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    Suppose you have a table called LOANS with fields StudentID, EquipNo, LoanDate, DueDate, ReturnedDate. I think this is all you would need to store in a table. You can derive the most recent “late” date and add 3 days to give you a date to compare to. Then have a form where new loans are entered and use a BeforeUpdate event procedure to check that the LoanDate on the new loan is after the most recent “compare” date.
    Hope that helps.
    Chris
    ps this is my first post

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi Chris,

    Just wanted to step in and say WELCOME to the forum. Hope you enjoy it and keep in mind we are all here to help one another. Also, in times it's best to post a sample database to get better help sooner. Also, in return to give good details to someone in need as we are not all always on the same knowledge level.

    have a great day here,
    Bud
    (again Welcome...)

  6. #6
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Wink thanks guys!

    This is my student table

    StudentID (no)
    First Name (text)
    Second Name (text)
    Surname (text)
    House (text)
    Telephone (text)

    I was thinking that when the teacher had signed back in the equipment if the equipment was late than a if statement could be used restricting that student from taking out any equip for 3 days, i just didnt know if it could be done!!?

    lucy x

  7. #7
    Join Date
    Jul 2004
    Posts
    125
    You need an additional table (let's call it Loaned) with the following fields:

    StudentID Same no field linked to the Students table StudentID Many to one relationship indexed.

    DateOut Date field
    DateIn Date field
    Restricted Boolean, like a check box
    PrimaryKey Autonumber

    If DateDiff("d", DateIn, DateOut)>3 then Restricted = True
    is the statement that would restrict re loaning the instrument

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I was thinking that when the teacher had signed back in the equipment if the equipment was late than a if statement could be used restricting that student from taking out any equip for 3 days
    After my first post I wondered if I'd mis-understood the requirements Lucy initially posted. I wondered if Lucy wanted to be able to set a blocking date for the student which would mean having to log a date against the student in the student table (as Bud picked up on). However, I think Lucy's latest clarification can be answered using the validation rule (therefore doesn't need any VB code) using the principle of a Loans table that ineuw and I have previously suggested:

    We have a table called LOANS with fields EquipNo, LoanDate, DueDate, ReturnedDate, StudentID (the primary key is EquipNo, LoanDate). I think ineuw's LOANED table would also need EquipNo to be in the primary key.

    Assuming you have a form called LoanForm, the following expression can be inserted into the validation rule of the LoanDate entry box properties:

    >DMax("[ReturnedDate]","Loans","[ReturnedDate]>[DueDate] and [StudentID]=[Forms]![LoanForm]![StudentID].Value")+3

    Assuming a StudentID has been entered, the validation rule will check that for overdue returns, a new loan date is greater than 3 days past the most recent overdue returned date.

    Use the Validation text to give an appropriate warning message.

    The above does not take account of someone who has overdue items but hasn't returned them yet i.e. the ReturnedDate field is null. But an IIF statement in the above expression should sort that or another validation in the ReturnedDate box on the form.

    The [ReturnedDate]>[DueDate] could be replaced by the more powerful DateDiff function as ineuw suggests.

    Also, as ineuw mentioned, the LOANS table should be linked to the Student table to give the full relationship.

    Hope that's of use
    Chris

    PS thanks for the welcome Bud

  9. #9
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    guys

    Thanks for the help I ave already got a booking table which has all the simular fields suggested in the loans table except for the boolean checkbox (sorry i should of mentioned i had a booking table to!) So im thinking that in the booking table should also be the restroction check box??

  10. #10
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    lucy xxx

    sorry thanks for ya help :0)

  11. #11
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Cool cool guys!

    I think I understand the loaned table now....when the equipment is brought back if the date exceeds the date required back then the restriction would be put upon the student from being able to loan anything out for 3 days. So the loan table would be a reference to when it was returned and who restricting the student from booking anything out???

    ^^^^^^

    does that make sense!?!?!?

    luc

  12. #12
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    could u........

    have a boolean check box type thing on the student table, so when the equipment was signed back in then if it were returned late the check box would be ticked 'freezing' the student account?!?!

    luc xxx

  13. #13
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by lucyg_2000
    have a boolean check box type thing on the student table, so when the equipment was signed back in then if it were returned late the check box would be ticked 'freezing' the student account?!?!
    Lucy There's several ways of doing what you want. The method I described doesn't need to log the student as frozen because it can work it out from the loan history so don't confuse it with the way you are going. The plus side to it is that it doesn't use VB code. The down side is that it's a bit rigid. See my example LOAN database. The interesting bit is in the properties of the LoanDate box on the form. Try creating a new entry for student A0001 with the loan date as 7th Aug 2004.

    Having a flag in the Student table (as suggested by Bud) sounds like the way you are heading. It’s more flexible but slightly trickier to implement IMHO. Are you familiar with VB code ?

    If you choose to have a flag then you need to be able to get your database to do the following as I see it:

    1) For returns, if the equipment is overdue, set the flag in the Student table.

    2) For new loans check if the student's frozen flag is set and if so then check if the 3 day frozen period has expired:
    a) If expired, clear the flag and allow the new loan
    b) If not expired disallow the new loan

    In VB this is fairly easy to implement. Do you want to try this route ?

    What fields do you have in your Loans table ?

    I've attached another database LOAN2. This uses a query to keep updating the flag in Student table. But it still relies on the history so you need to have a RETURNEDDATE field. Its not good database practice to store derived data like this but it might give you some ideas. VB is the best way to go though.

    Chris
    Attached Files Attached Files

  14. #14
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    thank you

    thanks for ya help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •