Not sure I can follow your description of your database structure.
Do you have a table where....
You have a field that records the name or (better) serial number of a single green house and a second field signs that is a number field counting the number of signs for that greenhouse?
Or do you just have a checkbox formatted field that lists all your greenhouses? If so, you should consider a redesign of your system here.
I think you are dealing with the latter here and that's what's making life difficult for you. You can have both, a table where one record exists for every greenhouse and a checkbox field that lists all your greenhouses. With the table listing all your greenhouses, determining which sign fields to total up and then increase by 4 becomes a much easier proposition.
Let me know if I'm on track and I or another poster can take you to the next step for here to do what you want.
I want to create a calculation that will add the # of signs needed together +4
Not sure what the "+4" means. To get the total amount of signs for the selected greenhouses, try =
Sum ( tblGreenhouse::Signs )
The tblGreenhouse is already filled out, much like a "lookup table" that has all the greenhouse numbers and numbers of signs already associated with them. This table does not get updated. It is linked to tblApplication which is has been converted to a layout where applications are recorded. The "GH" field on this layout is actually based on a value list that I created. So really the link between the 2 tables is there, but isnt really necessary or needed, until now.
Now I want the associated # of signs to be added up depending on which checkbox(es) are checked at the time of application.
Does that help explain it better? Hopefully it does
the +4 means I want 4 added to whatever the total is that results from the calculation, every time. I am going to wait to hear back from Phil, I may be going about this the wrong way.
the +4 means I want 4 added to whatever the total is that results from the calculation, every time. I
Sum ( tblGreenhouse::Signs ) + 4
Ok, this is how I read it.
You have a check box formatted field such as:
[ ] Greenhouse A
[ ] Greenhouse B
[ ] Greenhouse C
and so forth. If the user selects greenhouse A, (5 signs) and Greenhose C, (2 signs), You want to display 7 signs + 4 extra = 11 signs correct?
If your check box formatted field is the field you use in your relationship to link your two tables, then Comment's calculation, Sum ( tblGreenhouse::Signs ) + 4, will return the total you want.
Thank you both, I will give that a shot.
Question though, does it matter that the CheckBox formatted field is based off a value list? The greenhouse names/numbers are the same that is in the other table, identically, but the field in tblApplication was formatted as checkboxes based off a value list that I made manually, rather than the records in "GH" field in tblGreenhouse. If that makes sense.
Thanks again for the help!
A couple of points:
1. If you have a table of Greenhouses, there's no point maintaining a custom value list listing all greenhouses - that's what the Greenhouses table is for. If you add/delete/modify a greenhouse, you want to do it once only.
2. In the Greenhouses table, you shoud have a GreenhouseID field with auto-entered serial number. Your value list should use this field (also showing the name field), and the relationship should be based on matching Applications::GH to Greenhouses::GreenhouseID. This way, you can rename a greenhouse without breaking the relationship.
3. Using a checkbox as the matchfield is often a convenient shortcut - but it does NOT establish a full-fledged many-to-many relationship: you cannot enter any specific data pertaining to the join of Apllication X to Greenhouse Y, and you cannot produce some type of reports. For these, you need a third table to join these two.
Good points all. I would only use the relationship I described for this type of lookup function. A different relationship would be used for standard data entry.