Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!news.musoftware.de!wum.musoftware.de!news.ripco.com!news.glorb.com!border3.nntp.dca.giganews.com!Xl.tags.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local2.nntp.dca.giganews.com!news.giganews.com.POSTED!not-for-mail NNTP-Posting-Date: Mon, 23 Jan 2012 10:05:19 -0600 From: Doug Anderson Newsgroups: comp.databases.filemaker Date: Mon, 23 Jan 2012 10:05:19 -0600 Message-ID: <2012012310051997402-douga@fcstonecom> References: <2012011914424122189-douga@fcstonecom> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: 8bit Subject: Re: Splitting amounts based on linked table User-Agent: Unison/2.1.4 Lines: 72 X-Usenet-Provider: http://www.giganews.com X-Trace: sv3-JZs6J+3g9vKOOs/phh/hVPqVwu8LAvZ0ELEXDDGzG4O/upT558IBzIUPTDf0NHuQ/Nnmh9JMrTejlHP!EceW36jsHKmqMR4GcJPNuMle3RIhoDQCY2IBfEVgKl8kwH3l2ko9BttqwtC2554= X-Complaints-To: abuse@giganews.com X-DMCA-Notifications: http://www.giganews.com/info/dmca.html X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.40 X-Original-Bytes: 3568 Xref: x330-a1.tempe.blueboxinc.net comp.databases.filemaker:558 On 2012-01-20 01:30:44 +0000, Bill said: > In article <2012011914424122189-douga@fcstonecom>, > Doug Anderson wrote: > >> In MS Access I would accomplish this by simply making a summary query, >> but such a thing doesn't exist in Filemaker. I'm using FM 11. >> >> I have two tables as follows. >> >> Table1 >> cust >> BCID >> Segment >> Amount >> >> Table 2 >> BCID, Name, Percent >> >> These two tables are joined by BCID. Its a many to many relationship. >> >> I want output that shows table1.cust, table1.segment, table2.name, >> table1.amount * table2.percent >> >> Simplified assume the following data. >> >> Table1 >> cust BCID Segment Amount >> TestCo 1 XYZ 100 >> ABCCo 1 ZZZ 100 >> >> Table 2 >> BCID Name Percent >> 1 John .50 >> 1 Jane .50 >> >> In access if I join these two tables into a summary query I can get the >> following output. >> >> TestCo XYZ John 50 >> TesCo XYZ Jane 50 >> ABCCo ZZZ John 50 >> ABCCo ZZZ Jane 50 >> >> But I'm having a difficult time visualizing the Filemaker way to get >> this to work. > > To do a many-to-many relationship you need an intermediate Join table, > that holds the IDs for both sides of the relationship. You can use the > Join table to do calculations peculiar to each of the instances and to > display data from both of the linked tables. > > However, I am at a loss to understand the significance of the BCID in > your setup. It does not appear to be a unique identifier of anything. > > General practice is to assign a unique entity identifier to each record > in a table, normally an automatically generate serial number. Structural > relationships are usually based on these unique identifiers. You can > base relationships on other fields of course, but that is the usual > practice. > > There are some useful white papers and other resources on the FileMaker > web site that will help you understand these ideas better, and of course > there is the FileMaker user guide that comes as part of the electronic > documentation with your setup, as well as the on-screen Help. In reality both tables do have unique identifiers, but neither identifier exists in the other table. I just simplified my data and didn't bother showing fields that weren't relevant to my problem. It sounds like the only solution is to create an intermediary table.