The subject of using spreadsheets and databases for keeping information about seeds and plants came up in another thread here in the UMG forum. I'm adding my contribution to that discussion here so as not to hijack the other thread. LOL
First a little on the history of the "why" I did it and then I'll show you a bit about the "how" and what the results are.
(I'll let you know when my 'chapters' are ready for comment...ok? In the meantime, you can read but please don't post any comments here until I'm finished. LOL I'm "creating" and can't be disturbed. HAHAHAHA)
Gardening is not a recent addiction for me, I loved flowers and gardens all my life. But it was only after a 'forced' retirement that I found myself with time enough to actually get into it. With the circumstances being that I'm too old to make the same mistake more than twice (LOL) I needed to keep track of gardens. That meant I wanted to know what plants and/or seeds I'd tried to grow, where I got them, what specific needs they had and how could get *more* of them in the future?
I started out by trying to keep paper notes, then I tried paper notes in an electronic form. I tried desperately to locate a commercially available gardening planning/planting/plant info piece of software. I bought several and each time I opened them and tried working with them I got frustrated. All I could think was "This is SO lame...if I had written this, I would have made it so and so...(easier to use/have better or more complete info/ give the user more options to change field or add field names/ and make it simple to add photos)."
I'd been working with databases in my old job for 20 years...(Yes, Stacy...that means I go back to the age of D=dinosaurs, DOS and Dbase! LMBO). I didn't really *want* to sit back down at my desk for the all the hours I knew it would take to write the program but I kept thinking how much *easier* it would be on me if I had the ability to search records and filter them according to my current curiosity.
Finally, last winter - after spending a LOT more money than I care to admit, I sat down, wrapped my head around the project and developed a user interface that fit *my* needs and *my* level of gardening expertise. In other words, I built my own *house*. hehehe
With that said, I have to give credit where credit is due. NO house is built entirely without building upon the efforts or *knowledge* of others. My house was built on the work of Tom Clothier and his "Germination Tables". http://tomclothier.hort.net/
Before I began thinking in terms of developing my own database system, I used Tom's gracious offer to download his database. (You can find that resource here: http://tomclothier.hort.net/page48.html ) From there I put my brain to work, imported his info into a flat database table in MS Office's database 'Access'. (Note: 'Access' is a part of MS Office but usually isn't bundled in the Office package and has to be purchased seperately in most cases.)
It didn't take me long to realize that I had more information that I needed/wanted access to than Tom had included in his, basically, seed germination database. Little by little I added more 'stuff', made the user 'front end' friendlier and based more on the way *I* liked to use a database. I wanted pictures, web links, space to write my own notes, I wanted to know if...well...I wanted the most *complete* source of information I could come up with.
Results of the endeavors will be added a bit later.
~end of Chapter One~
~Beginning to write Chapter Two - The difference between a Spreadsheet and a Database ~
~The difference between a Spreadsheet and a Database~
A couple of people have asked what the difference is between a spreadsheet and a database.
The difference for me is that I *hate* working spreadsheets! I've seen a lot of people who use spreadsheets to keep their records but I've figured out how to make them behave the way *I* want them too. I can't tell you how many times I've tried to rearrange info in a spreadsheet (Using commands similar to those I'd use in a database) only to find that I'd completely and totally destroyed the structure and voided ALL connecting information. ("Aw GEEZ...I *did* it again!" All to often!)
Anyway, without going into the "technical" differences between the two, I'm just going to say that at the very beginning of my 'real world' job of keeping an enormous number of records documenting manufacturing projects (Vendors, purchase orders sent, rec'd, invoices processed and paid), and not having a corporate office to guide me, I had no choice but to learn to do *something* that made my 'one woman' office run the best it could. And still give corporate the information *they* needed.
A database was the only thing I about at the time. And besides, spreadsheets reminded me of numbers (accounting :-O), and numbers scared me to death! LOL So I never bothered to LEARN how they worked or why.
(C3-section one - The opening screen)
When I open my database, I have a 'switchboard' screen where I can choose which area of the database I want to work in. (I have a part of the database that alows me to enter an index of sorts for magazine articles, tv shows, and other info I collect). Most of the time, I go directly into my plant database main form. This is what I see:
This screen is the first record of my entire database...it's the one that lets me stay cool and collected and presents me with little more than the basic structure and a few hints of what info I want to enter or what I'll see when I get to the plant/seed I'm recording or looking up.
I haven't mentioned it yet, but there is a system of "Tabs" to the right of the plant photo...each tab holds a different type of infomation.
jsr notes = My own notes on what I consider "important" or "Must see"
General Info = All the specifics (zone hardiness, exposure, etc)
Prop-Seeds = Tom's Germination tables, mostly
Prop-Other = Other methods of propagation for THIS plant
Abbreviations = My code key for various fields
Suppliers = Web Links for vendors
Info Links = Web links for *really* good gardening info
TBA = To be announced (Just another way iof saying "Im not done yet" LOL
C3-S3 ~The General Info Tab~
Here's is the infomation available to me for this particular Achillea cultivar.
On this tab I find (mainly) the info I imported from Tom Clothier's database, with a few little extras of my own. (You'll also notice that I have left programing notes to myself. hehehe) Plus, there's a working link on this tab that takes me directly to TC's web site so I can get to his other articles and information quickly and easily.
way cool - Gave me some wonderful ideas! I love it. I can also tell it took alot of time. How long did it take you? Now, you mentioned that you downloaded Tom's db..but it wasn't like this? What you are showing us is your 'baby', correct?
Looks and brains! Do you have any idea how dangerous you are? LOL!!
You are so organized and well, you got me impressed! I'd be mighty embarrassed to show you my note-keeping; a binder with a bunch of scrawled info.
Anita, Thank you! I hope I *did* offer some good ideas with my database.
Now...to your question about basing my database on Tom Clothier's material.
If you download one of those databases on Clothier's site, you'll get *all* the seed starting info. (That's where the info you see in the pic in part C3-S4 post above came from.) I think he has two versions up there. One is 'comma delineated' and I *think* he has one that will import into the MS 'Works' software...but don't quote me on that. I used the "comma delineated" information and just imported it into a new Access database. At that point, all that my database had in it was a table with the information...and it would look very similar to your Excel spreadsheet.
Yep! What you're seeing is entirely *my* baby. I added the GUI (graphic user interface) because, for me, it was so much easier to "see" the information at a glance. And that "At a glance" characteristic is why I added the "Tab" system feature. I just hate being unable to quickly spot exactly what I'm looking for. At the same time, I don't like being inundated with more info than I need at the moment.
How long did it take me to design and build the application? You've got to kidding! LOL I'm STILL working on it! Seriously, It was up and running in a couple of weeks. But it only took me that long because I wasn't sure what I wanted to *do* with it in the beginning. Stacy will back me up on this, if a person wants a database built they'd better know *what* they want it to do before they start. LOL Otherwise, you'll end up with a lot of mistakes BUILT-IN that can't be easily undone later. (Just ask me HOW I know that! hehehe)
And there's *my* ARIANNE! LOL
You're so funny. "Brains and beauty" HA!! (Ok, so I added the 'beauty' part...I needed a 'lift' today.) And as for being 'dangerous'. LMBO I'm only dangerous to myself...just ask my DH. hehehe
For your information, ANY notes or scribbles that can be rummaged through sometime in the future (and made sense of) are far better than nothing at all. Geez...when I first started taking 'notes' on how long it took Morning glory seeds to germinate in the middle of June, I was using Post-IT notes...and sticking them to anything that stand still. (I'm still finding those notes in the bottoms of cell-pack trays. :-D
Sweetie...I'm not organized...I just like to pretend! LOL And looking inside my database gives a feeling that I *really* do have things under control (but *I* know better! ~evil grin here~)
Chapter 4 will probably come sometime tomorrow, when I have time to "think" again.
Today, I got more cookies baked and packed up, and made two batches of homemade Pizza dough (along with getting a gazillion phone calls, doing regular chores, and fending off the DH trying to get me to finish decorating the Christmas Tree. (I think he's tired of tripping over the boxes I've got strung out all over the place. hehehe)) Yep! Sure...I'm organized. If I were organized, I'd be having *someone ELSE* doing it all for me. :-D
Julie would you jump across the lake and do mine for me???? Pretty please...computer things just buffalo me. I write everything in a gardening notebook, but usually lose it and start another one. I have several floating around the house. I'm impressed...
Julie thats mighty impressive, with that information you could almost start your own gardening /plant business. Very nice! I'm not even as organized as keeping a 'paper' journel but I think I'll try this summer so my children and grandchildren can see what takes up so much of my time.
I will back Julie up (as requested) on knowing what you want before you build a database. Since all the tables have to relate to each other, and the forms and fields relate back to the tables, if you don't really think ahead you can end up with a lot of "undoing" to do.
Julie - I'll D-Mail you. I just have to get that database to play with. Fantastic job!!
momof2d...thank you. I've often given thought to doing just that. Having a business working with plants has never seemed like "work" to me. And if I did start a business, the very first thing I'd do is rewrite my database to include modules that would make my paperwork easier.
Stacy...LOL...I kinda thought you'd react that way. LOL And I knew you'd understand all the relational aspects of designing a database like this one. And as for the "undo-ing" (hehehehe) I *never* get around to doing that part! I just live with the extra queries, and reports and forms...LOL...to scared to delete them for fear I take some necessary links out at the same time. LMBO
Got your D-mail...will answer it now. Oh, and THANKS!!
Are you aware that you could program drop down menus in to that? Example would be for sources. Let's say I buy most Tall Pitchers from Sarracenia Northewest but sometimes I buy them from Dangerous Plants. I have seen software where once you add the name of a nursery, you can select if from an alphabetized drop down menu or add a new nursery the next time you add a plant. That's kind of a neat function. I don't know if you would be interested in something like that but it would be cool. Come to think of it, you can use drop down menus for anything from zones to type of plant. I've also seen where one can sort by plants based on their common name or the Latin name or plants beginning with the letter a or plants that flower white or plants that are on a wish list, etc.
I'm not computer literate by any means and can barely fill in the blanks at times so how do I know this... I paid somebody to create a program for me to use. She never finished. She went off and got married and to be quite blunt after looking at what you've created... she wasn't as good or as thorough anyway. Story of my life, I paid for something that is incomplete. I have her work saved to my desktop. If you would like, I could forward it to you and you could see if she came up with anything that might interest you. She did have a drag and drop feature in what she created for photos which was nice. Just D-Mail me your regular e-mail address and it's yours if you even want it.
HI Lauren...yes, I know that drop down menus can be programed into most fields. I didn't use them because I because I wanted the flexibility to enter the info as I wanted it...or even skip entering anything if that's what I chose to do or didn't have time.
The links for the suppliers was one of those "afterthoughts" placed on a tab-card just because I wanted to be able to *know* the links were easily available when I could think of the name of the company or find the danged link in my browser. hehehe
Hey Julie, it's been a few months since I even looked at what she did because... why bother... she never finished but, the fields that had a drop down menu didn't have to be filled if you didn't want to. It was one of those automatic type deals. If you bought say different plants from the same online nursery, you could enter one plant and the information for where you bought it and then by the time you moved on to adding the second plant, you could just click on the online nursery and it would prefill the space. The only time you had to add information was when you purchased from a new source. And, you could leave the field empty if you wanted to just skip adding anything. It was sort of neat because you didn't have to re-fill the name, address, phone number, fax number, url and all the other goodies if you didn't want to because after you did that once you never had to do it again whenever you bought a plant from there. I also liked that feature because I could store info on people who sent me gifts and such. It would have been nice to receive a plant and be able to go right into the file to click on their e-mail address and be able to hyperlink right then and there into my e-mail to send them a thank you. I don't know if she got to the hyperlink to websites or the projection into an e-mail client but she was working on it or had worked on it. I am really totally computer ignorant and all of this is waaaaaaaaaaaaaaay over my head.
I'm trying to think of anything else she did. Oh yes, she was working on allowing me to change the background field to a color or to a muted soft pastel image. I don't know if she ever got that far.
I'll go and get the application over to you. Heck, use anything you want for yourself because what's there is bought and paid for by me so I don't care. And, it is in Access!
Lauren, if the module hasn't been "locked" I can change all those things for you. Don't know for sure if I can put a live link in the drop downs or not...never tried it. And I'm not good at integrating my DBs into feeding other progs (like the email client). But I *can* tell you how to change your color set up if you'd like. (As I said...IF the module hasn't been 'locked')
I'm looking forward to seeing it! Thanks for the offer!
Uh...what do you mean "Way over your head?" tsk tsk tsk...I don't think so! LOL
Kelly, building up this database was the only way I had of staying sane last winter after I'd finished the Winter Seed Sowing. I'll tell you how bad it really was. DH gave me the entire set of Garden Gate back issues (9 volumes) for Christmas last year. Would you believe that I actually scanned in the indexes for each year and manually entered all but the last 3 into my database? How's that for being bored out of my mind? (I had to stop when I had my cataract surgeries and couldn't see the fine print anymore...so they still aren't completed.)
About you getting "organized". Are you *sure* you want to *do* that? LOL Organization is highly overrated. :-D
LOL,,,I'm thinking of just going to that,,,I'm using Microsoft stuff,,,haven't worked on it for a while now with Christmas coming,,,I prolly won't remember what I was doing and why when I get back to it,,,LOL
edited 'cause I still can't spell,,,let's just say My Fingers won't cooperate,,,LOL
Thanks for all your offers but quite frankly, I'm not computer literate enough to even understand what you offered me. I'm still trying to figure out what a stand alone module is. Does this say it all?
You geeks out there have fun. I think I'll just wait for a software application to come out that I can buy and don't have to fiddle with. I am just no good at this type of thing and am at the point in my life where there are too many remotes for my tv so I have to give them all to a kid and tell a kid to turn on Discovery or whatever it is I am interested in if I want to watch it. Technology passed me by I'm afraid.
Kelly, I'm not having trouble with my fingers and my typing...all my mistakes our being caused by the fact I *need* to cut these nails off! LOL
Lauren, I got the mail...I answered your D-mail...and I'm...uh...oooppps can't say *that* on DG. LOL I'll only say that if you paid for what I just saw, are you interested in a bridge? :-Z
I can tell from what I saw exactly what you were looking for in that application. I was really surprised to see how similar yours is to the one I built. The drag and drop feature for the pictures was one of the first things I put into my database. I just loved the idea of finding a picture on my hard drive or (don't tell anyone) borrowing one from the Plant Files until I could take my own and just dragging it to the DB. (A quick note here: Most the DG 'thefts' LOL have now been replaced by ones I've taken myself).
My DB is far from "complete"...I still want to add buttons I can push to get a listing of Winter Sown seeds (arranged by the dates they need to be started and well as alphbetical) or a hard copy of my suppliers names/addresses, etc.
As I've said before, I paid a LOT of money for commercial programs trying to get one I could use easily and that would hold the info I wanted to access. They ALL drove me crazy. But some of them did fill in the gaps for research UNTIL I found DG.
I've even thought of the possibility of selling my structure to some company who could market it. Obviously, that has been put on the back burner. LOL (I'm too busy to even *think* about dealing "people" at this point.)
I got it! The only thing that stands out for me is that it is a read only version. Anything I can do about that? Here's hoping what ever you suggest isn't too difficult or I'm in trouble.
The red background for whether a plant is in the collection or not is hard on the eyes but that was the only thing that I could find that seemed out of place. That is really an excellent application. You done good! Holy Moly great balls of fire. I have no idea how to use it though but I'm liking it.
Quoting: Here's hoping what ever you suggest isn't too difficult or I'm in trouble.
Ok...I suggest that you ask your husband to go into the properties of the file and uncheck the "read only" only. (And those colors were put in there because they were the only ones *I* noticed. hehehe
Thank you Sanna! I appreciate that. And I'm happy the TOm Clothier site is helpful to you.
Quoting:Ok...I suggest that you ask your husband to go into the properties of the file and uncheck the "read only" only.
My computer IQ is about 60 which is considerably lower than room temperature on a warm day ;) I'll have one of the kids go into the properties for me and fix me up. You know something Julie... one of these days I'm going to learn how to turn on my own tv and get it to the Discovery or HGTV all by myself! Don't be holding your breath or anything waiting for that day though.
Eq,,,LOL know how you feel,,,I used to be able to take something out of the box and just use it,,,now, I have to read the directions,,,LOL I remember as a kid thinking Jeez,,,adults are so stupid,,,LOL,,,now I Know I am. I guess the above average results of the last IQ test I took didn't include gadgets of the technological kind,,,ROFLOL
So, you feel my pain? Ha ha ha, woe is me. I used to be embarrassed. Now I don't care. Almost everything seems to come with an owners manual these days. I have a brand new digital camera right now that I can't use that is supposed to be able to take micro and macro shots and I keep meaning to find the owners manual that I took out of the box to read so I could use the camera. What an idiot I am. I'm sure I'll find the manual sooner or later but until then... my brand new camera is sitting in the box. That's the pits.
You know what's funny, the only thing that doesn't come with an owners manual that really should come with one is a kid. They come with titles (birth certificates) just like a car does but no owner's manuals. You'd think they'd tuck a little "How To" manual in the diaper or something but noooooooo, they leave us to our own devices.
LOL,,,yes, I feel your pain,,,I have a pretty snappy Amateur Professional Digital Camera,,,I Still haven't figured out how to do all the things it's supposed to do,,,haven't taken time to sit and read through the book,,,LOL I just use the Auto setting,,,it must have at least12 more settings and a whole lot of other snappy *things*,,,oh well, one of these days
I spent a good portion of this weekend working on my database. Here is one form that I have completed. I will post pictures of some of the other's that I have created as well. I still have to work on an information form. I was having problems getting the tab feature to work...
Anita, you might have better luck if you 'built' the tab unit first then added your entry form to the tabs. One thing I remember giving me fits was that my forms and all the various fields had to be placed *inside* the outer boundaries of the tabs section. I finally got the place where I cramped everything on the tab body, then checked it forms view to see if they all fit. LOL
Trust me, unless you do this everyday (which I *don't* any more) it can be very intimidating. Hang in there...(Stacey can give you some help. hehehe Gotcha now, Stacey. You are the designated "Designer!")
Anita...those look really good. And I like the idea of the worksheet as well. I did the same kind of things with my work DB for equipment job logs and such. And boy did it make a difference in being able to keep track of the things going on a one time in our facility.
Anita - I'd be happy to help! Which version of Access are you using?
There are a couple of things you might want to think about... You might want to have a separate table each for the mast plant database and the plants in your garden. Limit the field properties of the garden list to the name column in the master. Make sure to choose the "limit to list" property as "yes" and expand as "yes" or if you're like me, you'll put in a typo at some point, and end up with a messy plant list.
Go to the relationships set up, and set up a relationship between the two name fields, and select "enforce referential integrity".
Now you can use linked forms, child fields, and sub-forms.
Instead of trying to use the tabs in the forms, which as Julie said, can be a pain in the patooty, try using hot button to sub forms. Create the two forms separately, ie - one with your garden info, and one with the plant database information. Create a button on your garden form to find the plant information, and let the wizard walk you through. The action you want is open form, then "find a record", and link the forms by plant names. Save your form, and test it. Say you're on a delphinium...it should open up the plant database info and go to the record on the same plant name.
For worksheets, create a form with the fields you want, and in the form properies go to the format tab, and then down to views allowed...choose datasheet, and the form will pop up as a worksheet. You can hot link this via a button as well, or add it to your switchboard.
cool - excellent ideas. I plan on playing with this alot more. I have an old version of access. I just purchased the upgrade to '03 and am waiting for the disc so I can load it. The program is so useful. I also use it to track my Christmas list as well [sent/rec'd, etc].
I can't seem to find 'switchboard' in the version that I have as I created what I have already from scratch. I will let you know if I still have a problem once I get the latest and greatest version.
You know what bothers me about your post? I understood every word of it. Where the heck were you when I needed someone to help me out years ago? (Nevermind...you probably weren't *born* yet! hehehe)
Anita - if you're in Access 97, go to Tools, Add Ins, and then to Switchboard. I use 97 and 2003, believe it or not. Our operating system at work doesn't like the newer versions, so I have databases in both, depending on what they're for.
I've been puttering around building one of my own, but I haven't been able to work on it very much. I had two that I had to build for work, and I've olny finished one so far. I'll be able to spend a little more time building the garden one in a few weeks.
I've been reading this thread with interest as I've been wanting to get all my garden info in one program for a while. I knew a database program was the answer but I don't have Access with my MS Office and I'd never used a database before...so I've procrastinated.
I've been using a spreadsheet to keep a record of the plants in my garden. When they bloom, where I bought them, etc. Then I started one that I call my garden design assistant, where I list things like height, and foliage colour, time of bloom etc, then I used to sort it if I was looking for short plants, etc. But, as you say Julie, sometimes it would sort one column, not the line as a whole. I found this frustrating, plus the fact that there were so many columns across it was a bit cumbersome.
So, as I said I've been reading this thread and I was considering purchasing Access because of all your wonderful information and a ready-made template to copy, but the cost was holding me up ($300 CDN)! Then EQuil posted those links above. So, I've decided to get the IdeasGenie to keep all my gardening info in one place. It's in the mail!
Thank you EQ for the link! Thanks to all you other awesome computer savvies too for giving me the kick in the butt to learn something new. I like the idea of having a picture and all the info together.
Sandy - before you give up on Excel, you can create a column for pictures, and put in hyperlinks to the photos in My Documents, or whatever. Creating a hyperlink is pretty easy. What I like about excel is the ability to filter. I have a spreadsheet, too, and I have colums for cole, height, bloom time, bed, etc. You can use the auto filter function to narrow things down pretty easily. Not as fancy-schancy as Access, but it gets the job done for free.
Stacy - I didn't know you could put links to the photos in Excel. I'll have to try that. No, I won't give up on Excel, I'll still use it to keep certain lists.
In your opinion, Stacy, what is the biggest advantage of Access over Excel? Am I right in saying the info can be sorted on more levels at once? (Eg show me all the blue flowers that bloom in June or July with a height of less than12") Anything else? What would you say is the biggest disadvantage of Access over Excel? I'm guessing the lengthy set up and learning curve?
Quoting: What would you say is the biggest disadvantage of Access over Excel? I'm guessing the lengthy set up and learning curve?
(Sandy, I'm not Stacey...LOL...but I'm going to jump in on that one anyway.)
Disadvantage...The price! LOL Access isn't included in the Office bundle...and yes the learning curve is a little difficult if you're not used to working with a relational database.
And YES! Access *could* most certainly do the sort/filters you're talking about above. It's a very simple process to "Filter by Field". For your search you'd click inside a "color" field...which would filter all the records to leave only the 'Blue'...then you'd scroll through a few records until you found the month you wanted "June" select that field and once again, "Filter by Field" and VOILA' a list that only contains those two criteria.
Of course there are other ways to do it...and there *is* always the fact that the info/data has to be correctly entered for the filter/s to work properly. But that goes with the territory. You can only get "out" of the program, what you are willing to put "in". :-)
Okay...I'm trying to teach myself how to use Access, but I have run into a problem. What is and SQL Server Database? Everytime I try to start a project it asks which server I want to use, or tells me I need to connect to one.
I'll back up what Julie said. Think of Access as a lot more powerful version of excel. You can manage multiple spreadsheet ("tables" in Access) and it knows how they relate to each other. Access will let you easily make a form to manage your info, where in excel you're looking at the raw data. If you're keeping one big list with a lot of information, Excel is a decent but not "pretty" way to do it. Access lets you automate it and pretty it up.
Jody - What are you in when it's asking you to connect to an SQL? You should be able to open a blank database, give it a name, and import your tables just fine. Microsoft SQL server is a tool that can be used to create databases directly on the operating system as opposed to using a tool like Access. It would be something that a company might use to set up a client-server database or to be able to host a complex web application (ie internet shopping site, etc). If you still have problems d-mail me.
It happens to me when I click Project (New Data) when starting Access.
Julie or Stacy,,,Want to ask ,,,
Have any book recommendations for learning how to use Access better? Looked at both, The Complete Idiots Guide to Access and Microsoft Access 2002 for Dummies,,,neither got good reviews on Amazon.
About the best one I've found for beginners is Access 2003 All-in-One Desk Reference for Dummies. The regular "for dummies" book doesn't go into enough detail. This one misses a couple of things, but it covers enough to get a decent database off the ground. I also like Peter Norton's guides. I still use my old Peter Norton guide to Access 97 Programming. Most if it still applies to the new version.
When you click Project and new data, it sounds like it's looking for a MS Project file, not that you want to start a new project. I'm on my home pc, so I can't check. Access 2003 is on my work laptop - this computer has 97. After I havea cup of coffee (I just woke up) I'll check.
That's what I kinda got when looking through the reviews in Amazon,,,the 2003 Desk Reference had fairly good recommendations. Will have to look for Peter Norton guide to Access 97 Programming.
I thought that may be the thing,,,when I start a new database,,,I have never selected the project option,,,this is the first time I have done that,,,now I know not to.,,,I Learn something new everyday,,,LOL
Okay...I was clicking on "Project using new data." I thought I had tried "Blank Database" once, but I must not have. I was able to download the info from Tom's site; now I'm trying to figure out how to get all the fancy little tabs, etc.
Ok - Here's what I have so far. I started the plant list with the download the julie linked to above, removed fields I don't need, and added some that I want. That's the Master Plants List. I added a number key so that I have something easy to link to for the other table. I'm on my old home pc, so this is Access 97, but there would be almost no difference at this point with the newer version
My Plants is the list of what I actually have. The relationship is set up to enforce referential integrity to the main list. If I try to enter something in my garden list that isn't a real plant, it will error out, and I will add a link back to the main list to search and add a plant if it really need to be.
The sources table is my catalogs, trade sources, local nursuries, etc.
I haven't started on forms yet.
Someone above mentioned wanting a drop down box... I'll put the how-to for thay in another post.
Skip thinks I'm insane for playing with a database at 8:30 on Sunday morning. It's good to keep him wondering what I'm up to.
Screen shots of table design and relationships are attached...
Ok - To make a drop down box that ties directly to a specific field in a table is pretty easy. There are three different options for this, depending on what you want to accomplish.
Go to your table in design mode, an select your field. At the bottom you'll have some specific property options. Click on the lookup tab. Display control defaults to "text box". Change it to "combo box". If all you want it to be able to select all the items in that field (like click to pick a plant), you're done.
If you want to limit the selection to what's in the list (ie - make it harder to pick a plant that isn't in the list) change Limit To List to yes.
If you have a field, like I do for Acid, where you want to keep your entries to a few options (ie Acid, Normal, and Alkyline), do this...Below where you changed it to "combo box" is a field called Row Source Type. It defaults to Table/Query. Change it to Value List. In the field Row Source put in the entries you want to allow like this, with semi-colons as separators.
Set Limit to List to Yes.
Another option... if you want to limit the entries allowed for one field to a field in another table, you can also do this here via the combo box.. Like I want to limit the "source" field in My Plants to sources that are in my source table.
Change Display control to Combo box, leave row source type as table/query, and then
in Row Source click on the build (...) button next to the drop down. A window will pop up with your tables, and pick the one with the info you want. Double click on the field you want to tie to, and save. Change your limit to list to yes, and save your table.
I've been working on a form based on a query - but am running into road blocks.
I created a query that asks for my seed Id [I want to use this when I am updating the database with container info.] My form so far works nicely. Upon opening it it asks for the seed id and will open with the plant from my list. The container field is linked with the container table - once I enter a number in that field it automatically opens my container form. I have the container form rigged to close window after entry.
My problem is getting the container form to automatically open as a new record entry! Right now it defaults to the 1st record. Any suggestions?
I'm very impressed with the programming skills and knowledge shown on this thread! When you have it all ready to go and for sale, let me know! Untill then, I guess I just have to stick with my wireless notebook...
ROTFLMBO!! I LOVE this! Stacey you are doing a wonderful job. I may even try that drop down option myself...now, could you help me with remembering how to digitize a scroll-y satin stitch for my embroidery? hehehe
I started a DB and what made me start over was the stupid ID,,,thingie,,,it's on auto number and so it won't go Alphabetically,,, when I add a new hosta,,if I put it alphabetically, it puts like # 145 inbetween # 37 and #38,,,
I guess I will just have to keep going that way then,,,got a bunch done a long time ago and stopped, 'cause I am one of those Anal people that the number has to coincide with the alphabetical order of the plant names,,,LOL Like I can't have another plant added in alphabetical order with numbers way out of whack,,,LOL
But, I guess I just have to get over it,,,when I get new ones, the numbers will be out of order,,,grrrrr, LOL
Kelly, you don't have to worry at all about the numbers and the alphabetical order (You *could* just ignor the numbers or have them hidden from your view...hehehe).
Seriously, there's no way that you will ever get plants entered into your database with that kind of precision until you are no longer gardening and don't need to add a new variety.
(Sheesh! And I thought *I* was bad!) j/k
When I first set my DB up I wanted to try to keep things nice and neat too. But I kept getting gift Daylilies and my DH would bring me a new perennial plant while I was having eye surgery. Well, that's the cool thing about DB's...you can always filter and sort and add new things and still be able to find them later.
Thanks Julie - I'm having fun! As I walk everyone through theirs, I've started on my own. The challenge will be today, when I'll be working from home most of the day. It will be tough to stay on the computer and out of the garden database.
Kelly - You can use the auto number, just make it the second field in your table (In design mode just drag the field down a slot). It should auto sort by name. I like having a number, especially when there are a lot of similar varieties. The books should help a lot.
Kelly - do this... save your table, and go into design mode. Selete the auto number field, and then insert a line UNDER the plant name field. Then set it as auto number format and primary key. That should take care of it.
Wanna come over and play Access with me????? huh,,,do ya,,huh?????? Worked great, thanks,,,LOL If I would have patience to read the tutorial I'm sure I could get farther,,,I do better with books though,,,c'mon Amazon,,,LOL
ViolaAnn just e-mailed me 2 booklets? for lack of a better work about Access and how to use it. I'm printing them out now,,,can't wait to start reading them,,,pretty soon I'll be having one heck of a DB going,,,yeehaaa THANKS ANN
Yes,,,my username could have also been ComputerGeek_WI,,at least it's got Geek in it,,,I figure that covers a lot of stuff,,,lol ,,I have been addicted to computers since I got my first one 10 years ago. The funny part is,,,I didn't want one,,,Bill talked me into it,,,now he threatens to throw it into the Mill Pond,,,says I spend Way too much time on it. I'm mostly self taught,,not that I know really that much,,,but enough to get the things done that I want to,,,. I have this obsessive nature and now that I'm working on a DB,,,I won't let it go til it's the way I want it,,,lol,,, would Love to get more *into* it. Eventually I will. Bill asked me the other night why I don't go to school for puter stuff since that's what I like to do. Asked him how he thought the bills were going to get paid,,,lol
I hear you Kelly - I love my PC too. I am also self taught - or paid close attention when I was at work. Sometimes it's more gratifying to have figured something out for myself rather than having someone show me..
ohhhh, don't I know that,,,lol,,,I am *playing* All the time,,,love to get into a new program and figure it out. When I got my first puter,,,I bought the Complete Idiots Guide to Windows 95 and I sat with that book in front of the puter for weekends on end. Had only used a puter 2 times before that and both times it was me typing and the owner of the puter doing everythig else. I'm glad I pick up on stuff pretty quickly.
I have a question about switchboards. I created a second switchboard page for links to all my reports. I also created buttons that link the default and the new. As far as I can see - I did it properly, but the buttons don't seem to work? On the default switchboard all links work except for the link to the second page.
Hmmm - got me there. First I'd say to try deleting the switchboard with the links that aren't working, and then re-do. Make sure that you select the items from the drop list instead of typing them in. Sounds like there's a bad link, but without looking at it I can't say for sure.
As soon as I finish these monster books that Stacy told me about,,,I'll *git r done* until then, I'm just entering information right now. That's what I'm supposed to do first right? Enter all the info on each hosta in that DB,,,then get into the fancy stuff?
yup - set your tables up. Figure out what info that table should have. I have a table for my plants, a table for the seed companies, a table for my containers, a table for the Tom C table and then a table for the wintersowing info that I got from another website. If you are interested in that one, here's a link to my webpage where you can find the download link for it. http://www.lakehousecreations.com/winter_sowing.htm
Thanks,,,so far I just have my Hosta Table,,,which is pretty inclusive,,,down to who introduced it and what year,,,I Know,,,ANAL,,,but I have lots of info to get in there still,,,will take a Loooooong time,,LOL Thanks for the link
I've been working on this on and off through the winter. Now I have spring fever and have been feeling cooped up, so I've been working on it a lot over the last week. I'm on a new pc, and I'm having touble with the security levels not allowing me to post pics. I'll put on some screen shots when I have the problem sorted out
lol al - that's "delete" for plants that won't grow here. I started the database with a list that someone else made. I've had to remove a lot of plants, and I have a ton to add. It started ut with not a single pulmonaria, hosta, tulip, daffodil, etc. I'm working on going through my old order reciepts and plant notebooks to get it built.
Wow, nice job Stacy. See, not only is this dog a little too long in the tooth to even get a inkling of what you're actually doing to set this great program up, but I'm beginning to forget what is what and where it's at in the actual location. Thankfully I can still turn a compost pile, and know the difference between a weed and what's supposed to be there, and most importantly bend down and remove the offender and add it to the aformentioned pile. These "new tricks" I guess I will leave to you pups. I do still recognize a job well done, and consider yourself patted on the head. Ken
Stacy, I think Al wanted to know what the plant was that appeared in the photograph in the first photo. It looks to me to be Baptisia australis. That should do just fine by him in full sun. Nice deep roots too so once it is established, it should be drought resistant.
Thanks Lauren and Ken! I've been having a blast getting this put together!
I'm not necessarily a "pup" lol. I'm good on computers and disorganized with paper, so this is intended to keep my litter piles to a lower tonage.
Lauren - I thought he meant my bomb button on the Master. Yes, the plant photo on the main menu is Baptisia. It gets more shade here than it should like, but it does great. The other screens are a mix of foliage from jap. painted fern, pulmonaria and galium; rudbeckia; sweet violets; and peony. I went through my files last night and got 2004 and 2005 plant additions input. Unfortunately, I only got smart enough to save those things in mid '04, so the rest of the lists will have to come from walking around and hoping I remember correctly.
LOLOL! I can't tell you how many things I've bought/planted/seeded in that I didn't write down anywhere. After the reciepts, this is going to be interesting! If you and April come for an ID/Idea visit, I promise I'll write things down!
lol Lauren.. I'll do the writing - you and April can be off the hook :) Klehm's is pretty close to me, and so is Anderson Gardens. Both are pretty cool.
Kelly - the pretty pictures make it look a lot more done than it is. I have the front end stuff done, but I still have a lot of plants to put in and there's some functions that I need to code in still.
It's so time consuming - isn't it? I love it though. I just get frustrated with myself, when I decide that something isn't organized right or I want to change the table around...and it's just not working. But..I love a good challenge.
Stacy!!! It looks WONDERFUL! You've got all the most important information tucked in so it's available when and where you need it.
To y'all who are having a hard time getting your data entered...I can only laugh. I remember last winter when I decided it would be a good idea to manually enter 10 years of Garden Gate Index pages (the FINE print kind they put in the year's collection) into my database. Not a bad goal for someone who had just had cataract surgery, eh? LOL I got just about HALF of it done and couldn't take another line of print I had to use a magnifying glass to read. :-D
It's hard to force yourself to keep stuff entered and up to date, but my-O-my, it's worth every second of the effort.
Thanks Julie- good to see you come out of the basement :) I've been adding a few plants every morning, so it's not bad. It may take a while, but I won't get burn out.
Work this week has been all consuming, so I haven't had much time to play around with my own computer stuff. In the play time I got, I built reports to use as a task list and one that gives me a plant inventory by bed, but I haven't fancied them up yet. I'm hoping to get this as close to functional as I can before gardening takes over all my spare time. When the plants are up and going I can use the inventory list to double check that I have all my plants loaded. For now I'm going back through my plant pictures and using those as a double check against the database.
Haven't had any time to work on entering info into the DB at all for a couple of months,,,have to get back at it now that my bookwork is finished.
Life is insane here right now. Will try and check in after the weekend. Am going to International Falls, MN for my nieces Ice Show. She started skating at 5 and is now 15. Her skating club puts on a show at the end of every season.
Have a good weekend all. See you on Mon.
I have a simple flat Excel spreadsheet I use to type in the bloom start and end dates for each plant in my garden. It then links to a color coded chart that shows the succession of bloom times. If anyone would like a copy I'd be glad to share. I was not the originator, someone from another website is. I think she is now on DG and she annually does an update to share around so I know she won't mind. Its a simple tool to keep track of where you put each plant, bloom times, color. I often make notes in an adjacent cell that doesn't show up on the chart about special treatments, etc.
Don't get me wrong - if I was capable of putting together a multifaceted database I'd be right on it!!!!
I know its possible in Access, or maybe even the pivot table feature in Excel but I've never been able to sit down to plan such a thing. Julie's darn right about the fact that you need to think about what you want before you start designing. I used a commercial database called Commence at work to create our customer service and all customer records storage a number of years ago and succeeded in making at least one person in each dept unhappy as their particular want or need wasn't on top of the list.
Andrea - your spreadhseet sounds like something I used for years. I used an "if" formula and conditional formating to set the color of the cell to appromixate the plant color. It was handy to be able to use the filters to see what blooms when and in what color. i've been trying to figure out a good way to get color into my database reports, but I don't think I want to mess that much with code.
I got the reports I wanted built, and now I'm trying to build a "wish list" for plants that I don't have and tie them to specific beds. The problems now are that I want to be able to just check a box and have it add to the bed when I get the plant, instead of going through a three step process, and that a lot of the plants that I want to put on the wish list aren't in the master database yet. I need to make an easy - add to master. It was nice this weekend, so I didn't work on it much (and I got a new computer game that took up more time that I should have spent playing).
All Greek to me,,,LOL haven't had time to sit and start reading my books yet. But will at some point. Prolly once I get outside and the plants are up, I'll get excited about doing the DB and will work on it in the evenings.
I'm working on a database now to catalog my thousands of embroidery designs. I have a huge collection and it really tickcs me off when I can't find a single thing I want to use. SOoooo...I go buy some more. hehehe (Hubby is about to take away my plastic. :-D)
Nothing yet,,,hopefully this weekend I'll be able to get out and get some scrounged up. Haven't made Any progress on the Data Base though. Maybe I'll send all my info to Stacy and she can git r done,,,LOL
I have access, but have never used it. I'd like to, but can't figure out how to start, my sample databases won't come up when I click. should I search the web and try to find a sample to start me out? Or is there something I'm missing?