Link to home
Start Free TrialLog in
Avatar of Souther
Souther

asked on

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?
Avatar of A_Follower
A_Follower

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.
ASKER CERTIFIED SOLUTION
Avatar of JimMorgan
JimMorgan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
(See, I told you so.)  Mr. Morgan seems to know his stuff!
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
    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.
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
    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.
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
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...

    Married across the river from Memphis in Marion, Arkansas, 1944 (but never got to your part of Arkie).  What tie?
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
  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?
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
Perhaps it is also a rewritten version of 'Almighty'...
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.  :-)
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 ? ;-)
    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.}
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
Jim: I guess this has something to do with right/left brain dominance ;-)
Did souther give up on us?
Probably.  The question has been answered and he's out in him 'farm' rotating crops.

Or all the email responses just scared him away.
Avatar of Souther

ASKER

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.
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
Avatar of Souther

ASKER

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