Weights and Measure Query??? (1 Viewer)

[The Grand Pooh-Bah]

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..








.

 

[pbaldy]

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 Grand Pooh-Bah]

Here is a copy of what I have so far Link

 

[The Grand Pooh-Bah]

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.

 

[The Grand Pooh-Bah]

If nobody can help me can anyone suggest a tutorial, online course or site that might help me move forward?

 

[JHB]

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.

 

[The Grand Pooh-Bah]

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.

 

[JHB]

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.

 

[JHB]

Did you find a solution?

 

[The Grand Pooh-Bah]

No! I got more entered into the database to get closer, but not query to add things up

 

[jdraw]

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.

 

[The Grand Pooh-Bah]

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

 

[jdraw]

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.

 

[The Grand Pooh-Bah]

So I would do 50 pounds 7 ounces as 807 ounces?

 

[jdraw]

Sounds reasonable.

There are more here
http://www.onlineconversion.com/

 

[JHB]

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.