We'd need to see how you've set up your database. With your calculation, if there are related records in Table 1 and Table 3, it will return "13".
Thus, the issue would appear to lie either with the relationships you've defined or the data in the key fields of the two tables on each side of that relationship. Try placing the ID field from each of these tables on a layout based on whatever table in which you've defined this calculation.
Also, you've mentioned a "checklist box" (Check boxes?) yet this is a calculation field. Please explain the role of that "checklist box" in greater detail so that we can understand its purpose here.
Your calculation itself seems very unusual. Feel free to explain its purpose if you want. We may be able to suggest an alternate approach.
Thanks for your reply. I have a Client table where all client records are stored. From there, other tables relating to services carried out by the organisation are linked via a Client ID field. Clients are linked through this relationship to the service tables, hence avoiding duplicating client records in each table. Service tables have their own ID fields as well as the client ID.
What I'd like to do for each client record is list which service table(s) they can be found within. I attempted to do this using a checkbox within the client table layout based on a value list. So if Mr. X had service IDs for service tables Y and Z, the checkbox would be ticked for services Y and Z.
Does that make sense? At the moment, the way I've done it works, but the checkbox is only ticked if the client is found in one service table. If they are listed in more than one, no checkboxes are ticked. I think there's an issue with my calculation.
Is there any way to do this in one go? I could make multiple checkbox fields for each service but I would rather do it in one if possible.
If ( table 1::ID ≠ "" ; "1" ) &
If ( table 2::ID ≠ "" ; "2" ) &
If ( table 3::ID ≠ "" ; "3" ) &
If ( table 4::ID ≠ "" ; "4" ) &
If ( table 5::ID ≠ "" ; "5" ) &
If (table 6::ID ≠ "" ; "6" ) &
If ( table 7::ID ≠ "" ; "7" ) &
If (table 8::ID ≠ "" ; "8" )
To check multiple items in a checkbox (and this checkbox is calculation so it is only display) then you need to make the calculation return a LIST. Try adding ¶ &to the end of every line but the last. I think it can be simpler yet. If the IDs are numbers (and they should be) then it can be:
If ( table 1::ID ; 1 ) & ¶ &
If ( table 2::ID ; 2 ) & ¶ &
If ( table 3::ID ; 3 ) & ¶ &
If ( table 4::ID ; 4 ) & ¶ &
If ( table 5::ID ; 5 ) & ¶ &
If ( table 6::ID ; 6 ) & ¶ &
If ( table 7::ID ; 7 ) & ¶ &
If ( table 8::ID ; 8 )
I have to ask why you have your Services split accross 8 different tables. It usually is handled with one Services table and, if any services have different requirements than would fit in standard fields then that data would reside in another table related to Services. The fact that you need to 'search all 8 tables' with this calculation is only a small indicator of the problems you will run into having Services split.
Thanks LaRetta that worked with the carriage symbols.
I've inherited this database and it was already built in this style. To me it feels logical to have a table for individual services, esepcially as each service has 50+ unique fields.
Thanks for your help,
Oh, I understand what you mean. But you can always use subtype tables as I suggested because otherwise you will be hitting this type of roadblock at every turnaround, having to check every table for every value, complicating your calculations, complicating your tables with duplicate calcs and complicating your graph with multiple tables (and their table occurrences). Here are a few links to which explain it further:
You can't even generate a report based upon multiple services; if you use one main table then you could. Regardless, I'm pleased that you got your question answered. :^)