Table Structure

[hotrodsue]

I’m creating my second DB. The customer (dairy farmer) has crop fields harvested for feed. There are a variety of fields harvested, with varying acre size.

At harvest time, one field has up to 10 different trucks hauling 6 loads of feed per day. Each load per truck is weighed. All loads per truck are totaled. Truck Tare (empty) weight is subtracted to determine Actual Weight of Feed (Forage) harvested in pounds. The pounds are then converted to Net Tons.

The soil for each field (crop) is measured for moisture throughout one day. The total of all measurements are then averaged to determine the percentage of the Feed (Forage) that is Dry Matter. The Net Tons are multiplied by the percentage of Dry Matter to determine Dry Matter Tons.


My tables and queries are working up to this point; however, I hit a stumbling block. Customer wants a Crop Summary to show how many tons of feed is harvested per acre of field (or crop). In essence, I think I need to perform calculations on other calculated information and I’m not sure the best way to accomplish this.

Here’s what we need, there are four variables (in red) I don’t know how to achieve:

Date Harvested
Field (Crop) Name
Field (Crop) Size in Acres (Acreage varies and needs to be input as part of a calculation.)
Total Tons (Already Determined in Query)
Total Dry Matter Tons (Already Determined in Query)
Percentage of Dry Matter (Already Determined in Query)
Tons Per Acre (Need to Calculate: Sum of Net Tons for all Vehicles divided by Acres)
Dry Matter Tons Per Acre (Need to Calculate: Sum of Dry Tons for all Vehicles divided by Acres)
Comments (Text field to input comments)


Finally, some discussion in the “Queries Thread” brought me to evaluate attached table structure.

Just thought I’d throw out the information here for suggestions. Help is always so appreciated.

 

[jzwp22]

Based on the jpg file you posted, your tables are not normalized. This is evident by the repeating fields Dry1, Dry2... in the Dry table and Load1, Load2 etc. in the Harvest table.


Going back to your basic description, we can start to formulate an appropriate structure.

At harvest time, one field has up to 10 different trucks hauling 6 loads of feed per day. Each load per truck is weighed. All loads per truck are totaled. Truck Tare (empty) weight is subtracted to determine Actual Weight of Feed (Forage) harvested in pounds. The pounds are then converted to Net Tons.

The soil for each field (crop) is measured for moisture throughout one day. The total of all measurements are then averaged to determine the percentage of the Feed (Forage) that is Dry Matter. The Net Tons are multiplied by the percentage of Dry Matter to determine Dry Matter Tons.

First you will need a table to hold the fields that will be planted and their size

tblFields
-pkFieldID primary key, autonumber
-txtFieldName
-FieldSize (in acres)

You will need a table to hold the various crops that may be planted
tblCrops
-pkCropID primary key, autonumber
-txtCropName

Now, you need to assign the crop that is being grown to its respective field. I will assume that crops are rotated annually, so I think it would be best to have a year to indicate which field had which crop for each year.

tblFieldCrops
-pkFieldCropID primary key, autonumber
-fkFieldID foreign key to tblFields
-fkCropID foreign key to tblCrops
-plantingyear

Now, you need a table to hold the basic truck data including its tare weight

tblTrucks
-pkTruckID primary key, autonumber
-txtTruckNo
-trucktarewt (lbs)

Now join the trucks to the fields/crops they are working

tblTruckFieldCrops
-pkTruckFieldCropID primary key, autonumber
-fkFieldCropID foreign key to tblFieldCrops
-fkTruckID foreign key to tblTrucks
-totalweight (tare + crop in lbs)
-harvestdateandtime (date/time field)

tblSoilMeasurements
-pkSoilMeasureID primary key, autonumber
-fkFieldCropID foreign key to tblFieldCrops
-measurementdateandtime
-SoilMoistureAmount


The above structure many not be correct if there is a relationship between the harvest amount (trucks) on a particular day and the soil moisture measurements for the same day for the same crop/field? I am guessing that there is a relationship (you'll have to confirm); if so, I would restructure as follows:

tblHarvestDates
-pkHarvestDateID primary key, autonumber
-HarvestDate

tblHarvestDateFieldCrop
-pkHarvestDteFieldCropID primary key, autonumber
-fkHarvestDateID foreign key to tblHarvestDates
-fkFieldCropID foreign key to tblFieldCrops

tblSoilMeasurements
-pkSoilMeasureID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-MeasurementTime
-SoilMoistureAmount

tblTruckFieldCrops
-pkTruckFieldCropID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-fkTruckID foreign key to tblTrucks
-totalweight (tare + crop in lbs)
-Trucktime (time field)

 

[hotrodsue]

Wow, thank you for posting. Your detailed insights are very helpful. Since my original post, I’ve created queries to determine needed calculations. However, you’re correct that the table structure does not follow normalization guidelines. You’re also right; there is a relationship between the harvest amount and the soil measurements for the same day on the same crop/field.

The customer brought me his excel spreadsheet to show his calculations. Attached is current excel spreadsheet I’m reviewing, in the event that the information may help determine my DB table structure.

I’m going to study your notes a bit more; you’ve given me some good direction. There are a few areas I’m struggling with. Hope the information will be helpful in tackling this:

1) A Field (Crop) named “Putnal Big Field” is 100 acres in size. However, on a rare occasion they may plant only 75 acres on that “Putnal Big Field”. So the acreage on each Field that is named can vary.
2) The crops are rotated, however, more than once a year.
3) Tare Weight is based on the 3 most recent Tare Weight readings, averaged. The tare weight of a truck varies according to equipment configurations.
4) Net Pounds are determined by Average Tare Weight x the number of loads per day. So, I total the weight and I count the number of loads per vehicle.

Maybe I’m getting ahead of myself. Perhaps the calculations will all come together again for me, once I get that table structure. Again, many thanks for the time you’ve taken thus far, it’s very much appreciated.

 

[jzwp22]

The table structure needs to be fixed before we worry about queries and calculations, so I will focus only on the table structure at this point.

Based on your information below; the table structure I provided will need to be modified &/or expanded.

1) A Field (Crop) named “Putnal Big Field” is 100 acres in size. However, on a rare occasion they may plant only 75 acres on that “Putnal Big Field”. So the acreage on each Field that is named can vary.
2) The crops are rotated, however, more than once a year.
3) Tare Weight is based on the 3 most recent Tare Weight readings, averaged. The tare weight of a truck varies according to equipment configurations.
4) Net Pounds are determined by Average Tare Weight x the number of loads per day. So, I total the weight and I count the number of loads per vehicle.

Before we start modifying the structure let me ask some questions regarding the above items you listed.

For items #1 & #2
Can the planted area be occuppied by more than 1 crop at a time? For example, can the Putnal Big Field have corn (25 acres planted) and wheat (50 acres planted)? Could the planting times for the corn and wheat be different & thus the harvest times may also be different?

For item #3
Since a truck can have multiple tare weights that are time dependent, we will need a separate table to track the tare weights and the dates/times of when the weighings occurred (so we can get the 3 most recent)

Can the configuration of a truck be modified? i.e. multiple configurations per truck?

For item #4
This is just a calculation, once the table structure is set, it will be easier to see how to create the query/queries necessary to do the calculations.

 

[hotrodsue]

Before we start modifying the structure let me ask some questions regarding the above items you listed.

For items #1 & #2
Can the planted area be occuppied by more than 1 crop at a time? For example, can the Putnal Big Field have corn (25 acres planted) and wheat (50 acres planted)? Could the planting times for the corn and wheat be different & thus the harvest times may also be different?

For item #3
Since a truck can have multiple tare weights that are time dependent, we will need a separate table to track the tare weights and the dates/times of when the weighings occurred (so we can get the 3 most recent)

Can the configuration of a truck be modified? i.e. multiple configurations per truck?

For item #4
This is just a calculation, once the table structure is set, it will be easier to see how to create the query/queries necessary to do the calculations.

Thanks for your help with the table structure, which will be the foundation for a successful project. You’ve made a good point, once the table structure is set, it will be easier to create queries to do calculations.

To answer your questions:

For items #1 & #2
So glad you asked, I didn’t even think about this. There are 2 situations that can occur.

a) The first I already mentioned, the acres planted size varies for an individual field such as Putnal Big Field. (Perhaps the default size could be 100 acres, but when only 75 acres is planted we could input 75?)

b) The second situation is exactly what you asked – Putnal Big Field can occupy more than 1 crop at a time. However, in that very rare occasion, the dairy then assigns the second section of that planted area the name “Putnal Big Field Plot B”. For example Putnal Big Field has wheat (50 acres planted) and Putnal Big Field Plot B has corn (25 acres planted). *Note: We could have a list of Field names, such as Putnal Big Field. Then have a form that would allow them to enter a new Field name such as Putnal Big Field Plot B. Would that work?

For Item #3
Configurations aren’t recorded or tracked. I’m told the configuration could be as simple as the amount of fuel in the truck will affect the tare weight of the vehicle. They could have a piece of equipment on the truck that would also affect tare weight. However, they don't document that. Given that, it seems that your suggestion to track the tare weights and dates/times will be sufficient.

For Item # 4
I thought I was getting ahead of myself.

Thanks for helping me focus on table structure. I’ve spent months on tutorials and books and have learned a lot. There is so much more to learn. I’m grateful for your direction on modifying &/or expanding the table structure you provided.

 

[jzwp22]

Now we can revise the structure further

The second situation is exactly what you asked – Putnal Big Field can occupy more than 1 crop at a time. However, in that very rare occasion, the dairy then assigns the second section of that planted area the name “Putnal Big Field Plot B”. For example Putnal Big Field has wheat (50 acres planted) and Putnal Big Field Plot B has corn (25 acres planted). *Note: We could have a list of Field names, such as Putnal Big Field. Then have a form that would allow them to enter a new Field name such as Putnal Big Field Plot B. Would that work?

Since a field can have many plots (plot B, plot A etc.) it describes a one-to-many relationship so it should be handled with a related table and not as fields. Do they use the same naming convention for the plots within a field (i.e. Plot A, B etc.)?

So now we have to related the plots to the fields and the crop to the plot



tblFields
-pkFieldID primary key, autonumber
-txtFieldName
-FieldSize (in acres) default size


tblCrops
-pkCropID primary key, autonumber
-txtCropName

tblPlots
-pkPlotID primary key, autonumber
-txtPlotName

tblFieldPlotCrop
-pkFieldPlotCropID primary key, autonumber
-fkFieldID foreign key to tblFields
-fkPlotID foreign key to tblPlots
-plotsize (in acres)
-fkCropID foreign key to tblCrops
-plantingdate



tblTrucks
-pkTruckID primary key, autonumber
-txtTruckNo

Trucks will have multiple tare weights
tblTruckTare
-pkTruckTareID primary key, autonumber
-fkTruckID foreign key to tblTrucks
-TareDate
-TareWeight


tblHarvestDates
-pkHarvestDateID primary key, autonumber
-HarvestDate

tblHarvestDateFieldCrop
-pkHarvestDteFieldCropID primary key, autonumber
-fkHarvestDateID foreign key to tblHarvestDates
-fkFieldPlotCropID foreign key to tblFieldPlotCrop

tblSoilMeasurements
-pkSoilMeasureID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-MeasurementTime
-SoilMoistureAmount

tblTruckFieldCrops
-pkTruckFieldCropID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-fkTruckID foreign key to tblTrucks
-totalweight (tare + crop in lbs)
-Trucktime (time field)

I came up with another question regarding the truck tare weights. It would not make sense to me to use a tare weight if the weight was determined let's say 10 weeks before the harvest date, so is there a relationship between the harvest date and the tare weight dates of the trucks? In other words, do we want to average only the 3 most recent tareweights for the determinations done on the harvest date? If so, we'll need to tweak the structure further.

 

[hotrodsue]

Thank you for your continued help. I’m working in between your posts, and I’m making some progress. I figured the tblTruckTare correctly, mine matched yours.

so is there a relationship between the harvest date and the tare weight dates of the trucks? In other words, do we want to average only the 3 most recent tareweights for the determinations done on the harvest date? If so, we'll need to tweak the structure further.

Another great question. After checking with the dairy, I’m told that we do not want to average only the 3 most recent tare weights for determinations done on the harvest date. I posed your question regarding a tare weight being older, and the dairy said that was not a concern. Just need the 3 most recent tare weights over time.

