Table Structure

[hotrodsue]

Thank you. My tendency was to go to forms next, however, I mistakenly thought queries came after tables as I did that in some tutorials. I'll go forward with forms and subforms. Then to queries to perform calculations. I'll let you know how I do.

 

[hotrodsue]

Attached is my database with a frmTrucks and subfrmTruck Tare. That gives me the ability to enter Tare Weight for each Truck by ID. And, I have frmTruckLoads and subfrmTruckFieldCrops that allows me to enter TotalWeight of the truck loads.

I think I'm making this harder than it needs to be. I've looked at my book and my DVD for direction on creating forms and subforms. Also, I've searched the internet. This is my first time using subforms.

When I look at tblFields, tblCrops, and tblPlots, I see these are all a one-side table to the many side table tblFieldPlotCrop. Do I make a form for each of these one-side tables? If so, do I place a subform using tblFieldPlotCrop on each of the forms?

I'll keep working on grasping the concept of subforms. Thanks.

 

[jzwp22]

I don't have Access 2007 here at work, so I will have to take a look at your DB later this evening, but in answer to your question, I would have separate forms tied to each of the tables (tblFields, tblCrops, and tblPlots) since the data in these tables will be somewhat stagnant once you enter it.

I also had a second thought about the table structure; if a field can be subdivided into multiple plots, then I think this structure would be better. I've added the tblFieldPlots which relates the multiple plots to the field and then tied the primary key of this table to the tblFieldPlotCrops table

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


tblPlots
-pkPlotID primary key, autonumber
-txtPlotName

tblFieldPlots
-pkFieldPlotsID primary key, autonumber
-fkFieldID foreign key to tblFields
-fkPlotID foreign key to tblPlot
-plotsize


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

You can then add a subform to the form that is based on tblFields. That subform would be based on tblFieldPlots. You would populate the fkPlotID using a combo box (the row source of the combo box would be based on tblPlots).

 

[hotrodsue]

Thank you for your continued help, it's very much appreciated.

I would have separate forms tied to each of the tables (tblFields, tblCrops, and tblPlots) since the data in these tables will be somewhat stagnant once you enter it.

Ok, so all tables that have a one-to-many relationship don't necessarily need a sub form. Separate forms for these table will work, since they'll be somewhat stagnant.

Also, I'll go ahead and make your recommended changes to the table structure.

You can then add a subform to the form that is based on tblFields. That subform would be based on tblFieldPlots. You would populate the fkPlotID using a combo box (the row source of the combo box would be based on tblPlots).

With your specific directions here, I'm sure to get this. Many thanks.

 

[jzwp22]

I took a quick look at your forms especially the frmTruckLoads. If you look at the underlying table (tblTruckFieldCrops) to the subform, you will notice that the fkHarvestDateFieldCropID is not populating. This is because tblTruckFieldCrops is related to both the truck and the harvest date, so you will need a mainform, subform, subform set up.

 

[hotrodsue]

I've added the tblFieldPlots which relates the multiple plots to the field and then tied the primary key of this table to the tblFieldPlotCrops table

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


tblPlots
-pkPlotID primary key, autonumber
-txtPlotName

tblFieldPlots
-pkFieldPlotsID primary key, autonumber
-fkFieldID foreign key to tblFields
-fkPlotID foreign key to tblPlot
-plotsize


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

You can then add a subform to the form that is based on tblFields. That subform would be based on tblFieldPlots. You would populate the fkPlotID using a combo box (the row source of the combo box would be based on tblPlots).

After updating my table structure as noted above, I created the from based on tblFields, and subform based of tblFieldPlots. Attached is a jpg of the form.

Working to populate the fkPlotID box using a combo box. I'm able to create a new combo box PlotID within the form and base the source on tblPlots. Is that correct? If so, do I delete the fkPlotID field? That doesn't seem right.

I was able to change the fkPlotID text box to a combo box, however, did not have success indicating the source as tblPlots. So, I have a combo box, and no information in the drop down of that comb box.

 

[hotrodsue]

I was able to change the fkPlotID text box to a combo box, however, did not have success indicating the source as tblPlots. So, I have a combo box, and no information in the drop down of that comb box.

Not sure how I missed this, as I reviewed the property sheet to set the rowsource. Perhaps I didn't have the fkPlotID combo box hi-lighted. So, now I have the fkPlotID as a combo box, with the row source set to tblPlots. The combo box drop down now displays the PlotID primary keys 1,2,3 rather than the txtPlotName Entire Field, Plot A, Plot B. I'll see if I can figure that out. Am I on the right track now?

 

[jzwp22]

In design view of the form, I would delete the control for the fkPlotID and then use the combo box wizard to step you through the process of building the combo box. You will bind the combo box to the fkPlotID field in the table underlying the form. I would add some plot records to the tblPlots before creating the combo box just so you see what it will look like. They don't have to be real records, just something for test purposes.

 

[hotrodsue]

Yes, I'm excitied! I got the combo box done, and my first form with subfrm is completed. Thank you so much for your help.

I took a quick look at your forms especially the frmTruckLoads. If you look at the underlying table (tblTruckFieldCrops) to the subform, you will notice that the fkHarvestDateFieldCropID is not populating. This is because tblTruckFieldCrops is related to both the truck and the harvest date, so you will need a mainform, subform, subform set up.

Now onto tackling this one. On the mainform, subform, subform set up; a couple of questions. Is the 2nd subform a subform of the first subform, or are both subforms a subform of the main form?

I'm looking at the relationships: tblHarvestDateFieldCrop has a one-to-many relationship with tblTruckFieldCrops. Also, tblTrucks has a one-to-many relationship with tblTruckFieldCrops.

Would tblHarvestDateFieldCrop be my main form, with tblTruckFieldCrops as a subform? Then tblTrucks a subform of tblTruckFieldCrops?

 

[jzwp22]

I would base my main form on tblHarvestDate; then within that form have a subform based on tblHarvestDateFieldCrop. The subform will have two subforms, one based on tblTruckFieldCrops (and use a combo box to populate the TruckID) and the other based on tblSoilMeasurements.

 

[hotrodsue]

Your continued help is valuable, I appreciate your kindness. At times I’ve been confused and wondered if I’d get this. Also, I had concerns about asking too many questions.

Late yesterday I wrote down what might work for main form and subforms. I called it a day and knew I’d start fresh today. This morning I got your post and I’m surprised to see what I had is exactly what you suggested . I’m thrilled to be making progress. Of course, it would not have been possible without your insights. I’m glad you noted the combo box to populate TruckID.

Thank you. I’m going to get to work and will let you know how I do.

 

[jzwp22]

You're welcome & please do not hesitate to ask questions.

 

[hotrodsue]

Thanks so much. Attached is a .jpg of my Main form with subforms. I did the combo box for TruckID, as you suggested. Some other foreign keys did not populate. Do I need to a create combo box to look up those values?

 

[jzwp22]

Are the subforms properly linked to their respective main forms? To check this, click on the outer frame of the subform and open the property sheet. Go to the data tab and make sure the Link Master Fields (pkxxxxID of the main form) and Link Child Fields (fkxxxxID of the subform) are filled in and are correct. I have found that Access doesn't always link the main and subforms automatically even if the relationships are established in the relationship window.

 

[hotrodsue]

The Master and Child Field Links all look good. That’s a great tip; I didn’t know to check for that even when tables are joined in the Relationships window. Since the Links all seem to be correct, I wonder if I have other forms to create or perhaps test data to be entered.

On first glance I see that I have test data entered in the following:
tblFields
tblPlots
tblCrops
tblFieldPlots
However, I don’t see anything in the table tblFieldPlotCrop. This table consists of the pk, and 2 fk fields fkFieldPlots and fkCropID.

Am I looking in the right direction?

 

[jzwp22]

I think you are on the right track, you need to get some data in all tables. It will help diagnose where problems are occurring. From the sound of it, you still may need additional forms as well. Some people have a tendency to make one for with a bunch of subforms in an attempt to do everything in one main form. I think this is more confusing to the end users. I normally use a custom menu system (just a series of unbound forms) that directs users to the forms that the tasks they want to do--but I usually create the basic forms first and then the menu system.

 

[hotrodsue]

Good to know I’m on the right track. I’ll put data in tables and see what forms may be needed. The table below has pk and fk’s, with no data input field. This table wouldn’t require a form for data input, is that right? If I understand this, its purpose is to join related tables. Perhaps I still need a form for it to be used as a subform? Guess I wonder if every table needs a form.

tblFieldPlotCrop
pklFieldPlotCrop
fkFieldPlots
fkCropID

Some people have a tendency to make one for with a bunch of subforms in an attempt to do everything in one main form. I think this is more confusing to the end users. I normally use a custom menu system (just a series of unbound forms) that directs users to the forms that the tasks they want to do--but I usually create the basic forms first and then the menu system.

My preference would be to do as you suggest, I’d rather create more forms and direct the end user to input from a menu. Also, I’m still trying to wrap my head around the subforms. With all my questions, it may be hard to believe I did a small database for tracking fuel. I created a menu system that opened forms to input data or add a new record to a table. Just haven't done subforms before and need to figure out the proper use.

Is there a rule of thumb you use for determining when to use a form and when a subform is needed? Thank you for welcoming me to ask questions.

 

[jzwp22]

tblFieldPlotCrop
pklFieldPlotCrop
fkFieldPlots
fkCropID

You will still need a form to populate the fk's in the table. This table would be best tied to a subform linking via fkFieldPlots to the main form. You would then populate the fkCropID with a combo box.

I don't know if there is a general rule for when to use subforms, but I usually use them for the many side of a relationship. It can get somewhat cloudy when you have junction tables like the one above. I think in that case it depends on your process. I would say the logical way is to define your fields (main form) and then plots within those fields (subform) and then the crops within the plots (subsubform). If you don't like the mainform/subform/subsubform setup then you can just create a series of simplier forms and walk your user through those. 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. What I am saying is how you set up your forms also depends on how your business/operation works.

 

[hotrodsue]

Thanks for the detailed response, that really helps. I'm going to go back to work on it some more.

 

[hotrodsue]

It looks like I have my first mainform/subform/subsubform setup, attached is .jpg. All the fields are populating. Your detailed instruction was very helpful. On the very first field on the main form txtFieldName, should that be a combo box to choose the Field Name?

I'm going to look at your suggestion for series of simplier forms. I'll need to make a decision on which way to go and move forward. My goal is to use a setup that is most user-friendly for the end user.

Thanks again.