Results 1 to 12 of 12
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Help creating weight calculator

    I have no idea where to start on this one. I would like the user to click a button and have a form open. This form would allow them to enter 1 or multiple products and the quantity of each and get the resulting total weight. I have a table with 2 columns; product and weight. I am thinking something like the image below but I am not sure if this is the best way to go about it at all.



    As always any advice would be appreciated

    Click image for larger version. 

Name:	Capture.JPG 
Views:	28 
Size:	16.7 KB 
ID:	35562

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A continuous form with a textbox in the footer or header that sums the total field should work (=Sum([Total]). Quantity and Total are to be unbound fields I hope.
    Your table doesn't look like the above, yes?
    Last edited by Micron; 09-20-2018 at 09:51 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    A continuous form with a textbox in the footer or header that sums the total field should work (=Sum([Total]). Quantity and Total are to be unbound fields I hope.
    Your table doesn't look like the above, yes?
    All I have is a table with 2 fields....Product and Weight. I am not even sure where to start to get the ability for the user to choose a product and enter a quantity

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    A continuous form with a textbox in the footer or header that sums the total field should work (=Sum([Total]). Quantity and Total are to be unbound fields I hope.
    Your table doesn't look like the above, yes?
    Ok - I am getting somewhere - thanks for the help.

    This is what I have in design view - set to continuous forms ( I spaced it out for ease of reading)

    Attachment 35564

    When I switch to form view it looks pretty good except the Quantity updates to the same value I enter in all fields and the total is not working. Any ideas how to get the quantity field to be able to handle different values? So the user could enter 1 for 1 product and say 4 for another etc...

    Click image for larger version. 

Name:	RESULT.JPG 
Views:	23 
Size:	39.9 KB 
ID:	35565

    Thanks again for the help

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You have no place to save (store) the qty or Unit_Total except in the 2 textboxes. Since those textboxes are unbound, once you type something in them (or calculate a value), it's just there sitting on the form until you type something else.
    On the other hand, Product and weight are bound, so they draw their values from the table.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What's the purpose of the form? If it's only to perform calculations, you don't actually need any bound fields. However, if the either source data or figures/values are going to be saved, then you do require them. So as pointed out, you can't save qty with this setup. What you should not save is Total. 99.8795% of situations dictate that you shouldn't be storing calculations.

    Also, I goofed (and I know better but wasn't concentrating ). An unbound control on a continuous form cannot hold different values in different rows. What you probably should be saving is everything but the total. Your calculated control may also need to use DSum with criteria (such as limiting to an order number) so as to sum over a table field. Alternatively, it could be populated via a custom function. You will have to add a qty field to your table, but what might still be missing is, what ties these items together? A shipping/order ID? That's probably missing too. Maybe more info behind what you're up to would help.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Strictly speaking to the criteria of the OP it seems to me the simple way to go about it is have a temp table for when the form opens with fields product_fk and qty then set up a continuous form that micron suggests.

  8. #8
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    What's the purpose of the form? If it's only to perform calculations, you don't actually need any bound fields. However, if the either source data or figures/values are going to be saved, then you do require them. So as pointed out, you can't save qty with this setup. What you should not save is Total. 99.8795% of situations dictate that you shouldn't be storing calculations.

    Also, I goofed (and I know better but wasn't concentrating ). An unbound control on a continuous form cannot hold different values in different rows. What you probably should be saving is everything but the total. Your calculated control may also need to use DSum with criteria (such as limiting to an order number) so as to sum over a table field. Alternatively, it could be populated via a custom function. You will have to add a qty field to your table, but what might still be missing is, what ties these items together? A shipping/order ID? That's probably missing too. Maybe more info behind what you're up to would help.

    The form is a way for the user to select a product or products and it/their quantity(ies) and get the total weight of all pieces.

    the form would open and the user would either select or enter the product(s) and the quantity(ies) and the total weight of everything would show. The table I have has the product and its weight. Once they get the total weight they would close the form and the data does not need to be saved at all.

    Table looks like this
    Product Weight
    123 55
    456 65
    789 120
    etc

    The form or whatever it is the user would use would be something like this:

    Product Quantity
    123 2
    789 4

    and the result would be the total weight of 590

    Hope this helps and again I appreciate your time and assistance

  9. #9
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I created a table with 2 columns, autonumber_id and qty and added 1-10 in the qty field.

    I then added a field to the product & weight table called quantity and made it a lookup to the quantity field of the new table.

    Now I can select quantity for each row and it subtotals each product as well as totals the whole thing in the header which is exactly what I wanted.

    Do you know how I make the product field a drop down where they could choose the value(s) they want instead of having to scroll through the list to find the product - it is not is ascending order - i am working on that

    Click image for larger version. 

Name:	Capture.JPG 
Views:	14 
Size:	42.0 KB 
ID:	35570

    Do you think this is a viable solution?

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Have a look at this ProductWeightThingy.accdb

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well... maybe, maybe not. While it may work, lookup fields in tables should be avoided if possible. Partly because you shouldn't be working in tables (at least, that's how I interpret your last post). Then there is the fact that what's stored in this field is really the index number of the source, though you see the value itself. This can introduce difficulty trying to query against "apple" when it's really "2". And what happens if you need to enter 15?

    Your product table ought to have fields for each characteristic - length, weight, part number, whatever. However, what I'm still not understanding is what is this all about/for? Is it orders? Then you need an order items (and orders) table. Order items is where the quantity would be entered via the form. Your form would get all the product info plus input the orders info (qty) and do the math for the weight. Or is the form for creating a manifest for the load? Then you'd need a table to hold all the manifest data (carrier, product id, product qty for the load, etc). Again, the form calculates the weight according to the product qty in the load table. Product qty for a load could/should come from the order items table if there is one.

    Your form looks like it compiles a list of product items, but at the risk of repeating myself, a list for what? It looks like it ought to be a subform list (many) on a main form, where the main form is the order/manifest/whatever (the one).

    Why would you want to clear this form? It would delete all the underlying (bound) data. Again, if it's only an Access calculator, then fine; form combos that are bound to lookup tables (not fields) or have value lists as their row source is probably good enough.
    Last edited by Micron; 09-21-2018 at 10:01 AM. Reason: clarification

  12. #12
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    THAT IS AWESOME!!!! and I now have it working in my database.

    THANK YOU VERY MUCH!!!

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

Similar Threads

  1. Calculating Average Weight - Broken SUM
    By redekopp in forum Access
    Replies: 13
    Last Post: 04-12-2017, 09:04 AM
  2. Replies: 5
    Last Post: 01-21-2016, 02:52 PM
  3. calculate total weight from subform
    By msasan1367 in forum Access
    Replies: 1
    Last Post: 08-05-2013, 12:06 AM
  4. Setup a field to Total Weight in My Report
    By Triland in forum Access
    Replies: 2
    Last Post: 01-25-2013, 02:11 PM
  5. Calculator
    By Azeez_Andaman in forum Access
    Replies: 1
    Last Post: 08-14-2011, 12:10 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