Table Structure

[jzwp22]

Based on your attachment, the txtFieldName requires an actual entry (not a combo box) since this form is tied directly to the underlying table that holds the field names

 

[hotrodsue]

Based on your attachment, the txtFieldName requires an actual entry (not a combo box) since this form is tied directly to the underlying table that holds the field names

Hello, I'm back working on this project. Since the form is tied directly to the underlying table that holds the field names, does this mean I enter the txtFieldName such as Putnal Big Field, each time the Putnal Big Field is used?

 

[jzwp22]

No, you should have only 1 record that corresponds to each field name (such as Putnal Big Field) in tblFields. What do you mean by "...each time it is used"?

 

[hotrodsue]

Thanks so much for your help. What I mean is....each time something is harvested from Putnal Big Field. It seems I may be confusing this form, frmFields, with forms I'll need to do actual data entry.

In the last few days I've continued to review our discussion here. I've printed out your help and instruction to study the foundation you helped me with. At this time I think I'm stuck because I don't understand how the data input forms work when using subforms.

Thanks again for your kindness and help, I appreciate it.

 

[jzwp22]

In order to enter harvest information into the form you showed earlier, you would have to have more subforms which will probably make the form more difficult to use. I would therefore recommend a new form. When we last discussed table structure, I recommended having the following table, but I don't know if you have made any other changes to the structure.

tblFieldPlotCrop
-pkFieldPlotCropID primary key, autonumber
-fkFieldPlotsID foreign key to tblFieldPlots
-fkCropID foreign key to tblCrops

Could you post a copy of the most current version of the database? I don't have Access 2007 here at work, so I will have to look at it this evening.

 

[hotrodsue]

Attached is the database, thanks for taking a look at it this evening. There haven't been any changes to the table structure, and it does include your recommendation for tblFieldPlotCrop.

In working on forms, I have created 2 forms that have sub forms, frmHarvestDates and frmFields.

Your suggestion below from a previous post is what I tried when I created frmFieldPlots.

For example, I might create a main form/subform to define the fields and related plots since that is probably determined before anything is planted. Then you can create a form based on a query (using the fields and plot tables) with a subform to enter the crop planted in that field/plot combination. You can then go back and add the crop to the appropriate field/plot combination that had already been established.

Regarding the harvest information, I like your idea to make new forms rather than subforms. Thank you for your continued help.

 

[jzwp22]

I noticed a couple things with the combo boxes you had on both frmFields and frmHarvestDates. Most of the combo boxes were not bound to the underlying field. The combo boxes in these cases must be bound so that you will populate the data into the table. The key to getting the frmHarvestDates to work is to be able to identify which field-plot-crop is being harvested and tie that to the fkFieldPlotCropID in tblHarvestDateFieldCrop. To do that I first created a query (qryFieldPlotCropList) to bring all of those pieces of info together and then used that query as the row source to a combo box bound to fkFieldPlotCropID.

I corrected the combo boxes and added the other changes described above in the attached database.

If you want to use this same database over many planting seasons (which I assume you do), you can run into a situation where the same field/plot/crop combination might be planted exactly the same in more than 1 season. Access will unique identify the records, but they will look the same to the user. If you do not filter the Field-Plot-Crop listing you might end up "reharvesting" the same combination from a previous season rather than the current season. In other words, you will have to modify qryFieldPlotCropList to limit the list to only those (field-plot-crop combinations) that have not yet been harvested. I assume you have some way of telling when a harvest is complete.

 

[hotrodsue]

Thank you for your detailed post and updates. Today has been busy, I look forward to getting into this tonight. Thanks so much.

 

[jzwp22]

Please let me know if you have any questions.

 

[hotrodsue]

I kept my original database to compare to your updated version 2, so I can learn from your example. In my original I could easily see the unbound combo box, and how to fix it viewing your version.

On unbound combo boxes, it looks like you hi-light the unbound box and type the source (such as fkPlotID) into Control Source, in the Data tab of the Property Sheet. Is that correct? Also, I found when using the wizard, I would click “Store that value in this field” to create a bound control. That’s probably what I missed. Thank you for correcting my combo boxes. If I understand this, an unbound box would be for selecting info from a table, and a bound box will populate the table.

The key to getting the frmHarvestDates to work is to be able to identify which field-plot-crop is being harvested and tie that to the fkFieldPlotCropID in tblHarvestDateFieldCrop.

