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.
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.
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
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 07: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
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.