Any Open Office database experts here?

[Julie in CA]

I'm trying to create a database with a form to fill in, and I'm completely incompetent. :glare:

 

Here's what I'm trying to keep track of:

Ingredient (ex: "flour")

Cost (would be the price I paid, like "$3.59")

Quantity (would be size of pkg, like "10 lbs." or "16 oz.")

Source (name of supplier, like "Sysco rest. supply")

Ref. Date (this would just be the date I confirmed the price)

Conversion to U (would usually be a whole number, like 160)

Unit (would usually be something like "oz.", or "g.", or "ea.")

Cost per Unit (would be something like $0.22)

Commonly Used Meas. (would be where I'd put in something like "1/2 c=4 oz", just a reference I'd often use for that ingredient in a recipe)

Notes (anything misc. I'd like to make a note of, like "don't buy generic for this item")

 

Is this ridiculously complicated? :001_huh:

Help!!

[Julie in CA]

Just a lil bump. I know someone here will be able to help!

[dbmamaz]

so the point is, any time you have a recipe, you look up the ingredient and it tells you how much it costs? or are you trying to get the database to automatically calculate the cost of a recipe?

 

And is it just me, or do prices change monthly anyways?

 

to just create a flat table with those fields could be easy (although since unit price is total price divided by units, there is no need to store). but i'm not understanding how you would use this

[Susan C.]

I'm not great on the computer either, so I would get a stack of index cards.

 

But I do know someday I will have to take the plunge!

 

I'm wondering if you need to use Excel....(and I tried that and failed greatly)

[Julie in CA]

so the point is, any time you have a recipe, you look up the ingredient and it tells you how much it costs? or are you trying to get the database to automatically calculate the cost of a recipe?

 

And is it just me, or do prices change monthly anyways?

 

to just create a flat table with those fields could be easy (although since unit price is total price divided by units, there is no need to store). but i'm not understanding how you would use this

You are right that in ordinary circumstances, it would not be worth it. For professional reasons though, I have to cost out recipes almost daily. I'm not looking for the database to automatically calculate the cost, I'm just looking for an easier way to keep price listings than the extremely old-fashioned & messy spiral notebook that I'm using now. It takes me a long time to even find an ingredient listing in my notebook, because they're listed rather randomly, and not even alphabetized. I'm also constantly adding different ingredients and their prices as I order supplies.

 

Yes, the cost of ingredients changes rapidly, hence the listing for the last date the price was confirmed. I don't need to be exact, but I do need to be able to easily estimate how much each new recipe will cost me to make, so I can accurately charge customers for finished products.

 

I'm just getting frustrated because I don't know how to set up the database.

Ask me anything about pastry, but don't expect me to know how to keep the doggone database from rounding dollar amounts to the next higher dollar, lol! I'm sure this would be easy for someone, but that someone is not me. :glare:

[Liz CA]

I think I'd do it in Excel.

 

Start with the blank sheet and put the food item in the left most column - leave 2 columns blank on top. Add other food items vertically (down the column). Then put the criteria you mentioned in the columns horizontally at the top spaces.

You can now alphabetize the food item column by clicking on the very top space and then click on the icon that shows "A/Z" with arrows. It now alphabetizes your ingredient list. If you cannot find the icon on your toolbar, check in all the pull down menus - it's perhaps in editing, I cannot remember.

All other columns can be customized by clicking on the very top space of the column and then right click inside any of the spaces and choose "Format Cells." Now you can choose if the number in this particular column should be formatted as a Dollar amount or a general number.

 

Does this help?

Edited September 17, 2012 by Liz CA

[Julie in CA]

I think I'd do it in Excel.

 

Start with the blank sheet and put the food item in the left most column - leave 2 columns blank on top. Add other food items vertically (down the column). Then put the criteria you mentioned in the columns horizontally at the top spaces.

You can now alphabetize the food item column by clicking on the very top space and then click on the icon that shows "A/Z" with arrows. It now alphabetizes your ingredient list. If you cannot find the icon on your toolbar, check in all the pull down menus - it's perhaps in editing, I cannot remember.

All other columns can be customized by clicking on the very top space of the column and then right click inside any of the spaces and choose "Format Cells." Now you can choose if the number in this particular column should be formatted as a Dollar amount or a general number.

 

Does this help?

I don't have Excel. :sad:

[Annie]

I think I'd do it in Excel.

 

:iagree:

 

I think you'll be better off trying to use a spreadsheet to manage this information. Do you need/want to keep all of the historical price information or just the most current price?

 

I took a semester long database creation class in business school, and I think it would take me quite a bit of time and study before I would trust myself to come up with a good database.

[Annie]

I don't have Excel. :sad:

 

Do you have the open office spreadsheet program?

[HSMom2One]

Open Office has everything that Micr*soft Office has, and is completely compatible. I use it all the time. Just launch the program and select 'spreadsheet'. Then follow the same directions for Excel listed above.

 

Blessings,

Lucinda

[MomatHWTK]

In database terminology, each of those categories would be called a "field." So to set up the database, you list your fields, then you can enter data line by line or create a separate form that allows you to enter data for each field per item.

 

Try these tutorials if you want to go more in depth. You could also set up a simple table in the Open Office version of MSWord.

Edited September 17, 2012 by MomatHWTK

[Julie in CA]

In database terminology, each of those categories would be called a "field." So to set up the database, you list your fields, then you can enter data line by line or create a separate form that allows you to enter data for each field per item.

 

Try these tutorials if you want to go more in depth. You could also set up a simple table in the Open Office version of MSWord.

To give myself just a teeny bit of credit, I did remember that they're called "fields". :) I remember bits and pieces of database creation from my time (waaaay back when) as a student, just not enough. :glare:

 

My problem is assigning the right *type* of field, I think. I'm not getting that right. There's also something about designating a "key" for each entry into the database, and it won't let me move on without doing that. :confused:

 

I will spend some time with the tutorials. Thanks for posting the link!

[KAR120C]

To give myself just a teeny bit of credit, I did remember that they're called "fields". :) I remember bits and pieces of database creation from my time (waaaay back when) as a student, just not enough. :glare:

 

My problem is assigning the right *type* of field, I think. I'm not getting that right. There's also something about designating a "key" for each entry into the database, and it won't let me move on without doing that. :confused:

 

I will spend some time with the tutorials. Thanks for posting the link!

Just add a field for the key and then proceed to ignore it. In Access you can make it an autonumber field so it just increments by 1 for each new record. I don't know if Open Office does that, but it's a pretty standard feature so it probably does. Anything you're calculating with (prices and units) should be a number, and anything else can be text. If there isn't a currency type of number there will be some kind of decimal, possibly called "single" or "double" or "float". For currency it won't really matter which one you choose - they'll all be able to manage two decimal places.

 

I did a similar database in Access for my own grocery budgeting (and yes, it's excessive, but databases are what I do!) and I matched the base units to the USDA standard (I think it's 100g but it's been a while) so I could do nutrition information too.

[KAR120C]

You are right that in ordinary circumstances, it would not be worth it. For professional reasons though, I have to cost out recipes almost daily. I'm not looking for the database to automatically calculate the cost, I'm just looking for an easier way to keep price listings than the extremely old-fashioned & messy spiral notebook that I'm using now. It takes me a long time to even find an ingredient listing in my notebook, because they're listed rather randomly, and not even alphabetized. I'm also constantly adding different ingredients and their prices as I order supplies.

Instead of a single standard (grams or whatever) for ingredient amounts, it might be just as simple to use a standard for each one that matches how you'd use it. Like cups for flour and teaspoons for vanilla. kwim? Then when you're pricing it you can look at the cost list and say flour is .10/cup, and this recipe is 6 cups, so .60 for flour; vanilla is .25/tsp and this is a 2 tsp recipe so .50 for vanilla. There's no particular reason why they'd need to have the same standard... just a way for you to translate between shopping quantities and baking quantities.

[Julie in CA]

Instead of a single standard (grams or whatever) for ingredient amounts, it might be just as simple to use a standard for each one that matches how you'd use it. Like cups for flour and teaspoons for vanilla. kwim? Then when you're pricing it you can look at the cost list and say flour is .10/cup, and this recipe is 6 cups, so .60 for flour; vanilla is .25/tsp and this is a 2 tsp recipe so .50 for vanilla. There's no particular reason why they'd need to have the same standard... just a way for you to translate between shopping quantities and baking quantities.

I convert most (but not all) of my recipe measures to grams or oz. anyway, since it's a more exact way to measure many ingredients. I use my scale for most ingredients, and then I don't have to dirty a measuring cup for most things. :D

[Julie in CA]

Just add a field for the key and then proceed to ignore it. In Access you can make it an autonumber field so it just increments by 1 for each new record. I don't know if Open Office does that, but it's a pretty standard feature so it probably does. Anything you're calculating with (prices and units) should be a number, and anything else can be text. If there isn't a currency type of number there will be some kind of decimal, possibly called "single" or "double" or "float". For currency it won't really matter which one you choose - they'll all be able to manage two decimal places.

 

I did a similar database in Access for my own grocery budgeting (and yes, it's excessive, but databases are what I do!) and I matched the base units to the USDA standard (I think it's 100g but it's been a while) so I could do nutrition information too.

It automatically created the field for the key, and will not let me move on unless I enter something there for every single record. :glare:

 

You'd think I'd be able to select the right field type for the price too, but no matter which choice I select, it seems to round the price to the nearest dollar. I must be missing something there, but I can't figure out what. :confused:

[5of5]

It automatically created the field for the key, and will not let me move on unless I enter something there for every single record. :glare:

 

As another person said, you should have the option to make the key field auto-increment, but you might have had to specify that when it was first created.

 

 

You'd think I'd be able to select the right field type for the price too, but no matter which choice I select, it seems to round the price to the nearest dollar. I must be missing something there, but I can't figure out what. :confused:

 

There may be a default format associated with the fiield that rounds. If so, you should be able to select a different format, one that handles money.

 

Having said all that, I agree with several other posters that this would be easier to do in the spreadsheet part of Open Office.

[dbmamaz]

yes, spreadsheet is probably easier - except with a spreadsheet, if you are not careful, you can sort just one column without the rest, essentially turning it in to garbage.

 

yes, there should be a way to automatically fill the key, but otherwise, just put in a number and make each one one bigger than the last. its just a counter, basically, so each row has a unique number.

 

and yes, you shoudl be able to change the number settings for the field in design view? (i've only used access . . . and oracle .. .)