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

    Unanswered: Quick help needed - Query based prob

    Ok this is for a Video Rental database system. I've got the query:

    SELECT Count(FilmName) AS Number_Of_Rentals
    FROM FilmTBL LEFT JOIN RentalTBL ON FilmTBL.FilmID=RentalTBL.FilmID
    WHERE FilmName LIKE [Film Name?];

    That counts the number of rentals made for a particular film. I then want a Stock query which says "Yes you need to order more stock" or "No you do not need to order more stock". For this to work I need some kindof "if(=" query or something but I'm an utter noob at queries so I dont know how the "if" works.

    Am I right in thinking that the first query must put the results into a seperate table for the Stock query to work? Or can I do something like this:

    (SELECT Count(FilmName) AS Number_Of_Rentals
    FROM FilmTBL LEFT JOIN RentalTBL ON FilmTBL.FilmID=RentalTBL.FilmID
    WHERE FilmName="Mission Impossible]=A

    (SELECT Count(FilmName) AS Number_Of_Rentals
    FROM FilmTBL LEFT JOIN RentalTBL ON FilmTBL.FilmID=RentalTBL.FilmID
    WHERE FilmName="Die Hard]=B

    And then have the Stock query like this:

    if A>30 then printf "You need to order more stock for this film"
    }
    else printf "You do not need to order more stock for this film"
    if B>30 then printf "You need to order more stock for this film"
    }
    else printf "You do not need to order more stock for this film"

    I would really appreciate some help as this is for a school project and I'm very new to databases and queries.

    Thanks.

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

    Since you only need two answers (stock is sufficient or stock is low) you can use the IIF() statement. The first parameter is the test that you make (is the current stock lower than the minimum for reorder?) The second parameter is what you display if the condition is true, and the third parameter is what you display if the condition is false.

    I mocked up a hasty example. See the attachment.

    This is by no means a finished example - for one thing you have to have something to increment or decrease the stock level after each stock reorder or customer purchase. But it should give you the general idea.

    I threw in some film titles to make the example more realistic. Also note I added a column for a minimum stock level so you could set different reorder points for films that might sell better than others.

    The query with the IIF example is qryInventoryAlert.

    Joe G
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Viking-ex
    Wow thanks for your help but I have some more questions.

    That query I have:

    SELECT Count(FilmName) AS Number_Of_Rentals
    FROM FilmTBL LEFT JOIN RentalTBL ON FilmTBL.FilmID=RentalTBL.FilmID
    WHERE FilmName = StockTBL.FilmName;

    I would then take that number and minus it from the StockQuantity to see how much there is left.

    THanks for your help.
    I assume StockQuantity is a field in the StockTBL. There are two options I would see for doing this. You could either create a report/form and do the math at that level, or you can do the math directly in the query. This is how I would personally do it:

    SELECT Count(FilmName) AS rentals, StockQuantity, StockQuantity - Count(filmName) AS curr_inventory
    FROM FilmTBL INNER JOIN StockTBL ON FilmTBL.FilmID = StockTBL.FilmID LEFT JOIN RentalTBL ON FilmTBL.FilmID = RentalTBL.FilmID

    That will return the current rentals, original stock, and current inventory respectively. From there, you could use an iif statement on a form or report to generate the exact string you'd like to see depending on the current inventory levels. Something to the effect of:

    =iif(curr_inventory < 5, "Less the 5 rentals available, order more stock.", "stock is sufficient.")

    Naturally this can be modified to suite whatever triggers and messages you would like to see.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try this:

    FROM (RentalTBL RIGHT JOIN FilmTBL ON RentalTBL.FilmID = FilmTBL.FilmID) INNER JOIN StockTBL ON FilmTBL.FilmID = StockTBL.FilmID;

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

    I'm back. Sorry I missed your post yesterday but I had to go out of town for the day.

    I see Teddy's got you covered. If you need anything else, post me a message.

    Joe G

Posting Permissions

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