Next, I studied qryFieldPlotCropList you created. You entered some test data, did you use the form frmFields to do that? As a tutorial for myself (in my original database), I entered test data into frmFields. Then created qryFieldPlotCropList and used it as row source for combo box bound to fkFieldPlotCropID. It was great to view the property sheet of your version, I learned from you I could set the column width to 0 on the pk column.

Now I’m going to enter some test data into the frmHarvestDates.

If you want to use this same database over many planting seasons (which I assume you do), you can run into a situation where the same field/plot/crop combination might be planted exactly the same in more than 1 season. Access will unique identify the records, but they will look the same to the user. If you do not filter the Field-Plot-Crop listing you might end up "reharvesting" the same combination from a previous season rather than the current season. In other words, you will have to modify qryFieldPlotCropList to limit the list to only those (field-plot-crop combinations) that have not yet been harvested. I assume you have some way of telling when a harvest is complete.

Excellent point, this database will be used over many planting seasons. This brings 2 questions to mind. First, when you say modify qryFieldPlotCropList, will date parameters filter the listing? The person doing data entry will know when a harvest is complete when the last truck load is harvested. For example, 80 acres of corn was just harvested over a four day period. It seems I may need to address harvest completion. This brings me to my second question. Ultimately I’ll need to report on a field/plot/crop combination of each harvest. Does that mean we need a harvest start date and harvest end date? The customer mentioned he’d want to pull up a report by field name such as Putnal Big Field, showing each harvest. However, he may not know exact harvest date, so he'd like to search by Field name "Putnal Big Field" and a date range.

Hopefully my questions make sense. I’m going to go back to it. Thank you for your help.

 

[jzwp22]

On unbound combo boxes, it looks like you hi-light the unbound box and type the source (such as fkPlotID) into Control Source, in the Data tab of the Property Sheet. Is that correct?

Actually, I deleted what you had and used the combo box wizard to create a new combo box, but you can do it manually as you describe.

If I understand this, an unbound box would be for selecting info from a table, and a bound box will populate the table.

An unbound combo or list box is used for selecting from a table or query that is different from the table/query on which the form is based (bound). Any unbound form control (combo box, list box, textbox) will not populate a value in a table, only bound controls. You would generally use unbound controls for calculated values.

You entered some test data, did you use the form frmFields to do that?

Yes, I used your form to enter data my test data. To make your form easier on the user, you can hide the controls that hold the pk and fk values by clicking on them in Design View and going to the properties sheet--format--visible and set it to no. I generally leave them visible while testing to make sure everything populates correctly, then I change the visible property setting to no when I finalize the form.

First, when you say modify qryFieldPlotCropList, will date parameters filter the listing?

Yes, you could use dates to filter, I'm just not sure which date/dates yet.

Ultimately I’ll need to report on a field/plot/crop combination of each harvest. Does that mean we need a harvest start date and harvest end date? The customer mentioned he’d want to pull up a report by field name such as Putnal Big Field, showing each harvest. However, he may not know exact harvest date, so he'd like to search by Field name "Putnal Big Field" and a date range.

This last item might be a little difficult. We structured the database such that a harvest was more of an event for the whole farm and in each harvest event you had multiple fields/plots/crops being harvested. Now you could put a completion date for the entire event or you could put a completion date for each field/plot/crop combination. Does your user care when the harvest started and ended for an individual field/plot/crop combination or are they only concerned with the entire event?

 

[hotrodsue]

This last item might be a little difficult. We structured the database such that a harvest was more of an event for the whole farm and in each harvest event you had multiple fields/plots/crops being harvested. Now you could put a completion date for the entire event or you could put a completion date for each field/plot/crop combination. Does your user care when the harvest started and ended for an individual field/plot/crop combination or are they only concerned with the entire event?

The user is only concerned with the entire event. He was concerned that he wouldn't remember a specific date when searching for a harvest event. Of course, it's ok to have start and end dates, if that's what is needed.

Thank you for taking time to answer all my questions.

 

[jzwp22]

It sounds like you can put an ending date field in the harvest table. This will then assume that all field/plot/crops tied to that harvest event will have been harvested by the ending date. You can then use that to filter the query I mentioned earlier. I'm guessing that you will need to modify the query with a nested query. I'll let you give it a shot to see if you can figure out how to do it. If you get stuck and totally frustrated, just post back.

 

[hotrodsue]

Many thanks, I'll give it a shot and let you know how I do.

 

[hotrodsue]

