I'm trying to program an access database but I'm using SQL for all my querying. I've got the database almost complete but I have one query that has me stumped. It is a database which contains recipes. I have a table in which I have all the conversions for cooking (Tablespoon to Teaspoon, Teaspoon to Cup, etc.). The user needs to be able to put in an ingredient using whatever units the recipe calls for (in other words, I cannot standardize the units, I have to find what they are). Then I need to be able to convert these into a standardized unit. This is where I'm having the problem because things like vegetables can come in cups, tablespoons, etc. whereas things like meats come in ounces, pounds, etc. I want to avoid creating a bunch of vb if/then's. I feel like there must be a way to do this with SQL but I can't seem to figure it out.
as long as you are asking the user for the unit they are using, and you know what you are going for, then you should be able to just query the answer from the file. Assuming your table looks something like:
fromUnit fromUnitFactor (probably always 1) ToUnit ToUnitFactor (whatever it takes to get from the fromUnit to the toUnit)
then just query it out, you know what you are looking for, know what you you have. Then just take the amount given by the user and multiply it by the toUnitFactor you get back.
The biggest problem with this is you need every combination of from and to in your table.
make sense?
I think for cooking you have to separate two different types of units
- by weight
- by volume
You can't standardize these units, or what sense does it make to know that you need 2 cups of meet?
Then when you differentiate those two different types of units, you can standardize them two one unit. Maybe:
- cups, teaspoon,... in ml
- meat, vegetables,... in g or pounds, or whatever
If they all have the same base, then you're able to compare them.
I would think about this differently. You have different types of measures (volume, weight, count, etc.). Each of those measures has different, convertible units. Choosing a measure (ounces, for example), choose both a measure type and a particular unit. I'd have a way of converting between units of the same measure type -- to support resizing recipes -- but I wouldn't worry about converting between different measure types.
Once you know the type, you can store all values in the database in terms of a base unit for that measure type. Based on the value, and perhaps user preference, you can translate that to a suitable display unit when you show it. I don't think this would be particularly easy to do in SQL and I wouldn't be afraid of doing the conversion in code. You simply need to have a different display formatter for each measure type that chooses the appropriate unit and does the conversion.
The user would have to provide you with the unit, and evey unit would have a type (weight, volume, etc.) Your "conversion table" would just need to include a baseline for every unit type. i.e. 8 oz to a cup, 128oz to a gallon. Knowing those two things you can convert cups to gallons.
Doing this in access in a single query is a different story. If you can do a little VBA, get cup to ounces in one call, then ounces to gallons in another, I think you'll have a much easier time.
I got it to pull out the conversion factor for one record. I just did it with queries. I basically look up a column and a row (with sql, the column and row names are variablized) and that value is what I need to multiply by. However, as we all know, you never just work with one record in sql. Any ideas on how to expand this to incorporate all of the data?
OK,
Just wanted to post my solution in case anyone has this problem in the future. I actually make the table and populate it with all the other data that I'm interested in with SQL in the usual way. Then I have a DCOUNT function to tell how many records there are which sets a variable. Then I have a DLOOKUP function which tells me what to convert from and to and sets it in the new table for the record that I'm on. Phew, I think I'm in over my head! Thanks for the help folks.