Since a field can have many plots (plot B, plot A etc.) it describes a one-to-many relationship so it should be handled with a related table and not as fields. Do they use the same naming convention for the plots within a field (i.e. Plot A, B etc.)?

No they don’t have plots within a field, thanks for asking.

Studying your revised table, I have a few questions.
1) Looking at the Table: tblFieldPlotCrop
A plot will not always apply. We may have only “Putnal Big Field” planted with 100 acres of corn. No plots. May be a silly question, can the following fields be null or not applicable:
-fkPlotID foreign key to tbl Plots
-plotsize (in acres)
Also, planting date is not required nor provided. Can Harvest date go in the following: -plantingdate

2) Looking at Table: tblTruckFieldCrops
-Trucktime (time field)
Just checking, is the time field required?

I'll keep plugging away, given your input. Many thanks.

 

[jzwp22]

1) Looking at the Table: tblFieldPlotCrop
A plot will not always apply. We may have only “Putnal Big Field” planted with 100 acres of corn. No plots. May be a silly question, can the following fields be null or not applicable:
-fkPlotID foreign key to tbl Plots
-plotsize (in acres)
Also, planting date is not required nor provided. Can Harvest date go in the following: -plantingdate

2) Looking at Table: tblTruckFieldCrops
-Trucktime (time field)
Just checking, is the time field required?

Regarding #1:
Since it is probable that you can subdivide the field, you have to have the plots. In tblPlots you can have records indicating plot A, plot B,...Entire field etc. and you would use the Entire field to indicate that the entire field was planted and then specify the crop. If planting date is not important then you can drop it.

Regarding the harvest date, can a field always be harvested in the course of 1 and only 1 day? If so, then you might be able to have the harvest date in tblFieldPlotCrop and then join the trucks and moisture measurements to tblFieldPlotCrop. If a harvest for a field goes over several days (one field-to-many harvest days) then no, you cannot have the harvest date in tblFieldPlotCrop.

Regarding # 2
The time field is not necessary, I was using to capture if the same truck carried loads from the same field/plot during the course of the same day and that way you can calculate the time between loads if necessary.

 

[hotrodsue]

Thank you. I'm getting back to work on the table structure.

Regarding #1:
Since it is probable that you can subdivide the field, you have to have the plots. In tblPlots you can have records indicating plot A, plot B,...Entire field etc. and you would use the Entire field to indicate that the entire field was planted and then specify the crop. If planting date is not important then you can drop it.

Regarding the harvest date, can a field always be harvested in the course of 1 and only 1 day? If so, then you might be able to have the harvest date in tblFieldPlotCrop and then join the trucks and moisture measurements to tblFieldPlotCrop. If a harvest for a field goes over several days (one field-to-many harvest days) then no, you cannot have the harvest date in tblFieldPlotCrop.

OK, that makes perfect sense. That clears up my confusion regadring plots. And, since I can drop the planting date, I'll do that. Given that I can drop planting date, there is no need to put harvest date (or any date) in tblFieldPlotCrop.

Thank you for posting explanations that help me understand the process, I'm learning, it's sinking in. Especially your reminders about table relationships (one-to-many) and normalization. Hands on work and troubleshooting here is a wonderful extension to my book and DVD tutorials.

Regarding # 2
The time field is not necessary, I was using to capture if the same truck carried loads from the same field/plot during the course of the same day and that way you can calculate the time between loads if necessary.

Thanks, I really like this idea and would like to leave it. I'd like to give the dairy the opportunity to try it. Perhaps I can have that field be "not required". Question: If the dairy finds they'd prefer not to enter time, will it cause any problems in other database objects if that time field is not required? In other words, can it be included to see if it's useful? I think it will be, I just don't want to have something that isn't required mess me up down the road.

Again, many thanks, you've helped so much.

 

[jzwp22]

Regarding dropping the harvest date, you mentioned this in an earlier post:

You’re also right; there is a relationship between the harvest amount and the soil measurements for the same day on the same crop/field.

If you drop the harvest date, you will no longer need tblHarvestDate, but then you will need to restructure to tie the trucks and moisture measurements to the crop/field. So if that is the case, then I would recommend the following structure

tblFields
-pkFieldID primary key, autonumber
-txtFieldName
-FieldSize (in acres) default size


tblCrops
-pkCropID primary key, autonumber
-txtCropName

tblPlots
-pkPlotID primary key, autonumber
-txtPlotName

tblFieldPlotCrop
-pkFieldPlotCropID primary key, autonumber
-fkFieldID foreign key to tblFields
-fkPlotID foreign key to tblPlots
-plotsize (in acres)
-fkCropID foreign key to tblCrops

tblTrucks
-pkTruckID primary key, autonumber
-txtTruckNo

Trucks will have multiple tare weights
tblTruckTare
-pkTruckTareID primary key, autonumber
-fkTruckID foreign key to tblTrucks
-TareDate
-TareWeight


tblSoilMeasurements
-pkSoilMeasureID primary key, autonumber
-fkFieldPlotCropID foreign key to tblFieldPlotCrop
-MeasurementTime
-SoilMoistureAmount

tblTruckFieldCrops
-pkTruckFieldCropID primary key, autonumber
-fkFieldPlotCropID foreign key to tblFieldPlotCrop
-fkTruckID foreign key to tblTrucks
-totalweight (tare + crop in lbs)
-Trucktime (time field)

Regarding the trucktime, having it or not having it does not impact the relationships between the tables. You can keep it in the table, just make sure you do not change its required property to yes. You can include it in your forms/queries/reports. If they do not choose to use it, just change its visible property to NO in your forms and reports. It will be there if they change their minds in the future

 

[hotrodsue]

Regarding dropping the harvest date, you mentioned this in an earlier post:


Quote:
You’re also right; there is a relationship between the harvest amount and the soil measurements for the same day on the same crop/field.
If you drop the harvest date, you will no longer need tblHarvestDate, but then you will need to restructure to tie the trucks and moisture measurements to the crop/field.

My apologies, I don't think my post was relayed clearly. I'm not going to drop the harvest date, so I will use tblHarvesetDate. Before getting clarification from you regarding the planting date not being needed, I thought there needed to be some date value there, and thought of substituting the harvest date for the planting date. Sorry for the confusion.

Thank you for your time in revising the structure.

Regarding the trucktime, having it or not having it does not impact the relationships between the tables. You can keep it in the table, just make sure you do not change its required property to yes. You can include it in your forms/queries/reports. If they do not choose to use it, just change its visible property to NO in your forms and reports. It will be there if they change their minds in the future

Perfect, exactly what I needed to know. Nice idea to change it's visible property as needed.

Thanks for your kind help. I'll be sure to post here as soon as I get that table structure completed within my Access database.

 

[jzwp22]

You're welcome and good luck with the project.

 

[hotrodsue]

You're welcome and good luck with the project.

Thank you. Now that I have the tables in, I have a question about the foreign keys and table joins. For example, the tblTruckTare has the foreign key fkTruckID. I think perhaps the fkTruckID should be a lookup field, giving me the dropdown box to choose the Truck Number when entereing data into tblTruckTare.

tblTrucks
-pkTruckID primary key, autonumber
-txtTruckNo

Trucks will have multiple tare weights
tblTruckTare
-pkTruckTareID primary key, autonumber
-fkTruckID foreign key to tblTrucks
-TareDate
-TareWeight

To be sure I understand the structure process, I printed out your table structure and then drew table joins on paper. Basically, I know when I create a lookup field a table join is created. And, if I don't create a lookup field, I join the tables in the relationships window.

The foreign keys I'm not real sure about have to do with the Harvest Date. It seems like a date of any type would not be a look up field. Is that correct? I think the date gets entered in the tblHarvestDates under field "HarvestDate". If so, then are all other foreign key references to HavestDate (in red) would NOT be a look up field. Is that right?

tblHarvestDates
-pkHarvestDateID primary key, autonumber
-HarvestDate

tblHarvestDateFieldCrop
-pkHarvestDteFieldCropID primary key, autonumber
-fkHarvestDateID foreign key to tblHarvestDates
-fkFieldPlotCropID foreign key to tblFieldPlotCrop

tblSoilMeasurements
-pkSoilMeasureID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-MeasurementTime
-SoilMoistureAmount