I've put an ending date field in the harvest table and placed the harvest end date field in the frmHarvestDates. Then I entered a harvest end date for the first test harvest event.

Then I went to the qryFieldPlotCropList. There, I added tblHarvestDateFieldCrop which automatically joined to an existing table in the query tblFieldPlotCrop. When I run the query, it shows the only FieldPlotCropList combination that has been used. Rather than the FieldPlotCropList combinations that have not been used.

I then added the tblHarvestDates. I'm trying to filter by end date, so I'ved tried placing some Criteria >[tblHarvestDates]![HarvestEndDate]

It seems like I may be close. Am I on the right track?

 

[jzwp22]

You are on the right track; you just need the right criteria in the WHERE clause of the query. Since you only want those field/plots/crops that have not been harvested (i.e. no harvest end date) you basically want those records where the harvest end date is Null.

In SQL syntax:

SELECT...
FROM...
WHERE yourharvestenddatefield Is Null

 

[hotrodsue]

You are on the right track; you just need the right criteria in the WHERE clause of the query. Since you only want those field/plots/crops that have not been harvested (i.e. no harvest end date) you basically want those records where the harvest end date is Null.

In SQL syntax:

SELECT...
FROM...
WHERE yourharvestenddatefield Is Null

Attached is a screen cap of qryFieldPlotCropList in design view. Your SQL syntax direction makes sense to me. I don't have this working right yet. Just thought I'd be sure the layout of qryFieldPlotCropList is correct. In a previous post I noted that I added tblHarvestDates and tblHarvestDateFieldCrop to the qryFieldPlotCropList in an effort to filter by harvest event.

If this looks right, I'll keep plugging away. Also, thanks for your previous post about making my forms easier on the user.

To make your form easier on the user, you can hide the controls that hold the pk and fk values by clicking on them in Design View and going to the properties sheet--format--visible and set it to no. I generally leave them visible while testing to make sure everything populates correctly, then I change the visible property setting to no when I finalize the form.

 

[jzwp22]

It looks like you are on the right track, you just need to add the harvestenddate field to the query with the criteria I suggested. I would still recommend doing some testing with datasets that have a harvest end date and some that don't to make sure the query pulls the right info for the combo box.

 

[hotrodsue]

Thanks so much. I wondered if I should have some harvest events without an end date. Great to know I'm still on the right track. Will keep at it. Many thanks!

 

[hotrodsue]

The tblHarvestEndDate Criteria is set to Is Null. Entering another harvest event with no EndDate helped. Attached is a screen cap of qryFieldPlotCropList in design view. The query results are now returning the 1 field/plot/crop combination that does not have an EndDate.

If I understand this part, I want to show all field/plot/crop combinations created without an end date. So, when I go to frmHarvestDates to enter a harvest event, my Field/Plot/Crop Harvested combo box will have any filed/plot/crop combination with no EndDate to choose from.

In SQL view, it seems like the issue may be that I don't have a nested query as you suggested. I'm studying nested queries at the following links:

http://office.microsoft.com/en-us/access/HA102061111033.aspx?pid=CH100645771033

http://allenbrowne.com/subquery-01.html

Here is my SQL for the query screen cap I attached.

SELECT tblFieldPlotCrop.pkFieldPlotCropID, tblFields.txtFieldName, tblPlots.txtPlotName, tblCrops.CropName, tblHarvestDates.HarvestEndDate
FROM tblPlots INNER JOIN (tblHarvestDates INNER JOIN (tblFields INNER JOIN (tblFieldPlots INNER JOIN ((tblCrops INNER JOIN tblFieldPlotCrop ON tblCrops.pkCropID = tblFieldPlotCrop.fkCropID) INNER JOIN tblHarvestDateFieldCrop ON tblFieldPlotCrop.pkFieldPlotCropID = tblHarvestDateFieldCrop.fkFieldPlotCropID) ON tblFieldPlots.pkFieldPlotsID = tblFieldPlotCrop.fkFieldPlotsID) ON tblFields.pkFieldID = tblFieldPlots.fkFieldID) ON tblHarvestDates.pkHarvestDateID = tblHarvestDateFieldCrop.fkHarvestDateID) ON tblPlots.pkPlotID = tblFieldPlots.fkPlotID
WHERE (((tblHarvestDates.HarvestEndDate) Is Null));

Just thought I'd see if I'm continuing on the right track. Does it appear that my problem is that this is NOT a nested query?