Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: Noobie Needs A Little Direction

    Hi everyone, I am trying to set up a database and I don't think I'm getting the hint. I have tried tons of things and many hours of work, but can't get what I want.

    I have tables like this for different many different years:
    "FieldID" "FieldName" "Acres" "NetUnits" "Unit/A" "Crop"
    1 Home 5 20 5 Wheat
    2 Backyard 8 24 3 Beans
    3 West 10 40 4 Corn

    Then I have a main listing of all the fields.

    "FieldID" "FieldName" "Owner"
    1 Home Johnny
    2 Backyard Mark
    3 West Jerry
    _____

    I want to be able to sum up all netunits for each crop from multiple years, then take the total acres for that crop from the multiple years divided by the netunits. But I need to add the netunits from each year..... How do I get a Criteria setup in a query to do this??? The FieldID is currently my primary key in all of my tables. I would also like to see a breakdown of all the fields and what the Unit/A was each year...

    Anyone that has time to help with this is a genuinely nice person. Thankyou.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Are you saying that you have separate tables for each year? If so then you need to re-think your table design. You should include the Year as a column in your table. That way you can very easily write a query on one table to sum up your data.

    Furthermore, you don't need to keep the FieldName in both tables. Just use the primary key from your Fields table in your other table (foreign key). You might want to do a web search on Normalization to help you get started.

    C

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    how will that work if for every year, every field has a different crop....?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't have time right now to detail this, I'm just heading out, but it would involve recording the year along with the crop and the field.

    I'll post back when I can

    Someone else will probably beat me to it tho
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the design is flawed
    If I were you I'd have a good look at the stuff on normalisation.... Rudy's site has a good one.

    fwiw.. to give you an idea
    you are mixing a field with the crop
    a field has information relevant to the field
    eg size, location

    the owner of the field exists independantly as well, an owner may have more than one fireld

    the crop is planted in the field
    a crop exisits separately from the field, it exists for as long as the crop is viable in that field (technically there is no reason why you saymay have multiple crops in one field, a crop could be temporay (eg planted in spring, harvested in Autumn, it could be semi permanent ie grass)).

    you may want to record stuff such as yields per acre, you may want to go into further detail (eg the crop in area x of field 1 was zzz per hectare, area y was !!! per hectare and so on)

    Id expect a table with the owners in
    I'd expect a table to identify waht croptypes are used

    you then need to tie everything together so that
    you know who owns waht field (use the primary key field form owner as a foreign key in field)

    that might be
    fieldid pk to field
    ownerid ' identifies who owns the field
    location 'identifies where the field is.....

    you know what crop was planted in what field

    that might be
    croptype 'identifies the croptype (eg wheat, barley whatever)
    fieldid 'fk to field.. identifies the field where this crop is planted
    dateplanted
    dateharvested
    yield

    but as others have already said you may need to rethink that if you need to do comparatives between the yield for each type of crop, or the yield over time in the same field.

    but bone up on normalisation before you design any tables
    Last edited by healdem; 04-14-08 at 08:43. Reason: the usual typos, screwed up URL's... where is firefox's spell checker when you really really need it because you are pishpoor at proofreading

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I knew someone would beat me to it

    I agree with healdem. I think it's the result of a design flaw.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2008
    Posts
    3
    Thanks a lot guys!!! I spent quite a few hours playing with pivot tables in a spreadsheet, and now I think I have a better idea how I need to set up my tables. I was way off on my original thought process. I'm going to tinker around with the ideas you gave me and see what happens.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Feel free to post your planned table structure here for comments and suggestions
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •