Weights and Measure Query???

T

The Grand Pooh-Bah

Registered User.
Local time
Today, 06:34
Joined
Feb 16, 2013
Messages
17
I haven't used Access since the first version back in the 90's. So basically I'm an old greenhorn.

Just got Office 2010 and want to build an inventory database that will have everything from bandages to bullets, rice to fertilizer, buckets to shovels.

Im having a problem with getting totals on an item.. I.E. 1 gallon 3oz in bucket 34 and 15 fl oz in jar 56 and 31 fl oz in tub 45 How do I get the total Gallons/Ounces. Guess I'm looking for a Weights and Measure Query to plug into my database??

Anyway that is where I'm at on this little project, which is frying my old tired brain..








.
 
It may help to know your table structure. In general, I would store a consistent value, which in this case might be ounces. From there it would be relatively simple to sum up the value, and then to convert a result like 200 ounces to gallons/ounces. Key tools in that are the Mod operator and the Int() function.
 
The purpose of this database is to keep track of an inventory. Items are stored in different locations and in containers in the locations. I need to know what items/quantities/sizes are in the container. I also need to know all the different locations/containers any specific item is located. I also need to know the total amount of a specific item is in stock, down to the ounce, inch or pound.
 
If nobody can help me can anyone suggest a tutorial, online course or site that might help me move forward?
 
Only a quick reply then I go to work. I looked at it yesterday, but as I remember, there was little data in the table where the various items were in stock. What I lack is the same goods with the same content in different places. But try to show how you want the result, with item names, content etc. So it is much easier for us to understand how you want it.
 
I added some items to the inventory and did a quick duplicate query to show them. While entering the size of items I ran across some items that are in pounds and ounces I.E. 4lbs 3oz. I put the unit value at pounds but this will be a problem since there are ounces to add also.
 
My idea would be when the query is run to convert all the different values ​​to kilograms and liters and then when summation is done, convert it back to pounds and ounces and gallons.
Or how you name it.:)
 
Did you find a solution?
 
You will have to determine what the database is really about.
Also, you should be working toward a minimum set of Units on Measure.


A conversion table for oz to Gal, pounds to Kg or whatever.
When you do any queries or reports involving Sums or comaprisons you will need a common metric. THis what Paul and JHB have been suggesting as well.
 
Let me get this straight. I have to convert the weights into metric equivalents in order to add them up. then convert them back to standard weights? If this assumption is correct then I also need to use a constant metric equivalent such as grams?

So for 50 pounds 7oz I would enter it as 22878.047grams
So for 3 yards 4in I would enter it as 2644.8 millimeters
 
Last edited:
No that is NOT what I said. You need to do Sums and comparisons in a common unit of measure.
Convert weights to oz or grams or whatever
You have to have things in common units/terms to do comparisons or sums.
 
I've play a little with you data converted it to kg/liter/meter and back again, for all the data which the query "Find duplicates for SHTFListQ" return, summed them all only grouped at item name, (like you would do if you want to know the amount of all in "stock"). I've attached a picture of the result. The field "TotalBase" is the interesting part.
attachment.php
 

Attachments

  • Result.jpg
    Result.jpg
    101.7 KB · Views: 163
You must log in or register to reply here.

Similar threads

T
Greetings From Idaho
Replies
1
Views
603
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom