Farm records database

I'm trying to set up an Access 97 database for my farm records.  I want to record each time I fertilize, harvest, prune, irrigate, spray, etc. on any one of six different crops.  I need reports on activities for each crop per year, all activities per month, and all of a particular activity, say, all fertilizing in a certain period.  I can't figure out how to set up the tables.  The Northwind model doesn't seem to help because they aren't tracking a number of activities--just one (ordering), and the other tables stay unchanged and just fill in the blanks.  This must be a fairly simple job for Access, but I just haven't got the hang of how to figure it out.  Any suggestions?
SoutherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A_FollowerCommented:
Set up a table with one entry for each crop.  It will need the fields:

[Crop Name] (Plus a field any other crop-specific salient details, like field where it's planted, etc.)

[Crop ID]  (Auto Increment, Primary key)


Then make one table for each type of activity.  The fields in each will be identical:

CropID            Type Long
ActivityDate      Type Date

Relate each [CropID] field in each activity table to the CropID in the Crops table, with the CropID field as a '1', and the activities' ID field as the 'many' in a relationship.

Enforce referential integrity.

Each time you need to enter an activity you would add a record to the particular activities table with the ID field being equal to the ID of the crop where the activity was done.

The rest would be handled by using queries to restrict / include information.
0
JimMorganCommented:
Hi Souther:  Welcome to EE.

Having grown up on a farm and taken care of all the farm records for my Dad, I know just what you want.  The question is how to teach you enough about Access so that you can do it yourself.  I assume that you have purchased some books on Access.  Which ones did you buy?

I've personally found that Northwind had a lot to be desired.  I've looked at a few examples and agree that unless you are trying to do the same thing, there isn't much there to help.

Rather than try to follow Northwind, have you looked at the various templates that are provided with Access?  There may be some examples that can get you started.  Open a blank database and go to the Database tab and play with a few of the templates.  The best way to learn is through experience.

What you seem to be asking is not how to set up the tables.  It's more how do I layout the design of this database to do what I want?  Database design is more than just creating tables.

How are you doing it by hand?  If you have a system which works for you now, although it may not be computerized, then why not make the database model look like something that you are familiar with?

What other computer programs as you using?  Quicken or Quickbooks, Excel?

I ask this as it might help me form a simile for you.

Now that this has been said, I'd better give you some straight pointers because if I don't, other experts will use this as a starting point and go on from here.

I was suggest starting with a couple of databases which describe your crops and your activities.

Open Access and choose Blank Database and let Access create one for you.  It will ask you for a name and then create the blank database.  The first thing that you will see will be the tables tab with no entries.  Click New.  Then select Table Wizard.  This will show you in the first column some of the table templates that would be used if you had played around with some of the sample databases.  The second column contains the fields that might be used for each template.

The perfect one to start with would be 'Categories'.  You can use this to enter your six different crops.  The two fields are CategoryID and CategoryName.  Click the >> and the fields are put in the third column.  Highlight each field and click 'Rename Field'.  Rename the first field to CropID and the second to CropName.

Click next and it wants to know the name for this table.  Call it 'Crops'.  Let the wizard create the primary key and then next.  Select the third option which is enter data into the table through a form.  When you click 'Finish', up pops a premade form for you to enter your crop names.  (If you want to have more details, we can add those later.)

The first field is CropID.  This is an autonumber field, the numbers increment each time you add an entry.  Don't put anything in this field.  It is only for linking purposes.  (A method to pull the tables together for reports, etc.)

You can enter your crops now: Corn, Cotton, Oats, Soybeans, Alfalfa, and Sorghum.  (I'm from Arkansas and that is a typical rotating crop mix that we planted each year.)

When you are through, you are asked if you want to save your changes to the form.  Say yes and you have a table named Crops and a form named Crops.

How about farm fields?  Do you want to know which field the crop is planted in?  I'm sure that this will be important for crop rotations.  We'll make another database to link back to the fields.

Go through the same process of making a crop but change everything to FarmField.  We can put the 8 or 9 fields that you have in this table and form.  I used FarmField rather than just Field as field is a reserved word in Access.  There will be another question wanting to know how this table relates to Crops.  It doesn't yet so go on.

Use the categories again and created another table which we will call Tasks.  You may notice that there is another template called Tasks and we will use it later.  Do the same as you did with FarmField but use Task everywhere.

Now lets get on with the farm chores.  Back to the New table and son-of-a-gun, they have Tasks as a choice.  Bring all of those fields over and they are pretty good.  Change Tasks to Chores where ever it is found.

Don't go on yet as we need to know which crop the chore was done for. We need to track which crop, what field the work was done on, and which task was done - prepare soil, plow, plant, weed, fertilizer, etc.

Leave the fields where they are and go to categories again.  Select and move CategoryID to the fields and change its name to CropID.  Do this again but name it FarmFieldID.  And one more for TaskID.

Now you are ready to go on.  Now we get back to the area where it wants to know the relationships.  Click on 'Related to Crops' and then Relationships.  The suggested relationship - one crop to many tasks, is what we want.

Same thing applies to FarmFields and to Tasks.

Continue on and let it create a form.  You probably aren't ready to enter any data here and we do need to make a change to this form for our purposes.  We will want to make the three fields: CropID, FarmFieldID, and TaskID into combo boxes.

In the toolbar at the top left, there is a icon which looks like a Triangle and a T-square.  This is the Design View.  It will open the form in design view.  Do this and also find the Properties button - same toolbar, about 5 buttons from the right.  Click it as well.

Go to the CropID field and select it.  Go to the top menu bar and click Format|Change To|Combo Box and click.  On the properties box, there are several tabs.  Choice Data.  Click Row Source and you will see on the right side of the property line a triangle and an ellipse (...).  Click the ellipse and you will be in the query builder.  In Show Tables click Crops, Add, and Close.  The crops table will appear.  If the properies box is not visible, click the icon.  Change the properties such that 'Output all Fields' is Yes.  Click Close and the Yes.  You are back to the form.

Click the Format tab and Column Count to 2 and the Column Widths to 0;2 (or you could make the second number larger - it depends on the size of your data.)

Do the same task for FarmFieldID and TaskID.

The final steps are to change the labels of the combo boxes and make them wider.  To make wider, click the box and user Shift Right Arrow.  To change the labels, click the label goto the Format tab on properties and change the Caption to whatever you want.

Save and close your form.  When you want to enter data in your form, click on the Chores form under the database Forms tab and start entering.  You can choose which crop, task, etc. by either typing them in or using the drop-down and selecting the task.

This should get you started and it is running a little late for me in California (don't look at the time stamp - it was never changed to daylight savings time, I believe).

If you have any more questions, don't fail to ask.  And if you run into trouble with the DB, feel free to email it to me for review and suggestions.  My email address is in my profile.

Jim

 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AwmytieCommented:
(See, I told you so.)  Mr. Morgan seems to know his stuff!
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JimMorganCommented:
Awmytie:  Who did you tell so?

And thanks for the kudos.  I don't necessarily know enough but after spending all my youth on a working farm, I sure know my farming.  :-)

Jim
0
AwmytieCommented:
    Jim:  Souther originally had the Q. posted in a Computers - General forum; I suggested he repost here where there more likely would be real spreadsheet gurus.  He did, and there were, as in J.M.  My spreadsheet knowledge is next to nothing (I keep looking at Quattro and thinking I ought to be able to use if for something).  I was also curious to see what the results might be -- even an antique such as I occasionally can learn something.
     It's been a loooong time ago, but I was raised rural, too, northern Indiana.  Hand-shucked corn, pitched bales, milked Bossy, etc. -- before today's expensive machinery took over the chores.  But I've been urbanized so long I've forgotten most of it.
0
JimMorganCommented:
Awmytie: Well there are no 'spreadsheets' around here.  Just good old database gurus.

I've always had a heck of a time since the early days of Lotus 123 trying to help people differentiate between using a spreadsheet as a database and using a database as a spreadsheet.  By I won't go into that now.

You can't be much more ancient than I am.  Grew up on a rural farm in northeastern Arkansas.  It was such a tramatic part of my life that I don't think that I could ever forget it.

I knew from the beginning that I wanted to be an engineer but I was stuck on a farm.  And if I followed the traditions in the area, I was supposed to follow in my father's footsteps and take over the family business.  Last thing that I wanted.  So I started applying engineering to the farm.  Suggested to my Dad and Uncle that they ought to get some of that new irrigation equipment that they were using in California and try it out.  We have a lot of bayous around the farm and had plenty of ready water.

They started out with some simple things and I keep asking them to put in this crop or that crop as I thought that there would be a big market for it.  Slowly we migrated from cotton as the major cash crop to truck farming.

The big winner turned out to be the one that didn't want a lot of water - okra.  One summer I planted 3 acres of okra.  After the first week of trying to harvest 3 acres of okra each day (and I mean EACH day - 7 days a week - or the stuff goes to seed and quites producing) I plowed under 2 acres and concentrated on the one.  Made more money selling okra that summer than the whole farm made all year on everything else.  To make a long story short, the next year all the farmers planted okra and Birdseye came in, bought the farm, and turned it into a food processing plant.  My Dad stayed on a the plant foreman for years.  Since there was no family business to take over, I went on to become the engineer I always wanted to be.

Find a need and fill it.  The first time I ever worked my way out of a job.

Jim
0
AwmytieCommented:
    J.M.  Coincidences abound.  I'm a retired PE Electrical Engineer, plus a lot of electronics (two-way radio, audio systems, etc.).  And ancient, just past 76 (bet I got you beat there).
     Oh, the name is Billie; but I don't give out the rest since I like to poke those guys in the Lounge a bit, and some of them might bite back.
0
JimMorganCommented:
OK Billie:  I was a Ham myself.  We had the biggest ham radio antenna in the world.  Can you guess where that was?

As far as being ancient, depending on in which Southern state you went to get married, you could just be old enough to be my Dad.

BTW, just how bad is that tie?

Jim
0
TrygveCommented:
JimM: I see that this is an area that has special interest for you ;-)

One note though - "Database design is more than just creating tables." Yes it is, but making a good data modell is, even though this fact it is most of the time more or less ignored, the very most important task or step when making a system. Putting "enough" time into the date modell can save you hours and hours of bad programming and rewrites later on. For a smaller system it will not make a big difference whether the data modell is good or not so good, but those systems have a tendency to grwo...

0
AwmytieCommented:
    Married across the river from Memphis in Marion, Arkansas, 1944 (but never got to your part of Arkie).  What tie?
0
JimMorganCommented:
Awmytie:

PAPA!!!  I was born in Memphis (no hospitals in Marion) but lived in Marion until I ran away to join the Navy.

BTW, Marion is in the northeastern part of Arkansas.  You probably were stationed in Millington and got hitched after the war was over.

Jim
0
AwmytieCommented:
  Memphis AFB, WW-II, AACS detachment.  It's northeast?  I should'a checked a map.  Haven't been back there since 1944.  Want a DNA check?
0
JimMorganCommented:
How long were you stationed in Memphis?  Did you ever meet a young lady named Lorraine?  Loved to dance.

Maybe no DNA for me but I could swear my little brother who was born around that time had a different father.  You weren't short, wiry with blond hair at the time?

I believe that I misinterpreted you member name.  I read it is Aw My Tie and my wife, who isn't Southern, said "You should recognize that one.  It is Southern for All Mighty."  Is she right?

Jim
0
TrygveCommented:
Perhaps it is also a rewritten version of 'Almighty'...
0
JimMorganCommented:
Same thing.  Just different emphasis.  In the Southern part of the US, people have a tendency to swallow multiple words into one, like 'richair' for 'right here' or take a single word and make it sound like two words, like 'All Mighty' for 'Almighty' by emphasizing every syllable in the word.  Preachers and politicians are the best at that.  :-)
0
TrygveCommented:
Jim: I completely missed the last part of your last comment where you actually pointed out that it was Southern for All Mighty. I thought I had found another possible underlying meaning of underlying member name. Perhaps my subconsciouness had registered your comment and lead me to believe that I had figured this out by myself ? ;-)
0
AwmytieCommented:
    Don't remember any Lorraine (after high school, that is).  I was only at Memphis Army Air Force Base about 3 months before moving on to Pope Field, Fort Bragg, N.C.  Not too short, 5'10", brown hair (alas that was many years ago).
     {OK, so nobody would believe me when I said I was Ghaud.  So I just changed it to Awmytie.  Holy Jerusalem, what does it take to convince you mere humans?  Beam me up, Scotty.}
0
JimMorganCommented:
Trygve: You were right.  I have a tendency to read these member names literally and not read into them.  For example it took me a long time to realize that BeegSeester was Big Sister with a Spanish influence.  My wife knew right away.  I guess that is why she always beats me in our California Vanity License plate game.  In California you can buy a vanity plate which has whatever you want for a number as long as it isn't profanity or vulgar.  Any combination of letters or numbers up to 10.  I just don't get them but my wife, who is more artistic minded, understands them right away.  The state must have some retired engineer censoring the applications because some very creative names get through.

Awmytie:  It's amazing how pretentious people become as they become older.  For me it's embellishment.  :-)

Souther:  I hope you don't mind us using your question for idle chit-dhat.

Jim
0
TrygveCommented:
Jim: I guess this has something to do with right/left brain dominance ;-)
0
AwmytieCommented:
Did souther give up on us?
0
JimMorganCommented:
Probably.  The question has been answered and he's out in him 'farm' rotating crops.

Or all the email responses just scared him away.
0
SoutherAuthor Commented:
I'm still here, but what with rotating crops, stuffing myself with turkey, and writing that database, I haven't gotten around to putting my two cents in here.  

I put together a database along the lines of your suggestions, JimMorgan, and it was very easy to do and works just fine thanks to the great step by step.  I find I want some more detail, though--to be able to find out how much potassium sulfate I used in field (make that FarmField)4 in 1997, and so forth, so next I'm going to try something along the lines suggested by A Follower, where I have separate tables for the different tasks, and see how it works getting the info from queries.

Gotta milk the cow.
0
JimMorganCommented:
Souther:

Glad that it's working out.  Nothing like hands-on experience to help you learn.  Of course, unless you went to one of those ag colleges and worked on one of their school farms, you are finding out that you never experience and learn all there is about farming.  Not in your whole life.  Almost everyday there is something new!

I call them information tables.  They are tables which have one record for each different element of a subset - equipment, jobs, seeds, fertilizer, suppliers, workers etc.  These are static descriptions which are linked primarily by the information table and the primary key.  So in the daily events, I have a foreign key field for each of these information tables.  Entering just the primary key of the record that I want, allows me keep minimal information in my daily records but I can pull the other data in for reports or queries any time that I want.

It boils down to this:  Is this information fairly static?  It doesn't change that often.  Put it in a static table and refer to its key when we need to.  For example, a state table, will never change.  Once you get cities in a table, they seldom change.

In order to never lose the ancillary information which I need to make sense of this key value, I never delete a record in the information tables as long as there is one record somewhere else that depends on that info.  This is handled by referential integrity through relationship tables.  If an item is truly no longer used and I don't want to see it everytime I look at a list, I have another field called 'Inactive' (Yes/No) and an 'InactiveDate' to track if the data is no longer used but kept around for archival use.

Is the data likely to change ofter or have a substantial number of new entries enter on a regular basis?  This are my dynamic working tables.  They point to all of the information tables for data they need.

I also have a static/dynamic table.  Each customer has one record (somewhat static - name, address, etc.) which is the focal point for all my other tables and reports.  On the screen, I put the static information at the top and then switch in and out more dynamic subforms in the bottom half of the screen so I can see what is the current state of this customer including lists of recent activities.  It's designed to look like a set of flip cards on the screen with each card having its own set of attached flip cards to review.

Funny how the past comes back to you.  Here I am working on a farm database based on my youth experience and a couple days later, I work on a greyhound dog racing database, also a youth experience.  When we weren't farming we were raising dogs or running a racing kennel.

Jim
0
SoutherAuthor Commented:
Jim,

You're right about learning about farming.  My own special technique is to make every single mistake you can along the way and fix them one at a time.

I'll have to think for a while about what you've written above and see how it applies to my farm records database.

As far as dogs go, mine likes to race up and down the orchard when I first go out to work but then he loses interest and goes back to one of his favorite spots.  He's no greyhound.

Souther
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.