Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2010, 07:05 AM   #1
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 99
Thanks: 1
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
How to design a plant database

Hello,
I'm currently creating a database in which I can enter information about plants and track not only species information but also planting requirements & growing conditions, bloom colors, pollinator information, and bloom time of year.

My question is this: the most important type of report I want to get out of this DB revolves around bloom time. I want to be able to show a month-to-month timeline showing which flowers/plants/trees will be in bloom; almost like a horizontal bar graph, a large list showing all flowers in db, & when the start and end blooming.

I started by just having a check box for each month, but I then realized I don't know how to tabulate a report to get the flowers to be organized in ascending order by bloom start period, rather than in ABC order. Now, instead of 12 check boxes, I have a 'bloom start' text box and a 'bloom end' text box. I don't need the date picker because I really just need month, I don't need more details than that. However, I'm still having the same report issue of getting them in ascending order according to month.

Any suggestions?

hockey8837 is offline   Reply With Quote
Old 01-11-2010, 09:08 AM   #2
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,351
Thanks: 41
Thanked 47 Times in 43 Posts
Steve R. will become famous soon enough
Re: How to design a plant database

In a sense you are not really using date values. What I would suggest, as part, of your approach a table with two fields one an integer value and the other the name of the month.

You will use this table as the record source for a list box of combo box for entering your month. The combo box (list box) will display the month but the value stored in your main table will be the integer. You can then sort the "months" in your main table by the integer value.

A minor issue to consider. What happens if your plant (however improbable) starts to bloom in November (11) and ends its blooming period in February (2)? I would suspect that most plants would bloom lets say in March (3) and end by June (6) which would allow you to easily figure out the blooming window.
__________________
No matter how fast the computer is, it is still too slow.
Steve R. is online now   Reply With Quote
Old 01-12-2010, 05:04 AM   #3
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 99
Thanks: 1
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: How to design a plant database

Hello,
Thanks for the advice. That is kind of what I'd started developing, I just was not sure whether that was the best way to go about this or whether it was round-about.
Your point about plants in bloom from fall through the calendar year is a good one; we do plant items that are grown for their 'winter interest,' though this may not necessarily mean it's in bloom, it may still be something we want to track. I guess there is probably no way to tell the DB to go back around again, unless I were to assign additional months that are only applicable for plants that continue to have interest through the winter; i.e. January (1), February (2), March (3)...November (11), December (12), January-A (13), February-A (14), March-A (15).

I don't know if there is another way to do this that would be better. Thanks for the input!

hockey8837 is offline   Reply With Quote
Old 01-12-2010, 05:58 AM   #4
Access_guy49
Registered User
 
Join Date: Sep 2007
Location: London, Ontario
Posts: 463
Thanks: 3
Thanked 11 Times in 11 Posts
Access_guy49 is on a distinguished road
Re: How to design a plant database

before you dump too much more time into this, you may want to look at BG-Base.
It is a pre-established software industry known and used in some of the largest Botanical Gardens in the world. (I used it for my thesis).

Otherwise it looks like your going to have a problem when you get into plants that bloom for short periods or mid way through months. personally I would go with a "Bloom start" and "Bloom end" date fields. As you monitor bloom dates you can get much more specific and after years of data collection may be able to see shifts in bloom times over some species.

The problem with using integers in my opinion is that you loose out to some degree on a valuable detailed data type. alot can happen with a plant over a 2 week period.
__________________
What do you mean 'what do i mean'?
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Access_guy49 is offline   Reply With Quote
Old 01-12-2010, 06:50 AM   #5
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,351
Thanks: 41
Thanked 47 Times in 43 Posts
Steve R. will become famous soon enough
Re: How to design a plant database

Quote:
Originally Posted by hockey8837 View Post
Your point about plants in bloom from fall through the calendar year is a good one; we do plant items that are grown for their 'winter interest,' though this may not necessarily mean it's in bloom, it may still be something we want to track. I guess there is probably no way to tell the DB to go back around again, ...
This can be resolved through the use of of the IIF function.

May (5) - March (3) = 2 (March, April, May which equals 3)

February (2) - November (11) = -9 (November, December, January, February which equals 4)

