cartesian join

[Synergy.ron@gmail.com]

Attachment 43484 I wrote a dbms in Filemaker. the main table is Members. i used a cartesian join between Members and a copy of members for navigation purposes. i,e--the user clicks a members name on the left and some gtrr (go to related record) code shows that members info in the main window. I notice access does not have a cartesian join ability nor does it have a gtrr command.


question : is there a work around for this??? thanks

[Micron]

I notice access does not have a cartesian join ability

In Access, two tables in a query that are not joined will produce a Cartesian result. Add a third table (not joined) and the result will be exponential.

[CJ_London]

nor does it have a gtrr command.

Not familiar with gtrr but based on what you are saying....

In access there are a number of ways to do this but the nearest to what you are describing would have a mainform and subform setup with your members (in blue) in an unbound listbox on the main form together with the personal data (aka you have along the top). The transactional data would appear in one or more subforms.

the equivalent code for gtrr would be in the listbox after update event and would be something like

me.filter="PK=" & mylistboxname
me.filteron=true

this assumes that your listbox has a hidden column containing the PK for the member and is identified as such as the bound column

[DittoBird]

Hi Ben,

gtrr seems to be related to File Maker Pro, from a quick Google (I used FMP back in 1993/4 for less than a year ).

Why would you need every record in one table joined with every record on a copy of the same table? As Micron has mentioned, a cartesian join of two tables, each with the same number of records, would result in an awful lot of records. For 100 records per table, you'd end up with 10,000 records.

Is the copy of the Members table a back up?

You can certainly have the Members table reflected on a form and a button with code that will, what I assume is gtrr, search for first and last name on the Members Copy table and return a query or recordset with the single or multiple matching records in Members Copy. Otherwise, Ajax has mentioned a single primary key which would make things easier to do as he illustrated. If you are using multiple fields as a PK (first, middle, last name - though I have found a fellow in the UK with the exact same combination as I have! :O) then you really don't need a cartesian join.

All the best,
--
Tim

[Synergy.ron@gmail.com]

(see png pic above) the main form is based on members and so is the portal navigation table. it is 'x' related to show all member records. i uspect that the left 'members' tale should be replaxed with al list control that is populated wit some vba recordset code.....yjanks for the thoughts

[Synergy.ron@gmail.com]

thanks, this should be interesting.....onward into the light!

Not familiar with gtrr but based on what you are saying....

In access there are a number of ways to do this but the nearest to what you are describing would have a mainform and subform setup with your members (in blue) in an unbound listbox on the main form together with the personal data (aka you have along the top). The transactional data would appear in one or more subforms.

the equivalent code for gtrr would be in the listbox after update event and would be something like

me.filter="PK=" & mylistboxname
me.filteron=true

this assumes that your listbox has a hidden column containing the PK for the member and is identified as such as the bound column