Putting a summary field into a sub summary layout part produces a sub total in that sub summary part when the "sorted by" field specfied in the part setup is included in the sort order for your records.
But if you refer to a summary field in a calculation, this happens at a lower level that does not take the design of your layout into account. (Nor should it as this calculation field might be displayed on any number of layouts and the sub summary layout part might not even be on the other layouts.) Instead, references to a summary field return the grand total instead of the sub total.
But there's a function for handling this situation: GetSummary ( SummaryField ; BreakField )
The "break" field is the "sorted by" field you specify for your sub summary part. If you use this funciton in your calculation, and if your records are sorted by the break field, the funciton will return the desired subtotal so that you can use it in your calculation.
People >--< Events two tables that would be very hard to use to calculate certain things. [many to many relationship]
People >-- Attendance --< Events a join table that makes the calculations much easier. [many to one to many relationship]
Home > Designing and creating databases > Creating a database > About planning a database
A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.Follow these general steps to plan a database:
Relational Database Design 101 (part 1 of 3): Designing a Flat File Database
Relational Database Design 101 (part 2 of 3)
Relational Database Design 101 (part 3 of 3)
The White Paper for FMP Novices is useful -
Thanks PhilModJunk, that is fairly helpful. It at least got me going in the right direction. Here is where I find myself now:
I am able to use GetSummary (ProjectEquipment ; Model#) so as to get a quantity of model that I need for a project. I'd like to then subtract how many I have in stock - GetSummary (Stock::StockEquipment ; Model#) to then result in a number of how many I need to rent. It seems like if I am doing this in the Project Table, then only the first function works and if I am in the Stock table then only the second. How do I perform this across multiple tables? They are related, in that Stock ---< Project. Do I have a many to many relationship, since a project contains many types of equipment and so does my stock?
If I need to modify my relationships, I understand WHY just not HOW. Any thoughts are greatly appreciated.
Please describe your relationships between the two tables, (what are the match fields?) on which table your layout is based (see layout setup...) and how your system calculates the "in stock" value for a given item.
To me, this looks like you need another table so that you have exactly one record in Project for each project and a related table that lists the items required for that project.