Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    1

    Unanswered: Deleting a record from one table and adding it to another.

    I am currently doing a course which involves databases. I have been asked to create a database based on a video/game rental store. The criteria requires me to make 7 or more tables, due to this i was forced to split some of my tables up.

    Anyway i would just like to know how to delete a record from my "HiredMovies" (movies that have been hired by a customer) and move it to my "ReturnedMovies" table or anything similar to that procedure. It needs to be user friendly and the database needs to be form based so if it can be attached to a button or query which can be used in a form then that would be perfect, the user also needs to be able to enter a return date.

    Any suggestions on how i could do this or even how to set my database out better would be appreciated. Thanks in advance.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I doubt seriously if the object lesson here was for you to create X number of Tables for the sake of creating X number of Tables! I suspect that the instructor felt that this was the minimum needed to for the proper design of the database. You should have a single Table for movies, with all the data relative to a movie, and a Field to designate whether a given one is currently hired or in stock (returned.) I'd do this with a Yes/No Field.

    Call it Hired; if the Field is ticked, i.e.Me.Hired = -1, the movie is currently out, otherwise it is returned.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd go two steps further.

    A table for customers, a table for movies, and a table for checkouts. The checkout would show when a movie went out to a customer and when it was returned.

    Another table might describe the conditions that a movie might have, which could also be tracked in the checkout table. Another table might have media, dvd, Blu-ray, VHS, Beta (that's a joke, you probably don't remember Beta format tapes), which could apply to the movies.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or instead of a check box
    store the date and time it went out, store the date and time it came back, and argaubky store some element that identifies when it could come back.
    whether thats a date time or an interval is up to you and the narrative you need as aprt of the system design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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