Code:
BloomPeriod=IFF(EndMonth - StartMonth +1>= 0, EndMonth-StartMonth+1, EndMonth-StartMonth + 13
__________________
No matter how fast the computer is, it is still too slow.
Steve R. is online now   Reply With Quote
Old 01-12-2010, 08:02 AM   #6
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 99
Thanks: 1
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: How to design a plant database- reports & calendars

Thanks for the suggestions. I'll check them out to see how it goes...But first...

I'm now trying to create a report which will show me a cross-section/bar graph type calendar displaying all species. I need the plant's bar to start in the associated month [Start Month] and end with [End Month].

I found this Reports Calendar Demo DB online somewhere (can't remember where, now), which I've attached. I'd like it to look something like the crew rotation schedule; I've actually gone in and added to my Months calendar which previously had 2 columns only for Month and Month # (like January, 1) and added a 3rd date "1/1/01", etc. for all months to help function with this report (year is inconsequential for me but that's what it is in the demo).

However, since I only have a species bloom start & end dates and nothing to compare it to as in this demo report (ship captain's start & end time on board vs the ship they're captain of), I'm kind of stuck again.

I'm having a hard time finding anything online about reports calendars in general. Any suggestions?
Attached Files
File Type: zip CalendarReports.zip (156.7 KB, 164 views)
hockey8837 is offline   Reply With Quote
Old 02-04-2010, 04:46 AM   #7
gardengal444
Newly Registered User
 
Join Date: Feb 2010
Location: Massachusetts USA
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
gardengal444 is on a distinguished road
Re: How to design a plant database

I have been working on a similar issue with tracking bloom periods. In the past i have found it difficult to successfully record start/end dates for a very large garden area. So instead I have a series of "observation dates" and "Bloom percentage". I'm sure this could work out with start/end dates too. I converted the date to a week of the month and produced this. Its in excel right now, but I am planning on getting it into Access somehow.


gardengal444 is offline   Reply With Quote
Old 02-04-2010, 04:56 AM   #8
DCrake
Administrator
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,644
Thanks: 8
Thanked 249 Times in 189 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: How to design a plant database

There is no reason why you cannot store the base information in Access and get it to fire the data out to Excel to provide the layout you have displayed above.

David
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 02-04-2010, 06:28 AM   #9
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,351
Thanks: 41
Thanked 47 Times in 43 Posts
Steve R. will become famous soon enough
Re: How to design a plant database

Quote:
Originally Posted by gardengal444 View Post
So instead I have a series of "observation dates" and "Bloom percentage".
Very innovative solution and excellent graphical depiction.
__________________
No matter how fast the computer is, it is still too slow.
Steve R. is online now   Reply With Quote
Old 02-04-2010, 06:33 AM   #10
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 99
Thanks: 1
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: How to design a plant database

Hi There,
I actually was able to solve this problem with the help of others on a different thread. The person who helped me (who I still can't thank enough) wrote a module for my DB which took the bloom start and end dates, and attributed a # to each month it bloomed; i.e. June-Aug bloom=6 7 8.

This way, I can run a parameter query for either 1 month (by entering the same # in both prompts) or any combination to see what will be blooming during this time.

I've attached my DB, feel free to use this as your starting point. I had to delete my photos from it in order for it to be small enough to attach. Your idea of % of bloom time is pretty innovative, though. This may be a great addition to my DB-though, the way mine functions now pretty much meets my needs for the most part. Yours may be more specific.

Enjoy!
Attached Files
File Type: zip flower database2.zip (127.4 KB, 314 views)
hockey8837 is offline   Reply With Quote
Old 02-04-2010, 07:08 AM   #11
gardengal444
Newly Registered User
 
Join Date: Feb 2010
Location: Massachusetts USA
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
gardengal444 is on a distinguished road
Re: How to design a plant database

thanks for the innovative complement. I wish I had known how it would end up when I actually started tracking the data in the garden throughout the season. In reality, I recorded 1,2,3,4 or 5 where 3 was peak bloom and 1,2 were winding up and 4 and 5 were winding down. When I could not come up with a way to represent that graphically, I converted my 1:5 values to estimated bloom percentages which yielded the above. In the past when I attempted to record start/end dates, I noted that the plants don't really respond well to that "bucket". (nor do I walking around a garden with 500-1000 plants!) A true "end" is not so definitive for some plants. Some bloom in a small way for a long time.

Thanks for sharing the flowers database. I will download it and explore it as I start my Access database. I am still in the planning stages. One thing I have seen so far in my design plan is that plants and gardens yield a ton of data. I am anxious to see what you have captured. My potential table list is growing every time I look out the window :-)
gardengal444 is offline   Reply With Quote
Old 02-04-2010, 07:16 AM   #12
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 99
Thanks: 1
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: How to design a plant database

Yes, I'm still playing around with what I want to track-because the possibilities are endless! For my purposes, at this point, bloom start and end will do. But, you're right, it may be useful down the road to have that wax and wane tracked, too. I've found that most of the data I've found about the plants is not specific enough to really be able to reliably track this across the board. Many sources simply list season for bloom periods, even rather than months. This is why I chose months over weeks for bloom calculations (for now), though weeks would be more accurate. I think I will go back in later and add areas which also track fall or winter 'interest', since, though some things may not have flowers on them, they're still attractive for other reasons.

Good luck! I'd be interested to see what you end up with down the road!
hockey8837 is offline   Reply With Quote
Old 02-04-2010, 07:44 AM   #13
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,351
Thanks: 41
Thanked 47 Times in 43 Posts
Steve R. will become famous soon enough
Re: How to design a plant database

Quote:
Originally Posted by gardengal444 View Post
One thing I have seen so far in my design plan is that plants and gardens yield a ton of data. I am anxious to see what you have captured. My potential table list is growing every time I look out the window :-)
The weather here on the east coast of the United States has been pretty chilly. We haven't got our heating bill yet, can't wait to open it! But, in terms of your garden and your database design - do you collect/record heating/cooling days? I assume that knowing whether the temperature is hotter or cooler than normal would have a predictive value for when plants will bloom.

A word of caution though, there is such a thing as collecting too much data.
__________________
No matter how fast the computer is, it is still too slow.
Steve R. is online now   Reply With Quote
Old 02-04-2010, 08:09 AM   #14
gardengal444
Newly Registered User
 
Join Date: Feb 2010
Location: Massachusetts USA
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
gardengal444 is on a distinguished road
Re: How to design a plant database

Steve, OMG! yes heating/cooling days does get into the too-much arena for me. However, having said that, I do usually mark in my journal the day I see my first flower (Iris reticulata) each March just to set the stage for the predictive value for the rest of the season!!
gardengal444 is offline   Reply With Quote
Old 02-04-2010, 01:05 PM   #15
gardengal444
Newly Registered User
 
Join Date: Feb 2010
Location: Massachusetts USA
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
gardengal444 is on a distinguished road
Re: How to design a plant database

this conversation prompted me to get back into my plant db design. I am an experienced software person, but a novice in DBs so I ask your help in steering me in the right direction.

I've attached a prelim list of my proposed tables and columns. I haven't addressed foreign keys yet but it basically shows the info I want to capture. The right side is instance data, the left is more like constant data (not sure of correct DB terminology). I think the table organization is pretty straightforward, except for the most important part.

I have not figured out for sure, but I suspect it is so, that each entry in the Plants table should be an instance of a particular plant in a particular location. So if I have the identical plant in two different garden beds, I will have two Plant entries for that row, distinguished by its location.

The part that bothers me is that the genus & species dictates the contents of many of the other fields so Plant P in Location L will have a lot of overlap with Plant PP in Location LL. And I know that's wrong.

Maybe the Plant table should just be a "generic" plant? and Cultivar and Location (among other things) differentiate the "instance".

I also get dizzy when I think about PurchaseInfo. I could easily acquire two plants at the same time and put one in one location and the other in another location. So in my first model that I proposed I would have to repeat the same PurchaseInfo for two plant entries.

I know this decision is important to a good working DB design, so I thought I might run it by the experts. I have to extract something out, but not quite clear best approach.
Attached Files
File Type: pdf DesignNotes.pdf (44.5 KB, 220 views)

gardengal444 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Levels of Abstraction and Database Design Banana Theory and practice of database design 48 07-19-2007 06:31 PM
record ids auto number problem. jason2885 Forms 4 07-03-2007 10:16 PM
Database Design Question ions Theory and practice of database design 4 09-27-2006 05:20 PM
Returning to Design View-Main Database DanBoyce Forms 1 08-27-2006 02:55 AM




All times are GMT -8. The time now is 10:07 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World