Results 1 to 5 of 5
  1. #1
    mike_980 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    58

    query to show "total in store" with input table and output table

    I have a database which I am making to log fertilizer and how much is kept at each location on our farm.

    I have:


    tbl_store_location,
    tbl_fert_type,
    tbl_add_fert,
    tbl_remove_fert

    I have the forms working well, adding and removal of the fertilizer works in two separate tables so that all records are kept and my idea was to just use a date range to compute what was in each store at any point in time but now I have got around to making the queries I'm having a mental block on how to do this, any suggestions?

    Worth noting, I am currently on access 2013 and windows 8 pro 64bit, not got around to changing my profile

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming the data is stored like I think it is, the on hand at any given point in time is the sum of the "adds" less the sum of the "removes". You can get those with queries or DSum().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mike_980 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    58
    Thanks for the reply, I did just that and used queries. My problem now is a little more specific.

    I am using 3 queries to get the result I want, the first two use a sum function to add up the total of each bag, eg there is type 1, type 2, type 3 and each type of fertilizer has 3 weights of bag 500kg, 600kg and 1000kg.

    I should make clear that I am only interested in the quantity of each bag in the store not the total weight of each product etc so my final query needs a result like this:

    Type Location Bag weight quantity of bags
    Type 1 Highfield 500 7
    Type 1 Highfield 600 12
    Type 2 Kingthorpe 1000 500

    and so on....

    The first two queries add the totals of added and removed fine and segregate them up ok the problem is with the 3rd query.

    Here is the SQL code:

    SELECT qry_total_added.Location, qry_total_added.Fert_type, qry_total_added.Weight_of_bags, qry_total_added.SumOfBags_added, qry_total_removed.SumOfBags_removed, [SumOfBags_Added]-[SumOfBags_Removed] AS Expr1
    FROM qry_total_removed, qry_total_added
    ORDER BY qry_total_added.Location, qry_total_added.Fert_type, qry_total_added.SumOfBags_added DESC;



    The first problem is that I need to remove a value before the query acknowledges there is anything in the store so I have had to remove a zero quantity of each before the query will run.

    The main problem is that, if I remove 1 bag of type 1 500kg bag, the 3rd query removes 1 bag of each weight for that type not just the one in the tbl_remove_fert

    If I proceed to remove a different weight of the same type, I then seem to get duplicate values in the query results too.

    Any help i'd be very greatful! pulling my hair out with this!!!!!!

  4. #4
    mike_980 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    58
    If it helps, here is a screenshot of the query I am trying to get work in design view


    Click image for larger version. 

Name:	zsfbzs.png 
Views:	7 
Size:	33.4 KB 
ID:	14054

  5. #5
    mike_980 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    58
    For anyone interested or a potential future googler, I solved this issue:

    It turns out my bad practice was the cause, all I needed to do was create a table for the weights (tbl_weights) instead of just limiting the users choice with in build validation. This meant the database was not fully normalised and I think it was maybe trying to do a "many to many" relationship which obviously you can't do, I don't understand fully why it didn't work that way but I did have a feeling that could be the issue and it turns out i was right

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-05-2013, 06:36 PM
  2. Replies: 3
    Last Post: 04-06-2012, 09:14 AM
  3. 2003 Query "Total" Missing in 2007
    By Buchal in forum Queries
    Replies: 3
    Last Post: 02-14-2012, 09:43 AM
  4. Replies: 3
    Last Post: 07-23-2011, 09:12 AM
  5. Query Table for "*" and then Count all instances
    By Steven.Allman in forum Queries
    Replies: 14
    Last Post: 09-10-2010, 07:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums