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

    Unanswered: Insert Into Command

    Learnt MySQL and used Update query. Thanks for help though.
    Last edited by Viking-ex; 04-22-04 at 08:34.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Table

    Out of interest...why are you trying to store this information in a table?

    Considering that it can always be created "on the fly" by a query?

    ...Can you zip your (small?) db and post it?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I think garethdart asks a good question.

    Why insert into a table. I'd recommend exploring the DCount function. There are a number of ways to do it.

    Take the count of available films, minus the count of films that are in the rental table and are not yet returned.

    DCount("[FilmID]","FilmTBL","[FilmName]='Mean Machine'")

    DCount("[RentalID]","RentalTBL","[FilmName]='Mean Machine' AND [Returned?] = 'False'")

    (by the way - get rid of punctuation marks in the field names)

    If you use this method, there are ways to pass the name of the movie as a parameter. Post follow up question on the syntax if you are interested.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  4. #4
    Join Date
    Dec 2003
    Posts
    454

    store or get the number?

    Do you want to store the number of rentals of the film into a new table "NumRentalsTBL" or just want to get the numebr?

  5. #5
    Join Date
    Dec 2003
    Posts
    454

    try this

    It is really not necessary to store the number of the rentals of the film since you can get it anytime when you want it. If you insist on doing that, you can try the following steps.

    First, you need to get the number of the rentals of the film based on the query you posted. And then you need to update the fields StockQuantity and NumRentals (you need to add it to the table StockTBL since it does not exists) from the table StockTBL. The query statement is like the following:

    UPDATE StockTBL SET NumRentals = Number_Of_Rentals AND StockQuantity = StockQuantity - Number_Of_Rentals WHERE FilmID IN (SELECT FilmID FROM FilmTBL WHERE FilmName="Mean Machine").

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Unhappy Hmmm....

    Could you please explain WHY you want to store this value in a table?

    I am baffled!!!

    What are you "doing" with the value / using it for?

    Reports? Forms? Calculations?

    I am now most interested...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Unhappy Hello

    ...Please answer my question and we will try and help:

    WHY? do you want to store this information in a table?

    This is a bad idea in my humble opinion.

    Unless you have a VERY, VERY, VERY good reason to do so?, do not look at storing this data considering that you don't need to.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Dec 2003
    Posts
    172
    Viking-ex,

    What is the purpose of each of the tables? For example the people table?

    Thanks.

    Joe G

  9. #9
    Join Date
    Dec 2003
    Posts
    172
    will be back to you - am going to test a few things.

    joeg

  10. #10
    Join Date
    Dec 2003
    Posts
    172
    some thoughts and some examples. let's see which way to go from here.
    Attached Files Attached Files

  11. #11
    Join Date
    Dec 2003
    Posts
    454
    You need to execute two query statements. First you should get the number of rentals from the query you posted:

    SELECT Count(FilmName) AS Number_Of_Rentals
    FROM FilmTBL LEFT JOIN RentalTBL ON FilmTBL.FilmID=RentalTBL.FilmID
    WHERE FilmName="Mean Machine"

    And then you can insert the number into the table StockTBL using the following query statement:

    UPDATE StockTBL
    SET NumRentals = Number_Of_Rentals AND StockQuantity = StockQuantity - Number_Of_Rentals
    WHERE FilmID IN
    (SELECT FilmID FROM FilmTBL WHERE FilmName="Mean Machine")

    I do not think that you will be asked to submit FilmID when you are doing your job.

  12. #12
    Join Date
    Dec 2003
    Posts
    172
    ah, i put two queries in the example to show 2 ways to do this. one query uses dcount() to count the number of ids for any one rental; the 2nd query uses a vb function to get the same result (in my example using the dlookup() function)

    on your new question, do mean mean you are subtracting the number of rentals from the number 40 (for any given film)? what does 40 stand for?

    you can also do a calculating field for a query as an expression such as:

    MyRemainingStock:40-NZ(NumberOfRentals,0)

    I use NZ to ensure that at least a 0 (no rentals for that movie) is returned. I don't want to return a null since you can't add or subtract a null value.

    joeg

  13. #13
    Join Date
    Dec 2003
    Posts
    172

    a quick story about good program design

    You don't want it in the table. you would use a calculating field in a query such as:
    RemainingQuanity:40-NZ(NumberOfRentals,0)
    This would go in the field box.

    But a bigger question to ask is, could you explain the process of how stock is depleted and reordered in your database design?

    I ask for 2 reasons. First, it will help me understand where to advise you on the calculation point for the remaining quantity. Second, it may prevent you from programming yourself into a corner.

    A quick story. Several years ago, my sister was taking a vb programming 101 course at her local college where she was getting a teaching certificate.

    She had to write a program for her final project. As with most new programmers, she was anxious to just start banging away at the keys and making all the cool screens and buttons and graphics.

    But instead I sat her down and we sketched out how the whole program should work (sort of like a storyboard for a motion picture).

    Programming a computer really is like making a movie - the actors are the icons, the stage is the application, and what happens when you press the button is the difference between rave reviews from your customer or not getting paid (LOL).

    Well, we spent 90% of the time designing the whole program, how it should work, what happens to every calculation, and what we would do if the user pressed the wrong button.

    Sis got an A+ on the course and had the only program in the class that ran completely from start to end without one crash or bug.

    My point is that large or small, how the stock of film rentals is calculated, decremented and incremented is critical to your project working properly.

    Whether this is just a semester school project or a real-world application, knowing what the numbers should crunch to will make the programming part easy later on.

    Anyhow a short story for entertainment here tonight but I think you get my point

    JoeG

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Hello

    It's me again!

    Remeber I asked WHY you wanted to store this information in a table when it can always be calculated?

    Here is another point for you:

    Am I right in saying;

    You still have exactly the same stock as you had in the first place, as you are hiring these items are you not?

    Therefore do they come back into stock at some stage dont they?

    Surely you need to calculate what stock is AVAILABLE.

    I could be wrong, but I think you are going down a dead-end here...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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