tblTruckFieldCrops
-pkTruckFieldCropID primary key, autonumber
-fkHarvestDteFieldCropID foreign key to tblHarvestDateFieldCrop
-fkTruckID foreign key to tblTrucks
-totalweight (tare + crop in lbs)
-Trucktime (time field)

Hope my questions make sense. Sometimes I over-think things in my effort to be sure I understand and get it right. Many thanks.

 

[jzwp22]

If you are thinking about using lookup fields (list or combo boxes) at the table level--don't. This link explains why you should not. List and combo boxes are fine when you are doing your forms.

I would do all of the joins in the relationship window. I usually structure my primary key fields with pkxxxID and foreign keys with fkxxxID so that the relationship is clear. The pk is the one side of the relationship and the fk is the many side of the relationship. Regarding the harvest date, since you have to tie what was harvested on a particular day to the soil moisture measurements of the same day, that indicates that you have to join them via that date. Of course, the join field is not the actual date field but rather the primary key field of the record that holds the date.

 

[hotrodsue]

If you are thinking about using lookup fields (list or combo boxes) at the table level--don't. This link explains why you should not. List and combo boxes are fine when you are doing your forms.

I would do all of the joins in the relationship window. I usually structure my primary key fields with pkxxxID and foreign keys with fkxxxID so that the relationship is clear. The pk is the one side of the relationship and the fk is the many side of the relationship. Regarding the harvest date, since you have to tie what was harvested on a particular day to the soil moisture measurements of the same day, that indicates that you have to join them via that date. Of course, the join field is not the actual date field but rather the primary key field of the record that holds the date.

Thank you for the answer and the reason not to use lookup fields at the table level. I've bookmarked that link. I'll do all joins in the relationships window.

The relationship regarding the haverst date makes sense. Again, the one to many explanation helps. The join field is not the actual date field but rather the pk field of the record that holds the date. That gave me a better understanding of the role of the pk and fk.

I'm going to get back to it. Thank you so much.

 

[jzwp22]

You're welcome. Once you have the table structure and relationships completed, I would be happy to review it for you if you would like.

 

[hotrodsue]

You're welcome. Once you have the table structure and relationships completed, I would be happy to review it for you if you would like.

Thank you, that will be great. Attached is the database table structure and relationships.

When doing table joins in the relationships window, I checked "Enforce Referential Integrity" in the Edit Ralationsips dialogue box. In your experience, is it also good to check "Cascade Update" and "Cascade Delete"?

Regarding the foreign key, I chose the Data Type "Number". Is that correct? I believe I read the fk should be the same Data Type as the pk.

My DVD tutorial did have a lesson on look up fields in tables. I'm SO glad you posted that link that showed why I should not do that. I thought perhaps I didn't remember the lesson correctly. Very happy I avoided that.

At this time I'll enter some test data into my tables, and perhaps get working on my queries. Thanks again for looking over the attached table structure and joins.

 

[jzwp22]

You're welcome. Everything looks great!

I generally enforce referential integrity and check both cascade update and delete; there are some times when you don't, but I do not think it would apply in your application. You are correct in that the foreign key fields should be numbers (long integer) to match the autonumber datatype of the primary key fields.

Best of luck as you move forward on the project!

 

[hotrodsue]

Glad all looks well, thanks to your direction. Previously I've done queries, forms, and reports in my first database.

I had all types of queries for this current Harvest database, prior to getting proper table structure. I don't know why, but now that I have proper table structure, I'm not sure exactly how to start the query process.

Yesterday I started reviewing my tutorial material. Just thought I'd give a quick post here. If you have a suggestion on how to start, I would appreciate it. I really want to learn what I'm doing. You give me the "why" behind your instruction, which is great.

Back to the tutorials for now.

 

[jzwp22]

I would recommend starting with your forms first. The easiest thing to remember is that your main form should be based on the one side of a one-to-many relationship while the subform should be based on the many side of the relationship. I would try to base your forms and subforms on the tables rather than queries because when you start joining tables in a query, you could end up with an un-updateable recordset (one you will not be able to enter any data into). You can use the forms to enter some fictitious data and then work on the queries to do the calculations that you need.