Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Query Help (Sum of Quantity based on Expiration date)

    I have a query that I would like to only show the records that have a positive sum of the quantity that have the same Expiration date.

    I have 2 tables tracking inventory of Calk. The tables are as follows.

    Caulk:
    Expires (PK)
    Color

    Caulk Change:
    CID (PK)
    Expires (FK)
    Quantity

    Now I have a query that puts all of that together. I would like to only pull up the records that have a Positive quantity. When I receive a calk order I enter the Quantity as a positive. When someone uses the caulk I put the number in as a negative. I some how need to sum the quantity so that when I go to add or remove more caulk none of the caulk entries that have 0 left are shown.

    I dont know if I have made any sense at all but if you have any inkling of what I am asking please help, Thanks in advance.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Quote Originally Posted by JSThePatriot
    I have a query that I would like to only show the records that have a positive sum of the quantity that have the same Expiration date.
    ...
    Now I have a query that puts all of that together. I would like to only pull up the records that have a Positive quantity.
    ...
    I some how need to sum the quantity so that when I go to add or remove more caulk none of the caulk entries that have 0 left are shown.

    I dont know if I have made any sense at all but if you have any inkling of what I am asking please help, Thanks in advance.

    JS
    Yes, put in the criteria of your query "> 0" in your qnty field, then use the view menu "Totals" option to get the sum. That should do it.

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Nah that pulls all of the received items (because I enter a positive quan. when I receive caulk and negative when I hand it out). I tried doing that, that way but it didnt work. I need it grouped by the Expiration date then sum the quantity by the expiration date then I want it to sort by >0

    Thanks for the reply,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do it just like you said:
    query1 to group by expiration and sum quantity
    query2 to find >0 in query1

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just one query:
    Code:
    select C.Expires
         , C.Color
         , sum(CC.Quantity) as quantityremaining
      from Caulk C
    inner
      join CaulkChange CC 
        on C.Expires = CC.Expires
    group
        by C.Expires
         , C.Color
    having sum(CC.Quantity) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    That below is what I made mine do. I am sorry I didnt include all of the fields I had in the tables on here. Please help me transform the code to do what I am aiming for. When I try to run the query it says...

    You tried to execute a query that does not include the specified expression "CID" as part of an aggregate function.
    I would appreciate any more help. If I cant learn how to use the SQL to fix this then I will just have to use two queries to do what I am wanting.

    Thanks,
    JS

    Code:
    SELECT [Caulk Change].CID, Caulk.Expires, Caulk.Color, Sum([Caulk Change].Quanity) as QRemaining, [Caulk Change].[Date Recieved], [Caulk Change].[Date Released], [Caulk Change].Purchased, [Caulk Change].Project, [Caulk Change].Installer, [Caulk Change].InvID, [Caulk Change].InvAmt, [Caulk Change].[Date Entered], [Caulk Change].User, [Caulk Change].WhoChange, [Caulk Change].ChangeDate
    FROM Caulk INNER JOIN [Caulk Change] ON Caulk.Expires = [Caulk Change].Expires
    GROUP BY Caulk.Expires, Caulk.Color
    HAVING Sum([Caulk Change].Quanity) >0
    Last edited by JSThePatriot; 05-10-04 at 14:50.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm afraid you can't do that, you can't count detail rows and yet show them at the same time
    Last edited by r937; 05-10-04 at 15:01.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    magic!

    rudy - that is the third time you have explained something that i have frequently tried to do (...and failed to do exactly as frequently).

    thanks

    izy


    LATER: s#1t -- you withdrew the magic solution just as i was trying it
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi JSThePatriot

    Could you Zip and Post your Database and see if we can make it work for you? Kinda hard to figure it out without actually playing with it. You can take out your real data and place dummy info in it.

    just trying to help,
    Bud

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i edited my answer to remove the code, because when JS said "I didnt include all of the fields I had in the tables" i jumped to the conclusion that a few extra Caulk fields had been added, when in fact it was [Caulk Change] columns that were added

    since Caulk and [Caulk Change] are in a 1-to-many relationship, when you do an aggregate on a [Caulk Change] column (e.g. sum up the multiple quantities for a Caulk), you cannot simultaneously show individual [Caulk Change] values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    So I guess if I want to keep all the fields I want I would need to create two queries?

    Thanks again for everyone's help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not necessarily, but probably

    could you give an example of the output you expect?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I have a form and subform using the current query as its record source. I was wanting the form to only show the items that have 1+ in inventory. I dont need anyone to edit the items that have 0 tubes in inventory. It will save me/user from scrolling through all of the records to get to the one they need.

    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, you lost me, i can't help you, i don't do forms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay thanks anyway. The form works fine. It is the query I am trying to get to work. I just use the form to sort through the data easier.


    Thanks for your help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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